Finding Phish in Office 365

Imagine this scenario. Your logs show A dozen accounts logging in from the same unknown source IP with the same user agent on the same day. This activity is not normal for this group of people. You know you have a compromise on your hands. So you do what needs to be done and you revoke access and lock the accounts down. But how did this happen? It seems that many people fell victim to the same attacker. Was it malware? Password spray? Did someone get access to all the hashes in AD? Phishing!?

No one knows for sure, but chances are it’s a phishing attack. Perhaps all our victims fell for the same phishing attack. We can use logs from Office 365 to find out! Let’s see what that looks like.

Message trace logs

Office 365 maintains 90 days of message trace logs. These logs show the date and time, sender, recipient, subject, and delivery information of message that traverse Exchange. We can get access to the trace logs through the Security & Compliance portal,

If you limit the size of your query you can view the message trace logs right in the browser. If you try to enter all 12 at once you’ll get an error. We will queue up log retrieval jobs for the past 90 days, one log per recipient.

And 30 minutes later I now have notifications telling me my log retrievals have completed. Let’s download them all and get them into PowerShell.


Import-Csv will convert each line of the file into a PowerShell object we can manipulate. When importing data, I like to start by looking at the first record using select. This will give us an idea of the fields we can use.

Well that’s a mess. Unfortunately, the format that comes from Office 365 isn’t very useful to us in PowerShell. We can fix this by opening each file in excel and saving it. Excel will reformat the file into something PowerShell can consume.

This looks better.

To correlate the unique messages with the users we need a list of unique messages per user. We don’t care about the time the message was sent since each user presumably received the same message at different times. Looks like we have everything we need to do that here. We can use sender_address and message_subject to create our unique list per user.

Let’s use ForEach to create a string of sender_address space message_subject.

| ForEach{$_.sender_address + " " + $_.message_subject} | select -First 10

Now let’s make that list unique with group.

| ForEach{$_.sender_address + " " + $_.message_subject} | group | select -first 10

We only need the name from the output group object.

| ForEach{$_.sender_address + " " + $_.message_subject} | group | select name | select -first 10

Now we can put this into a file so we can compare files to each other and look for common senders and subjects across the users.

We could do this once per file and call them something like unique_list 1 through 12 but we are working in PowerShell! We can do a lot better. Here’s a block of PowerShell that will create the unique list output file per input file.

# get a list of all source files to a foreach loop
dir MTSummary_Message* | ForEach {
  $SourceFileName = $_.Name
  $TargetFileName = $_.Name -Replace "MTSummary_Message", "unique_list"
  Import-Csv $SourceFileName | ForEach{$_.sender_address + " " + $_.message_subject} | group | select name | Export-Csv -NoTypeInformation $TargetFileName

Now we have 24 files in our trace_logs directory. The original set and a new set of just the unique list of senders and subject lines.

We have to compare the files to each other. We want to identify all the common messages that were sent to each user. We can do this by importing all the lists and grouping them again.

# create an array to store the content of all csv
$all = @()
# get a list of all source files to a foreach loop
dir unique_list* | ForEach {
  # Get an array of the names from the file
  $content = (Import-Csv $_).Name
  # append the file array into the big array
  $all += $content
$all | group | select -First 10

This could take a minute or two.

And now comes the magic moment. We are going to select the group objects where the count is 12. This will give us a list of every message that was sent to all 12 people over the past 90 days.

$all | group | Where{$_.Count -eq 12}

Now we have a manageable list of 53 messages that all 12 have received in the past 90 days. Looking through the list one stands out.

And there my friends is the needle in the haystack!

Follow up

Now that we know this phishing attack exists. We can run one last message trace; all messages from this sender. The resultant message trace log will show us all potential victims that received this phishing message and we can follow up with them more personally to ensure they were not also compromised.

For all your users, perform an e-discovery request to retrieve this message and share the details of the attack with them. Your end users are the most important assets you have in stopping these attacks. Educate them as much as possible.


Leave a Reply

Your email address will not be published. Required fields are marked *