Analyzing Mail Activity Usage Data to Compute Traffic for Each Domain

I was asked how easy it would be to generate a report about the number of emails sent per domain in a Microsoft 365 tenant. My initial response focused on the message trace log, with the idea that you could extract the domain for each outbound email and count them. This was a bad idea. The message trace log only holds data for the last ten days and is cluttered with information about system messages, such as public folder replication (for those who still use public folders). In any case, it would take a lot of work to extract and refine the information to answer the question.

Mail Usage Reports

Then I had a brainwave. Why not use the mail usage reports generated by Microsoft for use in places like the Microsoft 365 admin center? The Graph GetEmailActivityUserDetail API returns mail activity data for users over the last 7, 30, 90, or 180 days. For instance, this command returns activity data for the last 90 days and stores it in a CSV file:

Invoke-MgGraphRequest -Uri "https://graph.microsoft.com/v1.0/reports/getEmailActivityUserDetail(period='D90')" -OutputFilePath 'c:\temp\Data.csv'

The data returned for each user account looks like this:

Report Refresh Date      : 2023-07-22
User Principal Name      : Jeff.Clark@office365itpros.com
Display Name             : Jeff Clark
Is Deleted               : False
Deleted Date             :
Last Activity Date       : 2023-07-22
Send Count               : 129
Receive Count            : 637
Read Count               : 692
Meeting Created Count    : 0
Meeting Interacted Count : 0
Assigned Products        : OFFICE 365 E3
Report Period            : 90

In addition, because Microsoft calculates the mail activity data daily (it’s always a couple of days behind), fetching this data will be much faster and more scalable than attempting to retrieve and process message-tracing events.

Best of all, I use these APIs in the Microsoft 365 user activity script, so I can repurpose some code.

Microsoft Graph PowerShell SDK Access to Usage Data

Using Graph API requests usually means that you need a registered app to hold the permissions necessary to run the requests. The Microsoft Graph PowerShell SDK includes cmdlets to access user activity data, which makes things slightly easier if you want to run interactive commands or an interactive script. For example, to fetch the last seven days of mail usage activity, connect to the Graph with the required permissions (to read activity data, read the directory for user information, and change the user concealment setting – we’ll get to that later), and then run the Get-MgReportEmailActivityUserDetail cmdlet:

Connect-MgGraph -Scopes ReportSettings.ReadWrite.All, Directory.Read.All, Reports.Read.All
Get-MgReportEmailActivityUserDetail -Period 'D7' -OutFile 'c:\temp\d7.csv'

The script I wrote performed the following initial steps:

• Run the Get-MgReportEmailActivityUserDetail to retrieve the mail usage data for 7, 30, 90, and 180 days.
• The cmdlet writes the mail usage data out to a CSV file. We end up with 4 files, one for each period, which the script can import into an array.
• Find all licensed user accounts with Get-MgUser. The code I use is:

[array]$Users = Get-MgUser -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member'" -ConsistencyLevel eventual -CountVariable Records -All | Sort-Object UserPrincipalName

• For each account, fetch its mail usage activity and build a map of the account’s mail activity over 6, 30, 90, and 180 days.
• Write the information into a PowerShell list.

Here’s the code I wrote:

Write-Output "Fetching mail activity usage report data..."
Get-MgReportEmailActivityUserDetail -Period 'D7' -OutFile 'c:\temp\d7.csv'
Get-MgReportEmailActivityUserDetail -Period 'D30' -OutFile 'c:\temp\d30.csv'
Get-MgReportEmailActivityUserDetail -Period 'D90' -OutFile 'c:\temp\d90.csv'
Get-MgReportEmailActivityUserDetail -Period 'D180' -OutFile 'c:\temp\d180.csv'
# Import the data into arrays
[array]$D7Data = Import-CSV 'c:\temp\d7.csv' | Sort-Object 'User Principal Name'
[array]$D30Data = Import-CSV 'c:\temp\d30.csv' | Sort-Object 'User Principal Name'
[array]$D90ata = Import-CSV 'c:\temp\d90.csv' | Sort-Object 'User Principal Name'
[array]$D180Data = Import-CSV 'c:\temp\d180.csv' | Sort-Object 'User Principal Name'

