Using Get-MailDetailSpamReport PowerShell cmdlet | View and export spam mail report | Part 2#3  5/5 (2) 34 min read

The PowerShell Get-MailDetailSpamReport cmdlet was created as a tool that simplifies the process of viewing and “dumping” information stored in Exchange Online spam log file.The Exchange Online spam log file, serve as a store that holds the documentation for each of the “spam mail events” that relates to incoming and outgoing mail flow.

In the current article, we review how we can expand and enhance the capabilities of the Exchange Online PowerShell cmdlet – Get-MailDetailSpamReport.

In the first part, we review the basic PowerShell syntax examples that can be used with the Get-MailDetailSpamReport cmdlet.

In the second part, we will review the more advanced use of the Get-MailDetailSpamReport cmdlet, by using a PowerShell script that will help us perform a sequence of tasks and to generate various types of spam mail reports (by using search queries).

Part 1#2 – basic use of the Get-MailDetailSpamReport cmdlet

Display – spam mail report information

To be able to get the spam mail report, all we need to do is just type the name of the Get-MailDetailSpamReport cmdlet without any other mandatory parameters.

For example:

Get-MailDetailSpamReport output

Export spam mail report information to CSV file.

To be able to export the result from the spam mail report to a CSV file, we can use the following syntax:

PowerShell command syntax

PowerShell command example

Export spam mail report information to CSV file | Specific Date range

In this scenario, we want to export the result from the spam mail report to a CSV file, but this time. We want to define a specific date range for the spam mail report.

To define the required date range, we use the parameters StartDate and EndDate.

PowerShell command syntax

PowerShell command example

Note – Later , we review more sophisticated methods that we can use for defining that required date range.

Brief prefix – spam mail sender versus spam mail receiver

The spam log file includes many “properties” of each spam mail events.

One of the most important distinctions that we need to be familiar with is – the event in which some entity (represented as E-mail address) sends spam mail. The meaning is – the side that holds the rule of the “attacker,” versus the entity of the user or the recipient who receives the spam emails meaning, the victim of the spam mail attack.

Spam mail - The Sender versus the Receiver -01

The implementation of this distinction is implemented by using the following PowerShell parameters:

  • The PowerShell parameter – SenderAddress, define the entity that sends the spam mail
  • The PowerShell parameter – RecipientAddress, define the entity that receives the spam mail.

Spam mail - The Sender versus the Receiver -02

Get spam mail report | Filter results – specific spam mail receiver

In this scenario, we want to get information about all the spam mail events, in which the spam mail was sent to (received by) a specific “destination E-mail address”.

To define the identity of the “receiver”, we use the PowerShell parameter RecipientAddress, and provide the E-mail address of the destination recipient.

PowerShell command syntax

PowerShell command example

Get spam mail report | Filter results | Specific spam mail Sender

In this scenario, we want to get information about all the spam mail events, in which the spam mail was sent from (sent by) a specific E-mail address.

To define the identity of the “sender,” we use the PowerShell parameter SenderAddress, and provide the E-mail address of the sender.

PowerShell command syntax

PowerShell command example

Get spam mail report | Filter results | Spam mail RECEIVER that their E-mail address includes specific domain name suffix

In this scenario, we want to get information about spam emails that were sent to specific recipients (destination E-mail addresses). Notice that this time we are interested in “multiple recipients” versus the default PowerShell parameter “RecipientAddress” that relate to a single entity of the recipient.

Our wish is to get information about all the spam emails, that was sent to recipients, which their E-mail address includes a specific domain name suffix.
For example, get information about all the spam emails that were sent to our organization recipients, meaning, recipients whom their E-mail address includes our organization domain name suffix.

In this scenario, we use the PowerShell parameter “Where-Object” which filter the information based on the search query that we define. In our scenario, the search query syntax is:

PowerShell command syntax

PowerShell command example

Get spam mail report | Filter results | Spam mail SENDERS that their E-mail address includes specific domain name suffix

This scenario is similar to the former scenario, but this time; we want to get information about all spam emails events, which relate to the “spam mail senders” (the entity that sends the spam mail) which use E-mail with a specific domain name suffix.

