Mar 03

Determine If Distribution Group is Being Used in 365 Exchange

Posted on March 3, 2016 by Managed WordPress Migration User

“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 = [XML](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

Posted on May 14, 2015 by Managed WordPress Migration User

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

Feb 18

Mail Merge with Attachment

Posted on February 18, 2015 by Managed WordPress Migration User

There is not native way to add an attachment when doing an mail merge in Microsoft Office (Outlook/Word/Excel). However, there are 3rd party apps that allow you to add attachments when doing a mail merge, but these programs usually cost $. If you’re like me and don’t want to spend money on an application you’ll probably use once, then I came up with an alternative free solution with Powershell. This method works similar to a Microsoft Office mail merge because it will require a data source, email template, and an Outlook profile.

Step 1 – Create Data Source

I will use a users.csv as the data source of users we want to email. The users.csv file will look as follows:

FirstName	LastName	Email	Username	Password
Joseph	smith	jsmith@domain.com	jsmith	VideoGame01
Bill	Contoso	bcontoso@company.com	bcontoso	LilyFlower18
Jim	Rufus	jim.rufus@yahoo.com	jrufus	StuffedAnimal23

Step 2 – Create Outlook Profile to Send Mail Merge From

Now that you have your data source, you’ll need to make sure you have an Outlook profile. This profile should be setup with the email address you wish to send the mail merge from.

Step 3 – Create Powershell Script

In the below powershell script, you’ll need to modify the following variables:

$DataSource – Path to the data source (users.csv) file created in step 1
$AttachFile – Path to the file to attach to the email
$EmailSubject – Subject of the email

In addition to modifying the above variables, you’ll need to modify $Mail.Body, which is the body of the email. The below example is referencing data fields in users.csv for the mail merge, which you may want to modify. These correspond as follows:

$($_.FIRSTNAME) – FIRSTNAME field in users.csv

$($_.LASTNAME) – LASTNAME field in users.csv

$($_.USERNAME) – USERNAME field in users.csv

$($_.PASSWORD) – PASSWORD field in users.csv

Special Note: To add a new line in the body text use a backtick + n ( `n)

$DataSource = "C:\mailmerge\users.csv"
$AttachFile = "C:\mailmerge\Setup Email Directions.docx"
$EmailSubject = "How to Setup Email"

Import-CSV $DataSource | Foreach-Object {
	$ol = New-Object -comObject Outlook.Application  
	$Mail = $ol.CreateItem(0)
	$Mail.Recipients.Add($_.EMAIL)	
	$Mail.Attachments.Add($AttachFile)
	$Mail.Subject = $EmailSubject	
	$Mail.Body = "$($_.FIRSTNAME) $($_.LASTNAME),`n`n"
	$Mail.Body += "The attached directions will guide you through setting up your email account.  Your username and password are as follows:`n`n"
	$Mail.Body += "Username: $($_.USERNAME)"
	$Mail.Body += "Password: $($_.PASSWORD)`n`n"
	$Mail.Body += "If you have any issues installing, please contact support"
	$Mail.Send()
}

Extra Special Note: If you want to have the email body be HTML formatted instead of Plain Text, just modify $Mail.Body to $Mail.HTMLBody and add your HTML tags in the text. Using the example above:

$DataSource = "C:\mailmerge\users.csv"
$AttachFile = "C:\mailmerge\Setup Email Directions.docx"
$EmailSubject = "How to Setup Email"

Import-CSV $DataSource | Foreach-Object {
	$ol = New-Object -comObject Outlook.Application  
	$Mail = $ol.CreateItem(0)
	$Mail.Recipients.Add($_.EMAIL)	
	$Mail.Attachments.Add($AttachFile)
	$Mail.Subject = $EmailSubject	
	$Mail.HTMLBody = "<html><body>"
        $Mail.HTMLBody += "<h1>$($_.FIRSTNAME) $($_.LASTNAME),</h1>"
	$Mail.HTMLBody += "<p>The attached directions will guide you through setting up your email account.  Your username and password are as follows:</p>"
	$Mail.HTMLBody += "<p>Username: $($_.USERNAME)</p>"
	$Mail.HTMLBody += "<p>Password: $($_.PASSWORD)</p>"
	$Mail.HTMLBody += "<p>If you have any issues installing, please contact support<p></body></html>"
	$Mail.Send()
}

Step 4 – Send the Mail Merge

Open Microsoft Outlook with the profile created in step 2 (It is required for Outlook to be open in order for the powershell script to work!) Then open Powershell, and run the powershell script in step 3. You can confirm the emails are sending by looking in the “Sent Items” in Outlook.

Note: – If you’re using User Account Control (UAC), Outlook and Powershell must be running at the same security level. This simply means, if you open powershell using “Run as administrator” you must open Outlook with “Run as administrator”. Alternatively, if you open powershell normally (not elevated) you must open Outlook normally (not elevated).

Sigkill IT

Code and IT Knowledge

Category Archives: Email