Office 365: Create a report of distribution group usage…
Occasionally we receive customer requests for generation of report data. Today I received a request where the customer wanted to generate a report of distribution list utilization. In essence they wanted to understand which distribution lists in their environment were in use. It sounds like the goal would be to eventually clean up those distribution lists that were not actually being utilized while retaining distribution lists that are in use.
I’m all in favor of a clean GAL – I work with plenty of customers who still seem to have objects around from Exchange 4.0 – so anything I can do to help with the cleanup effort I’m all for.
We do not have a precanned report that allows you to analyze distribution list utilization. So any form of analysis would force us to get creative. My idea for solving this particular question was to utilize the message tracking logs. Since the live message tracking logs contain the last three days worth of message tracing events within Office 365 – we could potentially scan the logs for recipients that were distribution lists and see what we find. A historical search would probably not be particularly useful since the number of searches were throttled.
In full disclosure I ran this in my lab – which only has a few distribution lists and a few message tracking events. Your success in a larger environment may vary.
The first step is to gather the groups into a variable.
$groups=Get-DistributionGroup -ResultSize unlimited
This should result in the $groups variable containing a list of groups to process.
$groups
Name DisplayName GroupType PrimarySmtpAddress
---- ----------- --------- ------------------
All All Universal All@contoso.com
Board Members Board Members Universal Board@contoso.com
Employees Employees Universal Employees@contoso.com
EOC-Alert EOC-Alert Universal EOC-Alert@contoso.com
TestDL MigratedDl-TestDL Universal MigratedDl_8cace652-98f1-4ca9-a2d9-f30f29abffe1_@domain...
Office365Notification Office365Notification Universal Office365Notification@contoso.com
Once we have the groups in a variable we can begin the process of searching the message tracking logs.
PS C:\> $groups | %{Get-MessageTrace -RecipientAddress $_.primarysmtpaddress ; write-host (“Processed Group: ” + $_.primarySMTPAddress) ; Start-Sleep -Milliseconds 500} | export-csv -Path C:\Temp\output.csv –Append
Note that I have incorporated a sleep into our get command to help throttle the request for message tracing data and hopefully relieve some stress on our powershell throttling budget. In addition I have utilize the append parameter with the CSV file. I did this assuming that you’d run this maybe once a week for a month to gather some relevant data over 30 days. This allows that data to be appended to the CSV file. The write host will provide output to the console on the group being processed to provide some form of process tracking.
PS C:\> $groups | %{Get-MessageTrace -RecipientAddress $_.primarysmtpaddress -Verbose; write-host ("Processed Group: " + $_.primarySMTPAddress) ; Start-Sleep -Milliseconds 500} | export-csv -Path C:\Temp\output.csv -Append
Processing Group + All@contoso.com
Processing Group + Board@contoso.com
Processing Group + Employees@contoso.com
Processing Group + EOC-Alert@contoso.com
Processing Group + MigratedDl_8cace652-98f1-4ca9-a2d9-f30f29abffe1_@contoso.onmicrosoft.com
Processing Group + Office365Notification@contoso.com
Opening the CSV file in notepad we can validate that information is contained for each of the recipients.
#TYPE Deserialized.Microsoft.Exchange.Management.FfoReporting.MessageTrace
"PSComputerName","RunspaceId","PSShowComputerName","Organization","MessageId","Received","SenderAddress","RecipientAddress","Subject","Status","ToIP","FromIP","Size","MessageTraceId","StartDate","EndDate","Index"
"ps.outlook.com","e55f3262-ed9d-4d6a-8894-6754f34dc22b","False","contoso.onmicrosoft.com","<FEA02074-3006-4AEA-A0E2-43D47593713B@contoso.com>","11/6/2017 2:52:00 PM","bmoran@contoso.com.org","all@contoso.com.org","Tuesday & Wednesday Night Driver","Expanded",,"74.126.52.144","19398","7731ffda-9297-461a-a0e3-08d52525ef68","11/5/2017 1:00:27 AM","11/7/2017 1:00:27 AM","0"
"ps.outlook.com","e55f3262-ed9d-4d6a-8894-6754f34dc22b","False","contoso.onmicrosoft.com","<BN6PR06MB3491B192326AE8CFC6BAC738C3530@BN6PR06MB3491.namprd06.prod.outlook.com>","11/5/2017 8:16:40 PM","tmcmichael@contoso.com.org","all@contoso.com.org","Monday, Tuesday, and Wednesday","Expanded",,"2001:4898:8010:1::1ef","37249","067dc5b4-c38f-4c29-2a1b-08d5248a200a","11/5/2017 1:00:27 AM","11/7/2017 1:00:27 AM","1"
"ps.outlook.com","e55f3262-ed9d-4d6a-8894-6754f34dc22b","False","contoso.onmicrosoft.com","<CAPq5dUQaYMxzSZuryF6-T9P0aBipxkWEj0CfzeEd6Wv-O8gQkA@mail.gmail.com>","11/6/2017 3:59:42 PM","brian@contoso.com","board@contoso.com.org","Board Meeting Reminder at Station 1","Expanded",,"209.85.128.180","31479","9eeb9375-7128-452c-0909-08d5252f646a","11/5/2017 1:00:28 AM","11/7/2017 1:00:28 AM","0"
Now that we have the information regarding the DLs that have been sent to – it would be helpful if this data was in a more usable format.
We can import the CSV file into a working variable.
$data=Import-Csv -Path C:\Temp\output.csv
The variable now holds the data to manipulate moving forward.
PS C:\> $data
PSComputerName : ps.outlook.com
RunspaceId : e55f3262-ed9d-4d6a-8894-6754f34dc22b
Organization : contoso.onmicrosoft.com
MessageId : <FEA02074-3006-4AEA-A0E2-43D47593713B@contoso.com>
Received : 11/6/2017 2:52:00 PM
SenderAddress : bmoran@contoso.com
RecipientAddress : all@contoso.com
Subject : Tuesday & Wednesday Night Driver
Status : Expanded
ToIP :
FromIP : 74.126.52.144
Size : 19398
MessageTraceId : 7731ffda-9297-461a-a0e3-08d52525ef68
StartDate : 11/5/2017 1:00:27 AM
EndDate : 11/7/2017 1:00:27 AM
Index : 0
PSComputerName : ps.outlook.com
RunspaceId : e55f3262-ed9d-4d6a-8894-6754f34dc22b
Organization : contoso.onmicrosoft.com
MessageId : <BN6PR06MB3491B192326AE8CFC6BAC738C3530@BN6PR06MB3491.namprd06.prod.outlook.com>
Received : 11/5/2017 8:16:40 PM
SenderAddress : tmcmichael@contoso.com
RecipientAddress : all@contoso.com
Subject : Monday, Tuesday, and Wednesday
Status : Expanded
ToIP :
FromIP : 2001:4898:8010:1::1ef
Size : 37249
MessageTraceId : 067dc5b4-c38f-4c29-2a1b-08d5248a200a
StartDate : 11/5/2017 1:00:27 AM
EndDate : 11/7/2017 1:00:27 AM
Index : 1
With the data in a variable – we can utilize the sort method to generate a list of the unique recipients found.
PS C:\> $data | select-object recipientaddress -Unique
RecipientAddress
----------------
all@contoso.org
board@contoso.org
eoc-alert@contoso.org
office365notification@contoso.org
This data can also be exported to CSV file for further analysis using the export-csv command.
This may serve as a method to attempt to report on the distribution lists that are in use within an Office 365 tenant. You can also use the same process for modern groups by replacing get-distributionGroup with get-unifiedGroup.
======================
Edited 11/11/2017
Syntax of powershell command for searching message tracking logs updated. Processing group was changed to processed group – since the notification comes after the group was processed. Corrected syntax in write-host so that it displayed feedback accurately.
======================