PowerShell command syntax

PowerShell command example

Part 2#2 – More advanced use of the Get-MailDetailSpamReport cmdlet

General concepts of Get-MailDetailSpamReport cmdlet use

The term “spam mail event”

Along the current article, we will mention the term “spam mail event” a couple of times. This term is not a formal term, but instead, my term that I use.

The term “spam mail event,” define a recorded in the Exchange Online spam mail log file, that includes a documentation of one of the two options:

  • Spam mail that was Sent From (Sent by) a specific entity (E-mail address).
  • Spam mail that was Received by (Sent to) specific entity (E-mail address).

The term “spam mail event record” includes the properties of the events such as date, message ID, message subject and so on.

Optimize the way we use Date ranges

As far as I know, at the current time, there is no formal information about the default time range that is used by the Get-MailDetailSpamReport PowerShell cmdlet.

Note – I don’t know what is the maximum date range of spam mail events that saved in the Exchange Online spam mail log file. From my experience, we can “fetch” information about spam mail events within 30 days ranges or more.

To be able to define that exact date range, we can add to the basic Get-MailDetailSpamReport PowerShell cmdlet the parameters – StartDate and EndDate, which enable us to set the exact date range.

For example

In the current article, I will use a customization of “Date range parameters.”
We will use a simple PowerShell formula, which defines Date range that will be measured in “day’s units.”

In our examples, we define Date range of 30 days.
Note – using this simple PowerShell formula, you can very easily create the required date rage customization for your specific needs.

The End Date value

Instead of manually providing the value for the “End Date”, we use a variable, that stores the results that we get from the PowerShell cmdlet Get-Date.

The PowerShell cmdlet Get-Date is configured to “fetch” the current date.

The Start Date value

Instead of manually providing the value for the “Start Date,” we use a variable that stores the results of a simple formula. The formula that we use, take the current date and subtract from this date the number of days that we specify.

The result of this formula will be – the “Start Date.”

An example of the two variables that we will use along this current article is:

In this example, we define a date range of “30 days”.

In the following diagram, we can see an example of the “date range” concept.

The Date Range

Dump the content of the Exchange Online spam mail Log file

In this section, we review the how to “dump” the content of the Exchange Online spam mail Log file, but the point is a trick that we use that will help us to bypass the default limitation of the
Get-MailDetailSpamReport PowerShell cmdlet, so we will be able to export (dump) the whole content of the Exchange Online spam mail Log file, for the date ranges that we define.

Export Spam Mail Reports | ALL Spam mail events | Last 30 days

Our mission: export all existing spam mail events from last 30 days.

Notice that in this scenario, we don’t want to use any type of filter or define a specific search query. Instead, we just want to “dump” all the information from the Exchange Online spam mail Log file to – a local file.

Exchange Online and secret data limitation default settings

When we ask for Exchange Online to “fetch” information that is stored in the data center Log file, the little secret that is not known to most of us is, that by default, Exchange Online will not “volunteer” to provide us 100% of the information.

Even when we provide a defined “date range,” Exchange Online is configured to provide a “restricted amount of data.”

The reason for this built-in limitation is probably some kind of “server protection mechanism” that was created for preventing excess loads from Exchange Online server or prevent excess loads of communication lines by limiting the amount of data that flows from the “cloud.”

The data measurement unit – the page   

The way that we use to measure the “amount of data” that Exchange Online server provides, defined using a measurement unit named “page.”

Each “page” can contain the maximum number of “rows.”
In case that we need to get more information than the information that can be stored in a single “page,” we need to “instruct” Exchange Online to provide us additional “pages.”

Page content default and maximum limit

  • The default number of “events” (Log rows) that appear on a single page is – 1,000.
  • The maximum number of “events” (Log rows) that can appear on a single page is – 5,000.

Pages default and maximum limit

  • The default number of pages is – 1.
  • The maximum number pages that can be provided by Exchange Online is – 1,000.

The concept of – Page

If you like to do the match, the maximum results that Exchange Online can provide us are – 5 million events (5,000,00000).

