Finding Emails Using Microsoft 365 Defender Advanced Hunting Queries

While working on some training course assets, I needed to execute “Advanced Hunting” queries from PowerShell. The logic is to get the results and then use advanced PowerShell capabilities for further analysis. At the same time as working on this, a friend posted a question:

I had a few attempts and came up with no answer. It made it worse as I was waiting in the car for my son to finish practice after school and had no way to test it, so I was guessing. 

Now if you are like me, questions like this play on your mind, and before you know it, you have lost a lot of time “playing” and trying to figure something out. Don’t get me started on some random SharePoint Framework thing I got hung up with for hours, literally hours!!

With that in mind, I decided to combine the two things, training assets and figuring out how to get the information Joanne was looking for. First, let’s recap what she was looking for:

1. Keyword Query to return all emails.
2. Filter the emails to where the only recipient in the “TO” field is the email address we specify.

You would think this would be easy, but you don’t have access to the function needed in straight “Keyword Query Language” used in Content Search. I decided to head into “Microsoft 365 Defender Advanced Hunting” to see if it works there. The good news is that a connected table called “EmailEvents” contains a history of messages. A simple usage of this could be to get it to show you all records; this is done by simply typing: 

EmailEvents

Then click “Run query,” and the results will appear. You can change to view a Custom time range, Last 30 days, Last 7 days, or Last 24 hours.

The syntax for this query and filter can be complex, but it becomes easier once you know the rules, like any querying language. Let us look at some basic examples:

# Get All Emails Containing ‘@onmicrosoft.com’ in the SenderFromAddress Column

EmailEvents | where SenderFromAddress contains 'onmicrosoft.com'

# Get All Emails Containing ‘@onmicrosoft.com’ in the RecipientEmailAddress Column

EmailEvents | where RecipientEmailAddress contains 'onmicrosoft.com'

# Get All Emails Containing ‘@onmicrosoft.com’ in the RecipientEmailAddress Column for a Specific Date

EmailEvents | where RecipientEmailAddress contains 'onmicrosoft.com' | where startofday(Timestamp) == startofday(datetime(2023-03-21T14:18:02Z))

# Get All Emails Where ‘Adele’ Is in the RecipientEmailAddress Column

EmailEvents | where RecipientEmailAddress contains 'adelev'

# Get All Emails Based on Populating a Variable for the Email we Want To Search

let Email = "adelev"; EmailEvents | where RecipientEmailAddress contains Email

Performing basic queries is relatively straightforward; however, we must delve deeper into a few options. Our first command is “summarize,” which allows us to transform the results; then, we need to look at the command “project,” which defines what we see in the results.

# Using Summarize – Return the Count of Emails to Adele With the Same Subject

let Email = "adelev"; EmailEvents | where RecipientEmailAddress contains Email | summarize  count() by Subject

# Using Project – Display Specific Fields

let Email = "adelev"; EmailEvents | where RecipientEmailAddress contains Email | project Subject, SenderMailFromAddress, Timestamp, RecipientEmailAddress

Now let’s add one last command called “mv-expand” which you use for expanding multi-value dynamic arrays or property bags into multiple records, often called “flattening.”

# Get Emails and Display All Recipients in a New Column Grouped by Subject

EmailEvents | extend OtherRecipients = split(RecipientEmailAddress, ';') | mv-expand OtherRecipients | summarize Timestamp=max(Timestamp), GroupedRecipientEmailAddress=make_list(RecipientEmailAddress) by Subject | project Subject, Timestamp, GroupedRecipientEmailAddress

The example “expands” the property “RecipientEmailAddress” very similar to using the PowerShell “-ExpandProperty,” which allows populating a variable then using “mv-expand,” after which we create a new field name and instruct it to create a list of the emails we found, then lastly grouping it all by the subject. 

Now back to the original question, can we create a query that gets all email messages with Adele’s Email as the only recipient in the “TO (RecipientEmailAddress)” field? With a bit of adjustment, the answer is yes 🙂

let Email = "adelev@msdx.onmicrosoft.com"; EmailEvents | summarize RecipientEmailList = make_list(RecipientEmailAddress), count() by Subject, NetworkMessageId | extend RecipientCount = array_length(RecipientEmailList) | where RecipientEmailList contains Email and RecipientCount == 1 | project Subject, RecipientEmailList, NetworkMessageId

Here is a summary of what it does:

1. It defines a variable Email and sets it to the email address “adelev@msdx.onmicrosoft.com“.
2. It queries the EmailEvents table and summarizes the data by grouping it based on the Subject and NetworkMessageId fields, creating a list of recipient email addresses (RecipientEmailList), and counting the number of events for each group.
3. It extends the summarized data with a new column RecipientCount, which represents the number of recipients in the RecipientEmailList.
4. It filters the results to keep only the rows where the RecipientEmailList contains the email address specified in the Email variable and the RecipientCount equals 1.
5. It projects (selects) the Subject, RecipientEmailList, and NetworkMessageId columns to display in the final output.

So can it be done?

Yes, it can, with some persistence and trying to figure it out. That old phrase, “Where there’s a will, there’s a way,” comes to mind. The key here is another reason why understanding all the features Microsoft 365 has to offer is critical. Hopefully, these queries are helpful.