<sub>(2024/10/01)</sub>
#microsoft #microsoft365 #powershell
<sub>Article inspiration: https://stackoverflow.com/questions/18574414/remove-lines-from-csv-file</sub>
## Intro
This is a follow up post to my original article [How to run a (targeted) compliance search for specific Exchange Inboxes or OneDrive+SharePoint sites with PowerShell - Part 1](https://owltec.ca/Microsoft+365/Purview+(Security+and+Compliance)/How+to+run+a+(targeted)+compliance+search+for+specific+Exchange+Inboxes%2C+and+OneDrive%2BSharePoint+sites+with+PowerShell+-+Part+1) so please read that post first before proceeding.
In my original post, I covered how to run a compliance search that selected specific users or specific SharePoint/OneDrive locations, but how do you run a search that is the reverse? Said differently, how do you run a compliance search for your entire organization while excluding certain people or sites?
Sadly, the `New-ComplianceSearch` cmdlet falls short once again as there are a few parameters such as `-ExchangeLocationExclusion` and `-SharePointLocationExclusion` but sadly the former only works with an on-premises Exchange server while the latter is "is reserved for internal Microsoft use."
So what can we do? Well it depends on the context but my current solution is as follows:
1. Create a .CSV of all users or SharePoint/OneDrive URLs
2. Create a .CSV another list of all users or SharePoint/OneDrive URLs you would like to exclude from your search
3. Use PowerShell to remove excluded users or SharePoint/OneDrive URLs from the .CSV of all users or SharePoint/OneDrive URLs
### The Secret
Thanks to the guidance from [Ansgar Wiechers](https://stackoverflow.com/users/1630171/ansgar-wiechers), I was able to figure out my current solution:
```
$ExcludedItems = Import-Csv -Path [PATHTOYOURCSVFILE-EXCLUSIONS] | % { $_.CSV-HEADERCOLUMN }
Import-Csv -Path [PATHTOYOURCSVFILE-ALLITEMS] | ? { $ExcludedItems -notcontains $_.CSV-HEADERCOLUMN } | Export-Csv [PATHTOYOURCSVFILE-ALL_ITEMS_MINUS_EXCLUSIONS]
```
Essentially what the script above does is:
* Load a .CSV file that contains all the exclusions that we want
* Take the .CSV file contains all of the items we have (but needs certain items removed), and then runs a loop where any item/row that does not match the .CSV file containing the exclusions is exported and added to the new .CSV file (based on the .CSV header column of your choice)
Below are some examples I did for each search location.
## Exchange
For Exchange, my .CSVs are formatted as follows:
| Email |
| --------------- |
|
[email protected] |
|
[email protected] |
|
[email protected] |
```
<#
Exchange Search
#>
$SearchName = Read-Host -Prompt "What what is the name of your search?"
$Criteria = [YOUR SEARCH CRITERIA]
# Connect to Microsoft Purview and Exchange
Connect-IPPSSession
Connect-ExchangeOnline
# Create .CSV file of all user mailboxes
$AllMailboxes = Get-Mailbox | select @{name='email'; expression={$_.userprincipalname}} | Export-Csv -Path [PATHTOYOURCSVFILE-ALLEMAILS]
# Creates new a .CSV based on [PATHTOYOURCSVFILE-ALLEMAILS]that excludes values from [PATHTOYOURCSVFILE-EXCLUDEDEMAILS]
$ExcludedMailboxes = Import-Csv -Path [PATHTOYOURCSVFILE-EXCLUDEDEMAILS] | % { $_.email }
Import-Csv -Path [PATHTOYOURCSVFILE-ALLEMAILS] | ? { $ExcludedMailboxes -notcontains $_.email } | Export-Csv [PATHTOYOURCSVFILE-ALL_EMAILS_MINUS_EXCLUDED_EMAILS]
$Search_Mailboxes = import-csv -Path [PATHTOYOURCSVFILE-ALL_EMAILS_MINUS_EXCLUDED_EMAILS]
#Initialize the array
$ExchangeLocations = @()
#Loop through each item in the CSV to populate the array
foreach ($Search_Mailbox in $Search_Mailboxes) {
$ExchangeLocations += $Search_Mailbox.Email
}
#Create a new compliance search with the specific users
New-ComplianceSearch -Name ($SearchName + "-Exchange") -ExchangeLocation $ExchangeLocations -ContentMatchQuery $Criteria | Start-ComplianceSearch
```
## OneDrive
OneDrive is straight forward as you can use the previously created .CSV file (`[PATHTOYOURCSVFILE-ALL_EMAILS_MINUS_EXCLUDED_EMAILS]`) from the Exchange section for your OneDrive search:
```
<#
OneDrive Search
#>
$SearchName = Read-Host -Prompt "What what is the name of your search?"
$Criteria = [YOUR SEARCH CRITERIA]
# Connect to Microsoft Purview and SharePoint
$Url = "https://[YOURTENANTNAME]-admin.sharepoint.com"
Connect-IPPSSession
Connect-PnPOnline -Url $Url -Interactive -ClientId [YOURCILENTID]
# Import CSV file
$Mailboxes = Import-Csv -path [PATHTOYOURCSVFILE-ALL_EMAILS_MINUS_EXCLUDED_EMAILS]
# Initialize the array
$ODLocations = @()
#Loop through each item in the CSV to populate the arrays
foreach ($Mailbox in $Mailboxes) {
$PnPUser = Get-PnPUserProfileProperty -Account $Mailbox.Email
$ODLocations += $PnPUser.PersonalUrl
}
#Create a new compliance search with specific user OneDrives
New-ComplianceSearch -Name ($SearchName + "-OneDrive") -SharePointLocation $ODLocations -ContentMatchQuery $Criteria | Start-ComplianceSearch
```
## SharePoint
For SharePoint, my .CSVs are formatted as follows:
| URL |
| ------------------------------------------------ |
| https://[yourtenant].onmicrosoft.com/sites/Site1 |
| https://[yourtenant].onmicrosoft.com/sites/Site2 |
| https://[yourtenant].onmicrosoft.com/sites/Site3 |
```
$SearchName = Read-Host -Prompt "What what is the name of your search?"
$Criteria = [YOUR SEARCH CRITERIA]
# Connect to Microsoft Purview and SharePoint
$Url = "https://[YOURTENANTNAME]-admin.sharepoint.com"
Connect-IPPSSession
Connect-PnPOnline -Url $Url -Interactive -ClientId [YOURCILENTID]
# Creates .CSV based on all SharePoint sites
$All_SPSites = Get-PnPTenantSite
$All_SPSites | select URL | Export-Csv -Path [PATHTOYOURCSVFILE-ALLSITES]
# Creates new a .CSV based on [PATHTOYOURCSVFILE-ALLSITES] that excludes values from [PATHTOYOURCSVFILE-EXCLUDEDSITES]
$Excluded_SPSites = Import-Csv -Path .[PATHTOYOURCSVFILE-EXCLUDEDSITES] | % { $_.URL}
Import-Csv -Path PATHTOYOURCSVFILE-ALLSITES] | ? { $Excluded_SPSites -notcontains $_.URL} | Export-Csv [PATHTOYOURCSVFILE-ALLSITESMINUSEXCLUDEDSITES]
$SP_URLs = import-csv -Path [PATHTOYOURCSVFILE-ALLSITESMINUSEXCLUDEDSITES]
#Initialize the array
$SPLocations = @()
#Loop through each item in the CSV to populate the array
foreach ($SP_URL in $SP_URLs) {
$SPLocations += $SP_SearchLocation.URL
}
New-ComplianceSearch -Name $SearchNameSharePoint -SharePointLocation $SPLocations -ContentMatchQuery $Criteria | Start-ComplianceSearch
```