The maximum number of events that Exchange Online can provide

Exchange Online and the Get-MailDetailSpamReport cmdlet default settings

Regarding the output that we get from the Get-MailDetailSpamReport PowerShell cmdlet, the default setting is configured to produce “1 page” that contains a maximum of one thousand lines (1 X 1,000).
In other words, by default, the Get-MailDetailSpamReport PowerShell cmdlet, is configured to provide us only the “first page” from existing information that is stored in the Exchange Online spam mail Log file.

To demonstrate this concept, let’s use the following scenario: Exchange Online spam mail log, including a documentation of 15,000 spam mail events.
(The Exchange Online spam mail log file includes –  15,000 rows).

In case that we use the Get-MailDetailSpamReport PowerShell cmdlet without any filters or parameters, Exchange Online will “send” us information about 1,000 events.

A quick reminder – Exchange Online is configured to provide by default only one “page” and each page is configured by default to a maximum of 1,000 “rows.”

This “default configuration,” could lead to problems with “data integrity” because, we cannot see the full picture and instead, we see only part of the whole information.

The good news is the Get-MailDetailSpamReport PowerShell cmdlet, allow us to define two important parameters:

  • Page size – the maximum number of “events” (rows) that each page contains. The maximum rows that a page can contain is – 5,000.
  • The number of pages – the number of pages that we “ask to get”.

Additional reading

In our scenario, in case that we want to get “Full spam mail report” that will include 100% of the available information, we will need to “expend” the default Get-MailDetailSpamReport PowerShell command syntax in the following way:

Using a PowerShell script that will dump all the information from the spam log file

Although this “solution” look like a satisfactory answer to our problem, we have additional issues that we need to solve!

In the current scenario, I mentioned that we “know” that the Exchange Online spam mail log file includes 15,000 rows but, in reality, how can we really know what is the number of events that are stored in the log file, so we can make the required adjustment to the PowerShell command syntax?

And the answer is, that most of the times, we don’t know what is the exact “number” of rows (events) that appeared in the Exchange Online Server Log file.

To be able to deal with this challenge, we can use a little PowerShell “tweak” that will provide the required solution.

The solution is implemented by using a “loop” procedure, that will instruct Exchange Online to provide us the required results, using a page size of 5,000 rows.

In case that there is additional information in the Exchange Online spam mail log file, the PowerShell “loop command” asks for Exchange Online to create a “NEW page,” that will contain the next 5,000 results and so on and so forth.

This loop process will run until we get all the available information that is stored in the Exchange Online spam mail log file.

In the following section, we can see an example of such a solution. The PowerShell script will perform the following sequence of actions:

Define the date range as a date range of 30 days (30 days going backward from the current date).

  1. Automatically create in drive C: NEW folder structure, that will serve as a container for the information that will be exported.
  2. The “data” that is collected from the PowerShell “loop process” that fetches all the available information from Exchange Online spam log file, will be saved in a variable named – $SpamMailLogFileContent.
  3. The content of the variable named – $SpamMailLogFileContent will be exported to a CSV file.

Export all information from Exchange Online spam log file | Last 30 days

Count Sent and Received Spam emails | Spam mail report.

In this section, I would like to demonstrate how to use the Get-MailDetailSpamReport PowerShell cmdlet, for generating a specific type of spam mail report, which will count the number of specific spam mail events versus the standard output of the Get-MailDetailSpamReport PowerShell cmdlet that display information about a specific spam mail event (the details about the specific spam mail event).

By default, the Get-MailDetailSpamReport PowerShell cmdlet doesn’t “know” how to count spam mail events. In the current section, we will demonstrate how to use PowerShell script that will “add” this required capacity.

Counting the sum of spam mail events

The goal we seek to achieve is, to count the following types of spam mail events:

  • Sum (count) the amount of spam mail that was Received (sent to) specific entity.
  • Sum (count) the amount of spam mail that was sent from (Sent by) a specific entity.

In our scenario, the term “entity” is translated to – “E-mail address.”
The “E-mail address identity,” could be the E-mail address of the external recipient (non-organization recipient) or the E-mail address of recipient organization.

