“I have a request from management to report for a set of users where I need to report how many emails they send outside of the company, the recipients and date and time.”
My initial response was that the management involved must be devotees of mid-20th century time and motion methodologies. Further reflection brought me to the point where the request might be justifiable by some form of investigation into suspected wrongdoing. Hopefully, management has legal and HR advice about reporting user activity in this way. Assuming they do, it’s an interesting technical problem to solve.
Message Tracing Provides the Data
Exchange Online message tracing is core to the solution. You can track the path of messages through the Exchange Online transport system using GUIs in the Exchange admin center or Microsoft 365 Defender portal. Message tracing data is also available through PowerShell using the Get-MessageTrace cmdlet, which is what we use here.
Exchange Online retains message trace data for up to 90 days. However, only the last ten days are available online. To track messages sent to external recipients, we can interrogate the message tracing data and report what we find. This isn’t the only useful purpose for message tracing. For instance, you can understand how active distribution lists are by tracking their email activity.
Identify Target Mailboxes
First, we need to identify the set of target mailboxes to monitor. The simplest method is to run the Get-ExoMailbox cmdlet to find a set of mailboxes belonging to a certain department or matching another attribute. For example, this command returns all the mailboxes with the Office property set to Boston:
Given the nature of investigations, you might not find a suitable property to query against. In this situation, using one of the 15 custom attributes available for mailboxes is a good way to mark the target mailboxes.
I decided to use a different approach by creating a distribution group containing the target mailboxes. The account assigned ownership of the group will receive the reports. To avoid users seeing the new group, I hid it from address lists and gave it a difficult to guess email address:
With a set of target mailboxes, we can go ahead and do some message tracing to find details of their external email. Here’s the loop I used to run a message trace for each mailbox and extract records for external email by checking the recipient address. A PowerShell list stores details of the messages for use later.
Write-Host ("Checking external email for {0} mailboxes" -f $Users.count)
$Report = [System.Collections.Generic.List[Object]]::new()
ForEach ($User in $Users) {
Write-Host ("Checking messages sent by {0}" -f $User.DisplayName)
# Get message information for the last ten days and filter so that we end up with just external addresses
[string]$SenderAddress = $User.PrimarySmtpAddress
[array]$Messages = Get-MessageTrace -StartDate $StartDate -EndDate $EndDate -SenderAddress $SenderAddress -Status Delivered | ? {$_.RecipientAddress -notlike "*@Office365itpros*"}
ForEach ($M in $Messages) {
$ReportLine = [PSCustomObject][Ordered]@{
Date = Get-Date($M.Received) -format g
User = $M.SenderAddress
Recipient = $M.RecipientAddress
Subject = $M.Subject
MessageId = $M.MessageId }
$Report.Add($ReportLine)
} #End Foreach messages
} # End ForEach Users
Notifying Management by Email
The request didn’t specifically say how management should receive the report of external email activity. It’s easy to create output from the data and provide it as a CSV file or HTML document. For the purposes of this exercise (and because we have some suitable code), we’ll email the report using cmdlet from the Microsoft Graph PowerShell SDK.
Management say that they want to know about external recipients and message timestamps. I assume they also want to know who sends messages and the message subjects, so we’ll take that information extracted from the message traces and format it as a HTML bodypart.
The next steps are to select the mailbox to send the message, its recipient, and a subject. I use the logged-in user to send the message to the person who manages the distribution list. Alternatively, you could hardcode these assignments. Here’s what I did:
Finally, we build the message body using the HTML bodypart, create a draft message with the New-MgUserMessage cmdlet, and send it with Send-MgUserMessage. Here’s the relevant code:
# Construct the message body
$MessageBody = @{
content = "$($Body)"
ContentType = 'html'
}
# Create a draft message in the signed-in user's mailbox
$NewMessage = New-MgUserMessage -UserId $MsgFrom -Body $MessageBody -ToRecipients $EmailToRecipient -Subject $MsgSubject
# Send the message
Send-MgUserMessage -UserId $MsgFrom -MessageId $NewMessage.Id
Figure 1 shows what the message received by the manager looks like. The HTML formatting is basic and could be made much prettier if that need exists.
Figure 1: The message tracing report for external email activity
Making Things Work
Although I don’t agree with the idea of monitoring user email activity in any way, this is an interesting example of using the message tracing information available to all Exchange Online tenants to deliver a reasonable response to a request. Combined with some PowerShell snippets from other scripts, it’s possible to create a fully working solution in a few hours. To get started, you can download my code from GitHub.
Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.
Yes, you can search using eDiscovery. However, I suggest that there’s more work to do using that approach. The ask was to generate a report. You could do the same by running a compliance search and interpreting the results, but you wouldn’t get details like the email subject, which means that someone would then have to open the search results (exported to a PST) and check each message manually. That’s certainly a way to detect malfeasance, but it’s not the approach I would take.
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
If you have E3/E5 another way to go would be to use eDiscovery
Yes, you can search using eDiscovery. However, I suggest that there’s more work to do using that approach. The ask was to generate a report. You could do the same by running a compliance search and interpreting the results, but you wouldn’t get details like the email subject, which means that someone would then have to open the search results (exported to a PST) and check each message manually. That’s certainly a way to detect malfeasance, but it’s not the approach I would take.