May 26

Remove Spam/Phishing Email From All Mailboxes

In 365, you can use Compliance Searches to search and/or remove emails across all of your user’s mailboxes. Compliance searches have replaced the Search-Mailbox cmdlet, which has been deprecated as of April 2020.

Pre-Requisites

You must be a member of the Discovery Management role group or be assigned the Compliance Search management role. Here’s how you can add a user to the Discovery Management group and confirm membership

#Connect to Security and Compliance Center
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.compliance.protection.outlook.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session -DisableNameChecking

#Add User to Discovery Management Group (Replace John Doe with your user)
Add-RoleGroupMember -Identity "Discovery Management" -Member "John Doe"

#Confirm Membership
Get-RoleGroupMember -Identity "Discovery Management"

#Disconnect Session
Remove-PSSession $Session

Function to Search & Remove Spam/Phishing Emails From All Mailboxes

Modify the Search Variables below to suite your needs

Function RemoveMaliciousEmails()
{
	#MODIFY THE BELOW VARIABLES - YOU MUST USE THEM ALL AND OPTIONALLY LEAVE SUBJECT BLANK!!!
	$compSearchName = "MaliciousEmail_$(Get-Date -Format "MMddyyyy_HHmm")"
	$compStartDate = "2020-02-24"  #YYYY-MM-DD
	$compEndDate = "2020-02-25"	   #Must be different than Start Date, if searching "today" make this date tomorrow
	$compFrom = "hacker@phishing.com" #You can use just the domain for a wildcard sender BUT USE WITH CAUTION
	$compSubject = "Phishing Test"  #Use backtick ` to escape special characters in the subject such as a quote ex: Dave shared `"New File`" with you
	#
	#DO NOT MODIFY ANYTHING BELOW HERE
	$UserCredential = Get-Credential
	$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.compliance.protection.outlook.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
	Import-PSSession $Session -DisableNameChecking
	Write-Host "NAME: $($compSearchName)"
	Write-Host "START: $($compStartDate)"
	Write-Host "END: $($compEndDate)"
	Write-Host "FROM: $($compFrom)"
	Write-Host "SUBJECT: $($compSubject)"
	If ($compSubject.Trim() -eq "")
	{
		$ncs = New-ComplianceSearch -Name $compSearchName -ExchangeLocation all -ContentMatchQuery "(c:c)(received=$compStartDate..$compEndDate)(from=$compFrom)"
	}
	Else
	{
		$ncs = New-ComplianceSearch -Name $compSearchName -ExchangeLocation all -ContentMatchQuery "(c:c)(received=$compStartDate..$compEndDate)(from=$compFrom)(subject=`"$compSubject`")"
	}	
	Write-Host "[$(Get-Date -Format G)] Start Compliance Search: $($compSearchName)"
	Write-Host -NoNewLine "[$(Get-Date -Format G)] Searching"
	Start-ComplianceSearch -Identity $compSearchName
	$resultSearch = Get-ComplianceSearch -Identity $compSearchName
	While ($resultSearch.Status -ne "Completed")
	{
		$resultSearch = Get-ComplianceSearch -Identity $compSearchName
		Write-Host -NoNewLine "."
		Start-Sleep -s 5
	}
	If (($resultSearch.Items -le 0) -OR ([string]::IsNullOrWhiteSpace($resultSearch.SuccessResults)))
	{
		Write-Host "`n[$(Get-Date -Format G)] Search completed with 0 successful results, Invalid Search Quitting!" -ForegroundColor Red
		Remove-PSSession $Session
		Return
	}
	Elseif ($resultSearch.Items -ge 500)
	{
		Write-Host "`n[$(Get-Date -Format G)] Search Completed with $($resultSearch.Items) successful results, you will need to run mulitple times!" -ForegroundColor Yellow
	}
	Else 
	{
		Write-Host "`n[$(Get-Date -Format G)] Search Completed with $($resultSearch.Items) successful results"
	}
	Write-Host "[$(Get-Date -Format G)] Create Preview and Export of Results"
	Write-Host -NoNewLine "[$(Get-Date -Format G)] Processing"
	$ncsa = New-ComplianceSearchAction -SearchName $compSearchName -Preview
	$resultPreview = Get-ComplianceSearchAction -Identity "$($compSearchName)_Preview"
	$ncsa = New-ComplianceSearchAction -SearchName $compSearchName -Export -ExchangeArchiveFormat SinglePst -Format FxStream
	$resultExport = Get-ComplianceSearchAction -Identity "$($compSearchName)_Export"
	While ($resultPreview.Status -ne "Completed" -AND $resultExport.Status -ne "Completed")
	{
		$resultPreview = Get-ComplianceSearchAction -Identity "$($compSearchName)_Preview"
		$resultExport = Get-ComplianceSearchAction -Identity "$($compSearchName)_Export"
		#Write-Host "[$(Get-Date -Format G)] Processing..."
		Write-Host -NoNewLine "."
		Start-Sleep -s 5
	}
	Write-Host "`n[$(Get-Date -Format G)] Preview and Export successfully created"
	Write-Host "[$(Get-Date -Format G)] View Results at https://protection.office.com/" 
	Write-Host "[$(Get-Date -Format G)] Preview: Search -> Content Search -> Searches tab -> $($compSearchName)"
	Write-Host "[$(Get-Date -Format G)] Export:  Search -> Content Search -> Exports tab -> $($compSearchName)_Export"
	Write-Host "[$(Get-Date -Format G)] Start Purging emails"
	Write-Host -NoNewLine "[$(Get-Date -Format G)] Purging"
	$ncsa = New-ComplianceSearchAction -SearchName $compSearchName -Purge -PurgeType HardDelete -Confirm:$False
	$resultPurge = Get-ComplianceSearchAction -Identity "$($compSearchName)_Purge"
	While ($resultPurge.Status -ne "Completed")
	{
		$resultPurge = Get-ComplianceSearchAction -Identity "$($compSearchName)_Purge"
		Write-Host -NoNewLine "."
		Start-Sleep -s 5
	}
	Write-Host "`n[$(Get-Date -Format G)] Purge complete"
	$confirmDelete = Read-Host "Delete Compliance Search, Preview, Export, and Purge? [Y/N]"
	If ($confirmDelete -eq 'Y') 
	{
		Write-Host "[$(Get-Date -Format G)] Deleting Compliance Search, Preview, Export, and Purge..."
		Remove-ComplianceSearchAction -Identity "$($compSearchName)_Preview" -Confirm:$False
		Remove-ComplianceSearchAction -Identity "$($compSearchName)_Export" -Confirm:$False
		Remove-ComplianceSearchAction -Identity "$($compSearchName)_Purge" -Confirm:$False
		Remove-ComplianceSearch -Identity $compSearchName -Confirm:$False
	}
	Write-Host "[$(Get-Date -Format G)] Removing Powershell Session..."
	Remove-PSSession $Session
}
RemoveMaliciousEmails
Mar 03

