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 [email protected] for a single day we can use:

$DGCount = Get-MessageTrace -PageSize 5000 -RecipientAddress "[email protected]" -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

18 comments

Skip to comment form

    • ChrisJ on February 1, 2017 at 9:41 am

    I’ve been looking for something just like this. Thank you!

    I did tweak the script some. I added code to automate the login process so that the script could be run from Task Scheduler. cred.txt has the password for an account with the privileges necessary to run the script stored in a secure encrypted string.

    #365 login creds
    $AdminName = “[email protected]
    $Pass = Get-Content “cred.txt” | ConvertTo-SecureString
    $cred = new-object -typename System.Management.Automation.PSCredential -ArgumentList $AdminName, $Pass

      • Deb Codding on December 6, 2018 at 1:34 am

      Thank you for adding your tweaks. They are just what I needed.

    • Declen on December 7, 2017 at 12:36 pm

    I was able to get this to work originally but now…it won’t run past the table creation it just hangs. Any thoughts?

    • Dheeraj on September 5, 2018 at 1:36 pm

    Works like a charm. Thanks a ton for the script. I have put this as a daily script to collect the data and should have a good report after a year.

    • Deb Codding, Office 365 Senior Administrator on December 6, 2018 at 1:32 am

    I am so appreciative of your efforts and willingness to share your scripts. We are in a hybrid Office 365 deployment. All email accounts are in the cloud and the DLs are still on-premise. Our DLs are a hodge podge that has grown and been sorely mismanaged (i.e., ignored) for more than a decade. I needed to identify the active DLs in preparation for moving them to the cloud and address deleting the unused DLs. Your scripts are excellent and suit my needs perfectly! In just a few hours I was able to pull the last 30 days. I’ve used ChrisJ’s idea and am now setting this in Task Scheduler to continue gathering data. Thank you for your great scripts! You’ve save me a great deal of time with the deadlines I am facing!

    • Ajai Gc on September 1, 2020 at 3:21 am

    Second script doesn’t seems to work “cannot convert null to type systemdate.time”

      • Cody on September 30, 2020 at 2:46 pm

      I found that I had to [XML] to the front of line $XmlDocument = (Get-Content -Path $_.FullName) so it became $XmlDocument = (Get-Content -Path $_.FullName). Then it works for me.

      1. You are correct – Looks be be a formatting error on the site because it shows when I edit the post, just not on the “live” version… Looking at fixing this now

        1. UPDATE: Found the Fix, it is hidden when ‘xml’ is lowercase between the brackets. It should read:

          $XmlDocument = [XML](Get-Content -Path $_.FullName)

            • Robert on January 15, 2021 at 5:02 pm

            Still getting this error on second script.
            Cannot convert null to type “System.DateTime”.
            $MasterRow.Date = [datetime]$_.Date
            + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : InvalidArgument: (:) [], RuntimeException
            + FullyQualifiedErrorId : nullToObjectInvalidCast

    • Roy on October 20, 2020 at 6:19 am

    I love this script in theory but it’s horrendously slow running in a larger environment! Any tips welcome…

    1. The speed is depends on the Get-MessageTrace cmdlet (Which is not fast at all), the number of DL’s, and the number of email sent. This is designed to run “365_DGCounter.ps1” once every 24 hours to build the daily data, and I would suggest running this as a scheduled daily task at like 12:30AM. You would then run the “365_DGCounterReport.ps1” script on the 1st or last day of the month to build a report of the daily data. To give you an idea of how long it takes, for about 1,000 DL’s the daily script runs in less than 10min and the monthly script in less than 1 hour. You’re probably not going to improve much speed on the 1st script, since that is what MS provides to grab the data. However, you could definitely improve parsing the data files on the second script. Personally, for me, I am fine waiting 1 hour since I use this as a monthly report. If you want to add any suggestions to improve that, let me know and I’ll update & tag you

    • Roy on October 20, 2020 at 7:17 am

    The get-messagetrace cmdlet also only goes back 10 days, the report.ps1 seems to go back 10 months, but contains no code to facilitate this, nor does the main script. Please explain how you make this out of thin air?

    1. Get-MessageTrace cmdlet can go back to 30 days (Which is slow but what is available), and the intent of these scripts is to use that as a basis to extend it on a more long term basis. This is meant to run as a 2 part script as follows:

      1) Daily script that calculates emails sent to each DL using get-messagetrace that runs once every 24 hours (Ideally after hours, or it may potentially miss an email count as it runs). This way you can quickly calculate how many emails are sent to each DL daily. I would recommend making this a scheduled task that runs every night at like 12:30AM
      2) Monthly script that parses the daily data dumps and adds them up to give you a monthly count. I would suggest making this a monthly task that runs on the 1s or last day of the month.

      Using just the get-messagetrace, you could only build a report that goes back 30 days and it takes FOREVER. The idea here is that you track the info every day using the get-messagetrace cmdlet, which should only take a few minutes and dump that data. You can then build a report on that daily data to get a count of emails sent to each DL based on whatever timeframe you want. I’ve done it on a monthly basis, but you can modify it to your liking.

        • Ed on December 23, 2020 at 1:11 pm

        “The StartDate can’t be older than 10 days from today” message when trying to run this for a month. How are you running it for 30 days? (slow or not)

        1. The first script runs daily to tally the total emails sent to each DL, which is written to a log file. You then use a 2nd script that runs every month that uses the daily logs to tally the total for the month. Basically, you can only start tracking the total emails sent to each DL from the day you start using the scripts and it cannot instantly give you results from the past few months. You need to build the data before it’s available to you.

    • Commenter2021 on May 16, 2021 at 11:17 pm

    Thanks for the scripts/article. Does time zone have any effect i.e. the today -1 part or is Office 365 based on UTC or your tenant geo location?

  1. 365 stores the date in UTC format, but using [datetime] in powershell will convert it to the timezone of the computer powershell runs on.

Comments have been disabled.