← Archive

How to run a (targeted) compliance search for specific Exchange inboxes or OneDrive+SharePoint sites with PowerShell - Part 2 (Exclusions)

Archived post · originally published 2024-10-01 · may be out of date

Microsoft 365 · #Microsoft #Microsoft365 #PowerShell

_{Article inspiration: https://stackoverflow.com/questions/18574414/remove-lines-from-csv-file}

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 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 another .CSV 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, 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:

• Load a .CSV file that contains all the exclusions that we want
• Take the .CSV file that 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:

<#
Exchange Search
#>

$SearchName = Read-Host -Prompt "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 a new .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 straightforward 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 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 [YOURCLIENTID]

# 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:

$SearchName = Read-Host -Prompt "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 [YOURCLIENTID]

# Creates .CSV based on all SharePoint sites
$All_SPSites = Get-PnPTenantSite
$All_SPSites | select URL | Export-Csv -Path [PATHTOYOURCSVFILE-ALLSITES]

# Creates a new .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_URL.URL
}

New-ComplianceSearch -Name ($SearchName + "-SharePoint") -SharePointLocation $SPLocations -ContentMatchQuery $Criteria | Start-ComplianceSearch