Determine If Distribution Group is Being Used in 365 Exchange

“What distribution groups are in use?” and “How many emails are sent to a specific distribution group per month?” are common questions I receive with 365 Exchange or Exchange.  Unfortunately, there is nothing built in that tracks how many emails on sent to a distribution group.  However we can use Get-MessageTrace to count the number of messages sent to a distribution group for a time range with the max being 30 days.  Also note, the by default PageSize returns 1000 items but you can increase the PageSize to 5000 items.  For example, to get the number of emails sent to the distribution group everyone@domain.com for a single day we can use:

$DGCount = Get-MessageTrace -PageSize 5000 -RecipientAddress "everyone@domain.com" -StartDate ([DateTime]::Today.AddDays(-1)) -EndDate ([DateTime]::Today) | ForEach-Object {$count++}
$DGCount

Using this method, we can count the number of emails sent to each distribution group each day and store the results in an output file.  We can then query those output files and create a report.  In my example, the report will show the total emails sent to each distribution group by month and go back 12 months.  Now without further ado, let’s get to the two scripts needed.

365_DGCounter.ps1

#365_DGCounter.ps1
# ------ SCRIPT CONFIGURATION ------
#Log File
$LogFile = $MyInvocation.MyCommand.Path.Replace($MyInvocation.MyCommand.Name,"Logs\") + ((Get-Date).AddDays(-1).ToString('yyyy_MM_dd')) + ".xml"
#Table Name
$TableName = "DG_Emails_Received"
# ------ END CONFIGURATION ------

#Confirm Logs Directory Exists
$LogPath = $MyInvocation.MyCommand.Path.Replace($MyInvocation.MyCommand.Name,"Logs\")
If (!(Test-Path $LogPath)){
	New-Item -ItemType Directory -Force -Path $LogPath
}

#Import Modules and Connect to Office 365
import-module ActiveDirectory
Try {
	$UserCredential = Get-Credential
	$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell/ -Credential $UserCredential -Authentication Basic -AllowRedirection
	Import-PSSession $Session
}
Catch {
	#Error Connecting to Office365
}

#Create Data Table
$CounterTable = New-Object system.Data.DataTable $TableName
$CounterCol1 = New-Object system.Data.DataColumn Date,([datetime])
$CounterCol2 = New-Object system.Data.DataColumn Email,([string])
$CounterCol3 = New-Object system.Data.DataColumn Count,([int])
$CounterTable.columns.add($CounterCol1)
$CounterTable.columns.add($CounterCol2)
$CounterTable.columns.add($CounterCol3)

#Get DG's and Add to Table
$DG = Get-DistributionGroup -Resultsize Unlimited | select Displayname, Primarysmtpaddress
$DG | ForEach-Object {
	$count = 0
	#Note: [DateTime]::Today returns 12:00 AM of the Current Date 
	#      Let's assume the date is currently 3/3/2016, the below returns the range 3/2/2016 12:00 AM to 3/3/2016 12:00 AM
	Get-MessageTrace -PageSize 5000 -RecipientAddress $_.Primarysmtpaddress -StartDate ([DateTime]::Today.AddDays(-1)) -EndDate ([DateTime]::Today) | ForEach-Object {$count++}
	$CounterRow = $CounterTable.NewRow()
	$CounterRow.Date = [datetime](Get-Date).AddDays(-1)
	$CounterRow.Email = $_.Primarysmtpaddress
	$CounterRow.Count = $count
	$CounterTable.Rows.Add($CounterRow)
}

#Output Results
$CounterTable.WriteXml($LogFile)
$CounterTable.WriteXmlSchema($LogFile.replace(".xml",".xsd"))
$CounterTable | format-table -AutoSize

#Disconnect From Office365
Remove-PSSession $Session

Note: [DateTime]::Today returns 12:00 AM of the Current Date.  Let’s assume the date is currently 3/3/2016.  The script above would return the range 3/2/2016 12:00 AM to 3/3/2016 12:00 AM.  Running this script will actually return the email counts from yesterday since today has not ended.

365_DGCounterReport.ps1

#365_DGCounterReport.ps1
# ------ SCRIPT CONFIGURATION ------
#Log File Path
$LogPath = $MyInvocation.MyCommand.Path.Replace($MyInvocation.MyCommand.Name,"Logs\")
#Table Names
$MasterTableName = "DG_Emails_Received"
$ResultTableName = "DG_Emails_Received"
# ------ END CONFIGURATION ------

#Import XML Files to Master Table
$MasterTable = New-Object system.Data.DataTable $MasterTableName
$MasterCol1 = New-Object system.Data.DataColumn Date,([datetime])
$MasterCol2 = New-Object system.Data.DataColumn Email,([string])
$MasterCol3 = New-Object system.Data.DataColumn Count,([int])
$MasterTable.columns.add($MasterCol1)
$MasterTable.columns.add($MasterCol2)
$MasterTable.columns.add($MasterCol3)
Get-ChildItem $LogPath -Filter "*.xml" | Sort-Object Name | ForEach-Object {
	$XmlDocument = (Get-Content -Path $_.FullName)
	$XmlDocument.DocumentElement.$MasterTableName | ForEach-Object {
		$MasterRow = $MasterTable.NewRow()
		$MasterRow.Date = [datetime]$_.Date
		$MasterRow.Email = $_.Email
		$MasterRow.Count = [int]$_.Count
		$MasterTable.Rows.Add($MasterRow)
	}
}

#Get List of Unique Distribution Groups from Master Table
$DGs = @{}
$MasterTable | ForEach-Object {
	If (!($DGs.ContainsKey($_.Email))){
		$DGs.Add($_.Email,$_.Email)
	}
}
$DGs = $DGs.GetEnumerator() | Sort-Object Name

#Count Emails By Month
$ResultTable = New-Object system.Data.DataTable $ResultTableName
$ResultCol1 = New-Object system.Data.DataColumn Email,([string])
$ResultCol2 = New-Object system.Data.DataColumn Mon,([int])
$ResultCol3 = New-Object system.Data.DataColumn Mon_1,([int])
$ResultCol4 = New-Object system.Data.DataColumn Mon_2,([int])
$ResultCol5 = New-Object system.Data.DataColumn Mon_3,([int])
$ResultCol6 = New-Object system.Data.DataColumn Mon_4,([int])
$ResultCol7 = New-Object system.Data.DataColumn Mon_5,([int])
$ResultCol8 = New-Object system.Data.DataColumn Mon_6,([int])
$ResultCol9 = New-Object system.Data.DataColumn Mon_7,([int])
$ResultCol10 = New-Object system.Data.DataColumn Mon_8,([int])
$ResultCol11 = New-Object system.Data.DataColumn Mon_9,([int])
$ResultCol12 = New-Object system.Data.DataColumn Mon_10,([int])
$ResultCol13 = New-Object system.Data.DataColumn Mon_11,([int])
$ResultCol14 = New-Object system.Data.DataColumn Total,([int])
$ResultTable.columns.add($ResultCol1)
$ResultTable.columns.add($ResultCol2)
$ResultTable.columns.add($ResultCol3)
$ResultTable.columns.add($ResultCol4)
$ResultTable.columns.add($ResultCol5)
$ResultTable.columns.add($ResultCol6)
$ResultTable.columns.add($ResultCol7)
$ResultTable.columns.add($ResultCol8)
$ResultTable.columns.add($ResultCol9)
$ResultTable.columns.add($ResultCol10)
$ResultTable.columns.add($ResultCol11)
$ResultTable.columns.add($ResultCol12)
$ResultTable.columns.add($ResultCol13)
$ResultTable.columns.add($ResultCol14)
$CurDate = Get-Date
$DGs | ForEach-Object{
	$DG = $_.Name
	$ResultRow = $ResultTable.NewRow()
	$ResultRow.Email = $DG
	$ResultRow.Mon = 0
	$ResultRow.Mon_1 = 0
	$ResultRow.Mon_2 = 0
	$ResultRow.Mon_3 = 0
	$ResultRow.Mon_4 = 0
	$ResultRow.Mon_5 = 0
	$ResultRow.Mon_6 = 0
	$ResultRow.Mon_7 = 0
	$ResultRow.Mon_8 = 0
	$ResultRow.Mon_9 = 0
	$ResultRow.Mon_10 = 0
	$ResultRow.Mon_11 = 0
	$ResultRow.Total = 0
	$MasterTable | ForEach-Object {
		If ($DG -eq $_.Email){
			#Current Month
			If ($_.Date.Month -eq $CurDate.Month){
				$ResultRow.Mon += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-1
			If ($_.Date.Month -eq $CurDate.AddMonths(-1).Month){
				$ResultRow.Mon_1 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-2
			If ($_.Date.Month -eq $CurDate.AddMonths(-2).Month){
				$ResultRow.Mon_2 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-3
			If ($_.Date.Month -eq $CurDate.AddMonths(-3).Month){
				$ResultRow.Mon_3 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-4
			If ($_.Date.Month -eq $CurDate.AddMonths(-4).Month){
				$ResultRow.Mon_4 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-5
			If ($_.Date.Month -eq $CurDate.AddMonths(-5).Month){
				$ResultRow.Mon_5 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-6
			If ($_.Date.Month -eq $CurDate.AddMonths(-6).Month){
				$ResultRow.Mon_6 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-7
			If ($_.Date.Month -eq $CurDate.AddMonths(-7).Month){
				$ResultRow.Mon_7 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-8
			If ($_.Date.Month -eq $CurDate.AddMonths(-8).Month){
				$ResultRow.Mon_8 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-9
			If ($_.Date.Month -eq $CurDate.AddMonths(-9).Month){
				$ResultRow.Mon_9 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-10
			If ($_.Date.Month -eq $CurDate.AddMonths(-10).Month){
				$ResultRow.Mon_10 += $_.Count
				$ResultRow.Total += $_.Count
			}
			#Current Month-11
			If ($_.Date.Month -eq $CurDate.AddMonths(-11).Month){
				$ResultRow.Mon_11 += $_.Count
				$ResultRow.Total += $_.Count
			}
		}
	}
	$ResultTable.Rows.Add($ResultRow)
}

#Rewrite Column Names as Months
For ($i=0; $i -lt 12; $i++) {
	If ($i -eq 0){
		$ResultTable.columns["Mon"].ColumnName = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($CurDate.Month)) $($CurDate.Year)"
	}Else{
		$ResultTable.columns["Mon_$($i)"].ColumnName = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($CurDate.AddMonths($i * -1).Month)) $($CurDate.AddMonths($i * -1).Year)"
	}
}

#Output Results
$ResultTable | format-table -AutoSize
$ResultTable | Export-CSV Report.csv -notypeinformation

 

May 14

Search for Emails in a 365 User’s Mailbox

EDIT: Search-Mailbox has been deprecated as of April 2020 in 365.  Please see my updated post that about using Compliance Search instead!

Overview

Often times, my posts are influenced by the questions of others in IT forums.  The other day, an IT pro asked “How can I retrieve emails a 365 user sent to a certain recipient”?  Obviously, I thought to myself, there should be a way to search a mailbox with powershell.  While writing the small script to answer their question, I realized I could do more than just search and copy with the search-mailbox cmdlet.

  • Search recoverable items.  This can be useful if a terminated employee deleted important emails that their manager needs.
  • Delete Emails.  This can useful for a scenario where a virus makes it to all user’s inbox or a disgruntled employee emails a nasty email to everyone.
  • There’s a TON of properties indexed by Exchange that you can query

Without further ado, let’s get to the script

Prerequisites

Delegate Full Access to Mailboxes

In order to search mailboxes, you’ll need to ensure your account has Full Access to each user’s mailbox.  You can do this through the 365 Exchange Admin Center, or you can give yourself full access to all user’s mailbox with the following powershell script.  Make sure you authenticate using an Exchange Admin and replace bsteinmeyer@yourdomain.onmicrosoft.com with the account you need to delegate access.

#Connect to 365 with Admin Credentials
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

#Delegate Full Access to All Mailboxes
Get-Mailbox -ResultSize unlimited -Filter {(RecipientTypeDetails -eq 'UserMailbox')} | Add-MailboxPermission -User bsteinmeyer@yourdomain.onmicrosoft.com -AccessRights FullAccess -InheritanceType all

Search Mailbox For Email Sent to a Specific Email

#Connect to 365 with Admin Credentials
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

#Search Mailbox
Get-Mailbox jsmith@yourdomain.onmicrosoft.com | Search-Mailbox -SearchQuery "To:foo@bar.com" -TargetMailbox bsteinmeyer@yourdomain.onmicrosoft.com -TargetFolder "Search_Result" -LogLevel Full –SearchDumpster
  • jsmith@yourdomain.onmicrosoft.com = User’s mailbox you want to search
  • foo@bar.com = Email address sent to
  • bsteinmeyer@yourdomain.onmicrosoft.com = The Mailbox you want to copy the emails to
  • SearchDumpster = Search recoverable items (Emails that were deleted from the Trash)
  • *Note: If you only want to test the command and NOT copy anything, you can add the -LogOnly switch

The above will search a specified user’s mailbox for all emails sent to the specified email address.  The results and emails will be copied to the specified mailbox in the specified folder (This will most likely be your admin account).  If the folder does not exist, it will be automatically created.

Search All Mailboxes for Specific Email and Delete It

In order to delete emails with the -DeleteContent switch, you must be assigned the Discovery Management role and Mailbox Import Export role.  By default, the Mailbox Import Export role isn’t assigned to any role group, so we’ll need to create a new group and assign our user.

#Query Discovery Management Members
Get-RoleGroupMember -Identity "Discovery Management"

#Assign Discovery Management Member
Add-RoleGroupMember -Identity "Discovery Management" -Member bsteinmeyer@yourdomain.onmicrosoft.com

#Create Mailbox Import Export Management Group
New-RoleGroup "Mailbox Import-Export Management" -Roles "Mailbox Import Export"

#Add User to Mailbox Import Export Management Group
Add-RoleGroupMember "Mailbox Import-Export Management" -Member bsteinmeyer@yourdomain.onmicrosoft.com

With that complete, we can now search everyone’s email by the subject and date and delete it.

Get-Mailbox -ResultSize Unlimited | Search-Mailbox -SearchQuery {Subject:"You're a Winner!" AND Sent:"5/14/2015"} -DeleteContent -LogLevel Full –SearchDumpster

*Note: If you only want to test the command and NOT delete anything, you can add the -LogOnly switch

Final Comments

If you’d like to further refine your queries or do more advanced queries, see the complete message properties indexed by Exchange Search below:

https://technet.microsoft.com/en-us/library/jj983804(v=exchg.150).aspx