Export Spam Mail Summary | Count (sum) all received Spam emails | All E-mail Addresses

In this section, we review how to use PowerShell script, which will generate a spam mail “summary report”, that count all the spam emails that were Sent to (Received by) each E-mail address that appears in the Exchange Online spam log file.

The goal we seek to achieve is, to count the following type of spam mail events:

  • Sum (count) the amount of spam mail that was Received by (sent to) specific entity.

Spam mail - The Sender versus the Receiver -01

Before we begin with the description of the PowerShell script that includes many different parts, I would like to present the basic PowerShell command structure that we use for counting the number of spam mail event that received (sent to) a specific E-mail address.

The basic PowerShell command syntax

Before we provide the “complex” PowerShell syntax, let’s view the basic PowerShell syntax structure that we need to use in this scenario:

In this scenario, the PowerShell script that will implement the following sequence of tasks:

Task 1 – Get information from Exchange Online spam mail log file about all the spam mail events, which occurred in the last 30 days.

The content of the spam log file is fetched by the PowerShell command that we revive in the section XXX.

We use the variable – $SpamMailLogFileContent as a logical container, that will store the content of the spam log file.

Task 2 – Create a list of all E-mail addresses that appear in the Exchange Online spam mail log file.

The list of all E-mail addresses from the Exchange Online spam log file that appears “under” the column – “spam mail Receiver,” is created by using the following PowerShell command:

We use the variable – $ALLSpamMailRecipients as a logical container, that will store the list of the E-mail addresses.

Task 3 – Create a folder hierarchy which will store the exported spam mail summary report.

Task 4 – Run a loop process, which will take the “first E-mail address” on the list, and count all the spam mail events that relate to this E-mail address.
Then the loop process will “move on” to the next E-mail address in the list until he reaches the last E-mail address in the list.

The information about each E-mail address and the “sum” of receiving spam events will be written to the PowerShell console and in parallel exported to a report file.

The output from the loop process takes place in a dedicated space which describes as – a hash array.

In our scenario the hash array named – $Results = @()

The loop process in which we query the spam log file for each E-mail address, is implemented by the following PowerShell command:

We use the variable – $ReceiveEvents as a logical container, that will store the information about all spam mail events that are related to the specified E-mail address (represented as $ID1)

Counting the spam mail events

Given that we have the list of spam mail events that are related to the specific E-mail address (specific receiver), we can use PowerShell formal, that we count the number of the existing spam mail events.

The counting process, is implemented by the following PowerShell command:

We use the variable – $ReceiveEventsCount as a logical container, that will store the “counting results” for the specific E-mail address.

Task 5 – Write the information that was saved in the hash array to – CSV file.
The PowerShell command that we use for exporting spam mail report to CSV file is:

We use the variable – $ResultsExport as a logical container, that store all the result from that was gathered during the loop process. The meaning is – each E-mail address of “spam mail receiver” and the sum of the spam emails (spam events) that was sent to the specific E-mail address.

The PowerShell script Count (sum) all received Spam emails

Export Spam Mail Summary | Count (sum) all Sent Spam emails | All E-mail Addresses

The current scenario is similar to the former scenario which we review in the former section.

The main variance from the former scenario is that now, our main focus is about the entities that send spam mail.

The goal we seek to achieve is, to count the following type of spam mail events:

  • Sum (count) the number of spam mail that was sent from (Sent by) a specific entity.

Export Spam Mail Summary -Count (sum) all SENT Spam emails -02

The basic PowerShell command syntax

The basic PowerShell syntax structure that we need to use in this scenario is:

The PowerShell script – Count (sum) all Sent Spam emails

Generate Dedicated Spam Mail Reports For Each Exchange mailbox user (Bulk mode)

In this section, we use a method that will generate a “dedicated spam mail report” for each Exchange Online User E-mail address, that appears in the Exchange Online spam mail log file.

I use the term “dedicated,” for describing the process in which the PowerShell will automatically create a dedicated folder for each Exchange Online User E-mail address.
This folder will serve as a container that stores the “dedicated spam mail report” that includes information about – all the spam mail events that are related to the specified E-mail address.

