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).

The mail activity usage data retrieved from the Graph
Figure 1: The mail activity usage data retrieved from the Graph

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.

Accessing mail activity usage data in the Microsoft 365 admin center
Figure 2: Accessing mail activity usage data in the Microsoft 365 admin center

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!

On Demand Migration

Migrate all your workloads and Active Directory with one comprehensive Office 365 tenant-to-tenant migration solution.

About the Author

Tony Redmond

Tony Redmond has written thousands of articles about Microsoft technology since 1996. He is the lead author for the Office 365 for IT Pros eBook, the only book covering Office 365 that is updated monthly to keep pace with change in the cloud. Apart from contributing to Practical365.com, Tony also writes at Office365itpros.com to support the development of the eBook. He has been a Microsoft MVP since 2004.

Comments

  1. Tumisang

    Hi,
    Just wondering if its there a possibility of getting a script that would be able to pull out email and teams usage for the last 365 days.

    Thanks

  2. VirEl

    This is great script. Extracts all the data from 365 Exchange reports. There is only one thing I’d like to ask if possible, is there any chance to extract number count of sent emails per user internally? I just need count of internally sent emails per user within my domain. That would be great

    1. Avatar photo
      Tony Redmond

      Nope. The usage report API doesn’t differentiate between internal and external email. If you want that information, I think you’ll need to analyze the message trace data, and that only goes back 10 days.

      1. VirEl

        Tony, you are the man. Write long and prosper. Thank you

  3. Mariangeli Hinds

    Looking to run a report of messages sent via SMTP through our On-Prem Exchange servers which are used as an SMTP mail relay to O365. Similar to the Mail Activity Report you created.

    1. Avatar photo
      Tony Redmond

      You’ll need to use the message trace logs rather than the Graph usage APIs.

  4. Mike Koch

    Hi Tony – Running the script as downloaded from Github (no changes), I’m getting a repeating InvalidOperation error at line 49, shown below, running Powershell 7.3.6, Microsoft.Graph 2.3.0, Microsoft.Graph.Beta 2.3.0.

    Welcome To Microsoft Graph!
    Finding user accounts to process…
    Fetching mail activity usage report data…
    InvalidOperation: C:\temp\Report-MailUsageDomains.PS1:49
    Line |
    49 | $ReportLine = [PSCustomObject] @{
    | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    | You cannot call a method on a null-valued expression.
    InvalidOperation: C:\temp\Report-MailUsageDomains.PS1:49
    Line |
    49 | $ReportLine = [PSCustomObject] @{
    | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    | You cannot call a method on a null-valued expression.

    Am I missing something obvious? Thanks!

    1. Avatar photo
      Tony Redmond

      What’s in the $ReportLine variable? Something in it is causing the issue.

      Does the user account being processed have a value in the Mail property?

      1. Mike Koch

        Yes, that’s exactly the problem. We have a large number of users that don’t have email. I’ll have to filter them out of the $Users array. Thanks for the quick response, sir.

        1. Avatar photo
          Tony Redmond

          Or change the script to report based on their UPN. That is, if the UPN matches their primary SMTP address.

          1. Avatar photo
            Tony Redmond

            I updated the script in GitHub to use the UPN if a user Mail field is blank or empty.

  5. Clément BONNIN

    Hello,
    Thanks for this script 🙂
    I just had to replace Get-MgReportEmailActivityUserDetail by Get-MgBetaReportEmailActivityUserDetail to make it work.

    1. Avatar photo
      Tony Redmond

      You shouldn’t have to use the beta version. I just reran the script and was able to use Get-MgReportEmailActivityUserDetail. What version of the SDK are you using (I am using 2.3).

  6. jan sleeboom

    I just ran your script after downloading it from the GitHub.
    However the output is zero on all and wonder if this is the cause of the problem?:
    # Do not use our scripts in production until you are satisfied that the code meets the needs of your organization. Never run any code downloaded from
    # the Internet without first validating the code in a non-production environment.
    Welcome To Microsoft Graph!
    Finding user accounts to process…
    Get-MgBetaAdminReportSetting : The term ‘Get-MgBetaAdminReportSetting’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name,
    or if a path was included, verify that the path is correct and try again.
    At line:14 char:6
    + If ((Get-MgBetaAdminReportSetting).DisplayConcealedNames -eq $True) {
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (Get-MgBetaAdminReportSetting:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    Fetching mail activity usage report data…

  7. Dirk Burger

    Hi.
    Just wondering, on line 14 of your code you have:

    ForEach ($User in $Users) {

    But I can’t see where you define $Users ??? I assume its something to do with ‘Get-MgUser’ but again, I can’t see that either in your script… is it me??? Am I going script-blind???

    1. Avatar photo
      Tony Redmond

      If you look in the full script (in GitHub), you’ll see the line:

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

      But seeing that all of us could suffer from script blindness, I have added the line to the article.

      1. Dirk Burger

        Apologies… I should have got the script for the proper location… that’ll teach me! 🙂
        Many thanks for your understanding. Keep up the good work.

Leave a Reply