In my last post, I wrote about finding emails using Microsoft 365 Defender Advanced Hunting queries. I wanted to show something similar using just PowerShell and the Audit Logs in this short post.

For example, if we are using this Kusto Query Language (KQL) query:

EmailEvents
| summarize RList = make_list(RecipientEmailAddress) by Subject, NetworkMessageId
| project Subject, RList, NetworkMessageId

This query does the following:

  • It queries the EmailEvents table.
  • It groups the data by the Subject and NetworkMessageId fields and creates a list of recipient email addresses (RList) for each unique email using the summarize function and make_list() function.
  • It projects (selects) the Subject, RList, and NetworkMessageId columns to display in the final output.

Could we use the Microsoft Graph PowerShell SDK to get the same results?

Of course, the Kusto Query Language (KQL) query capabilities are far superior to calling the Microsoft Graph PowerShell SDK; however, using PowerShell, we can retrieve datasets and then “slice-and-dice” as we need.

To ensure we can query all email messages and not just our own (currently logged-in user), you must utilize an App Registration with the required permissions. To review how to create an App registration, you can refer to a previous post: 

Connect to Microsoft Graph PowerShell using an App Registration

Once you have this done, connect using the App registration and execute the following PowerShell.

# Connect to the Graph using the App registration details

Connect-MgGraph `
	-ClientId "05c53143-7c86-48b8-ac78" `
	-TenantId "4510da24-0f43-48d3-837d" `
	-CertificateThumbprint "3F813D3DAC8E3613199359D23AEBA"

Once connected, you can execute the core commands to query the desired information.

# Get all users

$users = Get-MgUser -Select "*"

# Initialize an empty array to store the summarized data

$summarizedData = @()

# Calculate the date 30 days ago

$startDate = (Get-Date).AddDays(-30).ToString("yyyy-MM-dd")

# Loop through each user

foreach ($user in $users) {
    if ($user.Mail) {
        $messages = Get-MgUserMessage `
            -UserId $user.Id -Select "*" `
            -Filter "ReceivedDateTime ge $startDate"

        foreach ($message in $messages) {
            $recipientList = $message.ToRecipients | `
                ForEach-Object { $_.EmailAddress.Address }
                     $summarizedData += [PSCustomObject]@{
                        ReceivedDateTime        = $message.ReceivedDateTime
                        Subject                 = $message.Subject
                        RecipientList           = $recipientList -join ', '
                        NetworkMessageId        = $message.InternetMessageId
                    }
              }
       }
}

# Display the summarized data

$summarizedData | Out-GridView

If you run both queries side-by-side, you may see a discrepancy in numbers simply because using the Microsoft Graph gives near real-time results, and the advanced hunting does not. The delay is typically a few minutes. However, it can vary depending on network conditions, system load, and the amount of data. In some cases, it may take up to 15-30 minutes for data to become available for querying in the Advanced Hunting system. It’s important to understand that Advanced Hunting provides quick results but not real-time due to this inherent delay.