# Process mailboxes
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($User in $Users) {
    [array]$D7Email = $D7Data | Where-Object {$_.'User Principal Name' -eq $User.UserPrincipalName}
    [array]$D30Email = $D30Data| Where-Object {$_.'User Principal Name' -eq $User.UserPrincipalName}
    [array]$D90Email = $D90ata | Where-Object {$_.'User Principal Name' -eq $User.UserPrincipalName}
    [array]$D180Email = $D180Data | Where-Object {$_.'User Principal Name' -eq $User.UserPrincipalName}

    If ($D7Email.'Report Refresh Date') {
      $ReportDate = Get-Date($D7Email.'Report Refresh Date') -format dd-MMM-yyyy
    } Else {
      $ReportDate = $Null }

    If ($D7Email.'Last Activity Date') {
      $LastActivityDate = Get-Date($D7Email.'Last Activity Date') -format  dd-MMM-yyyy   
    } Else {                              
      $LastActivityDate = $Null }      
    
    $ReportLine = [PSCustomObject] @{  
        User            = $User.UserPrincipalName
        Name            = $User.DisplayName      
        'Data Date'     = $ReportDate            
        'Last Activity' = $LastActivityDate      
        'D7 Mail In'    = $D7Email.'Receive Count'
        'D7 Mail Out'   = $D7Email.'Send Count'         
        'D30 Mail In'   = $D30Email.'Receive Count'  
        'D30 Mail Out'  = $D30EMail.'Send Count'     
        'D90 Mail In'   = $D90Email.'Receive Count'   
        'D90 Mail Out'  = $D90Email.'Send Count'      
        'D180 Mail In'  = $D180Email.'Receive Count'  
        'D180 Mail Out' = $D180Email.'Send Count'     
        Domain         = $User.Mail.Split('@')[1]}    
    $Report.Add($ReportLine)               
}    

We now have an array of records for user accounts. Here’s what one looks like:

User          : Jeff.Clark@office365itpros.com
Name          : Jeff Clark
Data Date     : 22-Jul-2023
Last Activity : 22-Jul-2023
D7 Mail In    : 53
D7 Mail Out   : 9
D30 Mail In   : 105
D30 Mail Out  : 17
D90 Mail In   : 200
D90 Mail Out  : 27
D180 Mail In  : 637
D180 Mail Out : 129
Domain        : Office365itpros.com

Piping the data to Out-GridView gives a good overview of the mail usage activity for accounts across the four periods (Figure 1).

To calculate the email activity for domains, the script runs the Get-MgDomain cmdlet to fetch the set of registered domains for the tenant and loops through the domains to fetch information about user activity captured beforehand. It then measures the sum of the 180-day figure for mail sent to compute the total of emails sent by people using the domain. Here’s the code:

[array]$Domains = Get-MgDomain | Select-Object -ExpandProperty Id 
$OutputData = [System.Collections.Generic.List[Object]]::new()     
ForEach ($Domain in $Domains) {                         
    $DomainData = $Report | Where-Object {$_.Domain -eq $Domain}  
    $DomainSendCount = ($DomainData.'D180 Mail out' | Measure-Object -Sum).Sum 
    $DomainOutput = [PSCustomObject] @{   
       'Domain'     = $Domain          
       'Send Count' = $DomainSendCount } 
    $OutputData.Add($DomainOutput)                                                          
 }                                                                                                        

# Display the domain data
$OutputData | Sort-Object 'Send Count' -Descending

The resulting output looks like this:

Domain                             Send Count
------                             ----------
redmondwork.org                          2503
office365itpros.com                       212
office365itproebook.com                     0

Dealing with Obfuscated User Data

Of course, there’s no need to write PowerShell to access usage data. You could go to the Microsoft 365 admin center and download the email activity data to a CSV file from there (Figure 2). Running Excel to process the CSV file with a sort and a few sums will soon reveal the information.

But look at the information reported in the Microsoft 365 admin center. It is obfuscated because the reports setting for the tenant dictates that personal information should be concealed. Administrators can change the reports setting to expose the real information, but that’s just a hassle and the possibility exists that the setting won’t be reset to its original value.

Fortunately, we can handle this situation in code. This snippet checks if the display concealed names setting is True. If it is, the script resets it to False to expose the real data.

If ((Get-MgBetaAdminReportSetting).DisplayConcealedNames -eq $True) {
    $Parameters = @{ displayConcealedNames = $False }
    Update-MgBetaAdminReportSetting -BodyParameter $Parameters
    $DisplayConcealedNames = $True
}

After processing is complete, the script can reset the value to True with:

If ($DisplayConcealedNames -eq $True) {
    $Parameters = @{ displayConcealedNames = $True }
    Update-MgBetaAdminReportSetting -BodyParameter $Parameters
    $DisplayConcealedNames = $Null
}

Changing the display concealed names setting is why the script needs the ReportSettings.ReadWrite.All permission. You can download the full script from GitHub.

Never Wasting Time with PowerShell

This is a long-winded answer to a question that I thought would be quick. However, writing PowerShell is never wasted time and this response allowed me to show how to use the Microsoft Graph for PowerShell SDK (V2) to download usage data and change report settings. You never know when this knowledge might be useful!