Generate a Dedicated Spam Mail Reports For Each Exchange mailbox user

We will review two variations of this scenario.

Scenario 1 – in this scenario, we create a dedicated spam mail report for each Exchange Online User E-mail address, which appears as an E-mail address that “RECEIVE” spam mail.
In this case, our purpose is to analyze spam mail events in which hostile element attacks our organization recipients.

Scenario 2 – in this scenario, we create a dedicated spam mail report for each Exchange Online User E-mail address, which appears as – E-mail address that “SEND” spam mail.
In this case, our purpose is to analyze spam mail events, in which one of our organization users is distributing (Intentionally or unintentionally) spam mail.

Export dedicated Spam Mail events Report | RECEIVED Spam Mail | For Each Exchange mailbox user recipient

In the current scenario, we would like to get a detailed information about spam mail events, in which we organotin users are being attacked by the hostile element that sends them spam mail.

The PowerShell script that will implement the following sequence of tasks:

Task 1 – Get information from Exchange Online spam mail log file about all the spam mail events, which occurred in the last 30 days.

Task 2 – Create a list of all E-mail addresses of Exchange Online recipients who describe as “Exchange Online users” (user with a mailbox).

The “list” of the Exchange Online user recipient will be created by using the following PowerShell command:

We use the variable $GetMBXUser as a logical container, that will store the list of Exchange Online Users E-mail addresses.

Task 3 – Scan the information that was fetched from the Exchange Online spam log file, and perform a loop process.

The PowerShell script will take the “first E-mail address” in the list, and check if there is any information in the spam log about – spam mail events in which spam mail was sent to the specific E-mail address.

In case that there is no information that related to the specific E-mail address, the PowerShell script will continue to implement the same process for the “next E-mail address” in the list.

In case that the PowerShell script finds information about spam emails that was sent to the specific E-mail address, the PowerShell script will perform the following actions:

3.1 – Create a dedicated folder, and name the folder using the Display name of the Exchange Online user recipient.

3.2 – Generate spam event log that includes all the spam mail events in which spam mail was sent to the specified E-mail address (described as Received spam).

The PowerShell script – Export Dedicated Spam Mail events Report | RECEIVED Spam Mail

Export Spam events Mail Report | SENT Spam Mail | For Each Exchange mailbox user recipient

The current scenario, is similar to the former scenario which we review in the former section.

The main variance from the former scenario is that now, our main focus is about the “entities” that send spam mail.

In this case, we want to create a dedicated spam mail report for each Exchange Online User E-mail address, that appears in the Exchange Online spam log file as – E-mail address that sends spam mail.

The PowerShell script – Export Dedicated Spam Mail events Report | SENT Spam Mail

Export Spam Mail Summary | Count (sum) Sent Spam emails | All E-mail Addresses | only if sent item value is Greater than X

In the current scenario, we would like to count spam mail events of “spam mail senders” (spam mail that was sent from a specific E-mail address).

The difference from the former scenarios in which we count the spam mail that was sent by a specific E-mail address is that this time we want to view information only when the number of the sent spam mail items passes a certain threshold.

In our specific example, we define the number “10” as the threshold.

The basic PowerShell command syntax

The basic PowerShell syntax structure that we need to use in this scenario is:

xxx


Export SPAM mail reports – Office 365

PowerShell menu based script, that will help you to display and export information (to various file formats -TXT, CSV, and HTML) about – spam mail events in an Office 365 based environment. The spam mail report includes – summary spam mail report, which counts the sent and received spam mails for specific recipients or detailed spam mail report, that includes a description of each spam mail event.

The next article in the current article series

Now it’s Your Turn!
It is important for us to know your opinion on this article

Related Post

Please rate this

Eyal Doron on EmailEyal Doron on FacebookEyal Doron on GoogleEyal Doron on LinkedinEyal Doron on PinterestEyal Doron on RssEyal Doron on TwitterEyal Doron on WordpressEyal Doron on Youtube
Eyal Doron

Share your knowledge.

It’s a way to achieve immortality.

Dalai Lama


Leave a Reply

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