Skip to content

Using Get-MailDetailSpamReport PowerShell cmdlet | View and export spam mail report | Part 2#3

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 serves as a store that holds the documentation for each of the “spam mail events” that relate 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.

Article series | Using Get-MailDetailSpamReport | View and export spam mail report

  1. Office 365 spam mail report using PowerShell | Introduction
  2. Using Get-MailDetailSpamReport PowerShell cmdlet | View and export spam mail report (this article)
  3. How to use the spam mail report PowerShell script

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. Basic use of the Get-MailDetailSpamReport cmdlet

Display – spam mail report information

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 | FT Date,Subject,Direction,SenderAddress,RecipientAddress

PowerShell Get-MailDetailSpamReport output:

PS C:\> Get-MailDetailSpamReport | FT Date,Domain,Subject,Direction,SenderAddress,RecipientAddress,EventType,MessageSize,MessageId,MessageTraceId

Date                   Domain                    Subject                                                                                                           Direction SenderAddress                               RecipientAddress
----                   ------                    -------                                                                                                           --------- -------------                               ----------------
2017-09-28 1:42:16 PM  o365info.com              INBOX SMTP,RDP,UNLIMITED WEBMAIL,FRESH LEADS,FUND TRANSFER TO ALL BANKS IN USA..all available now.                Inbound   ojomade200@gmail.com                        alice@o365info.com
2017-09-28 1:42:25 PM  o365info.com              Undeliverable: INBOX SMTP,RDP,UNLIMITED WEBMAIL,FRESH LEADS,FUND TRANSFER TO ALL BANKS IN USA..all available now. Outbound  postmaster@o365info.com                     eyal@o365info.com
2017-09-28 1:42:25 PM  o365info.com              Undeliverable: INBOX SMTP,RDP,UNLIMITED WEBMAIL,FRESH LEADS,FUND TRANSFER TO ALL BANKS IN USA..all available now. Outbound  postmaster@o365info.com                     ojomade200@gmail.com
2017-09-28 7:50:37 PM  o365info.com              Scan Data                                                                                                         Outbound  documents@o365info.com                      u003eisabely@o365info.com
2017-09-28 8:24:52 PM  o365info2.onmicrosoft.com Cloud App Security: suspicious administrative activity detected                                                   Inbound   no-reply@cloudappsecurity.com               admin@o365info2.onmicrosoft.com
2017-09-29 12:41:46 AM o365info.com              RE: ANITA ROBERTS ( DEVCOIN CRYPTOCURRENCY )                                                                      Inbound   anita.roberts@devcoin.com                   eyal@o365info.com
2017-09-29 2:39:59 AM  o365info.com              Scan Data                                                                                                         Outbound  scanner@o365info.com                        john@o365info.com
2017-09-29 7:02:53 PM  o365info.com              Become a Target Content Curator Today!                                                                            Inbound   target@affiliates.acceleration-partners.com eyal@o365info.com
2017-09-29 11:31:00 PM mottie.com                Invoice                                                                                                           Outbound  Lynne@mottie.com                            eyal@o365info.com
2017-09-30 5:16:18 AM  alwasmiwater.com          BENEFICIARY PAYMENT ADVICE                                                                                        Outbound  accounts@alwasmiwater.com                   eyal@o365info.com
2017-09-30 6:38:37 AM  o365info.com              Top On Web                                                                                                        Inbound   info@webdeta.biz                            eyal@o365info.com
2017-09-30 7:07:23 AM  eenvoudigonline.be        Invoice                                                                                                           Outbound  Kaye@eenvoudigonline.be                     whiskers@o365info.com
2017-09-30 8:04:06 AM  timberlineroof.net        Invoice                                                                                                           Outbound  Millard@timberlineroof.net                  job@o365info.com
2017-09-30 9:24:07 AM  o365info.com              Invitation: UNITED BANK OF AFRICA @ Friday, 29 September 2017                                                     Inbound   onyema.onye@yahoo.com                       alice@o365info.com
2017-10-01 7:43:34 PM  interia.com               QUOTATION  doc20171002                                                                                            Outbound  tina.anish@interia.com                      eyal@o365info.com
2017-10-02 4:09:56 AM  o365info.com              Hi                                                                                                                Inbound   Katherineqvs@cynjin.com                     ht@o365info.com
2017-10-02 8:24:58 AM  o365info.com              What's up                                                                                                         Inbound   Violetteeqog@valedotijuco.com.br            ui@o365info.com
2017-10-02 10:36:54 PM o365info.com              Hey, Sweety                                                                                                       Inbound   Evelinann@fibertel.com.ar                   ui@o365info.com
2017-10-03 1:15:58 AM  o365info.com              Emailed Invoice - 822287                                                                                          Outbound  Muriel@o365info.com                         paramedi@o365info.com
2017-10-03 2:19:17 AM  o365info.com              Hey, Sweety                                                                                                       Inbound   Evelinacmgfl@telkom.net.id                  ht@o365info.com

Export spam mail report information to CSV file.

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

PowerShell command syntax:

Get-MailDetailSpamReport | Export-CSV <Path> –NoTypeInformation -Encoding utf8

PowerShell command example:

Get-MailDetailSpamReport | Export-CSV c:\temp\"spam mail report.CSV" -NoTypeInformation -Encoding UTF8

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:

Get-MailDetailSpamReport -StartDate <Date> -EndDate <Date>

PowerShell command example:

Get-MailDetailSpamReport -StartDate 09/01/2017 -EndDate 10/01/2017

Brief prefix – spam mail sender vs. 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,” vs. 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:

Get-MailDetailSpamReport -RecipientAddress <Sender E-mail address>

PowerShell command example:

Get-MailDetailSpamReport -RecipientAddress "john@o365info.com"

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:

Get-MailDetailSpamReport -SenderAddress <Sender E-mail address>

PowerShell command example:

Get-MailDetailSpamReport -SenderAddress "john@o365info.com"

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” vs. 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:

{$_.RecipientAddress -like "*@<Domain name suffix>*"}

PowerShell command syntax:

Get-MailDetailSpamReport -StartDate <Date> -EndDate <Date> | Where-Object {$_.RecipientAddress -like "*@<Domain name suffix>*"} | Select SenderAddress,RecipientAddress ,Date

PowerShell command example:

Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate | Where-Object {$_.RecipientAddress -like "*@o365info.com*"} | Select SenderAddress,RecipientAddress ,Date

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 previous 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:

Get-MailDetailSpamReport -StartDate <Date> -EndDate <Date> | Where-Object {$_.SenderAddress -like "*@<Domain name suffix>*"} | Select SenderAddress,RecipientAddress,Date

PowerShell command example:

Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate | Where-Object {$_.SenderAddress -like "*@o365info.com*"} | Select SenderAddress,RecipientAddress,Date

Part 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:

Get-MailDetailSpamReport -StartDate 09/01/2017 -EndDate 10/01/2017

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 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:

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

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”.

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:

Get-MailDetailSpamReport -PageSize 5000 -Page 3

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

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

# Define the Date format variable

$Datef = Get-Date -Format "\Da\te dd-MM-yyyy \Ti\me H-mm"

# Define variables that contain the folder names

$A20 = "C:\INFO\Spam mail Reports"

$A21 = "$A20\ALL Spam mail events - Last 30 days - $Datef"

# C:\INFO\Spam mail Reports

IF (!(Test-Path -path $A20))

{ New-Item $A20 -type directory | Out-Null }

# ALL Spam mail events - Last 30 days - <Date>

IF (!(Test-Path -path $A21))

{ New-Item $A21 -type directory | Out-Null }

# Define the variable for the exported Filename

$File1 = "Full spam mail report - All information - last 30 Days"

# Using PowerShell command that will verify that ALL the available information will be exported.

# Get the complete content of the Exchange Online spam mail Log file (all existing spam mail events in the last 30 days)

$SpamMailLogFileContent =

for ($c = 1; $c -lt 1001; $c++)
{

    if ((Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate -PageSize 5000 -Page $c).count -gt 0)
    {

        Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate -PageSize 5000 -Page $c

    }

    else

    { break; }

}

# Define a variable that store the content of the Exchange Online spam log file

$ExportSpamLog = $SpamMailLogFileContent | Sort-Object -Property Date -descending

# Export information to the CSV file

$ExportSpamLog | Export-CSV $A21\"$File1.CSV" -NoTypeInformation -Encoding utf8

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 vs. 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:

$ReceiveEvents = Get-MailDetailSpamReport -RecipientAddress <E-mail address> -StartDate <StartDate> -EndDate <EndDate>

$ReceiveEventsCount = @($ReceiveEvents).count

write-host "The Number of Received spam emails is: $ReceiveEventsCount"

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:

$ALLSpamMailRecipients = $SpamMailLogFileContent | Group-Object -Property RecipientAddress | Sort-Object name -descending | Select-Object name,count

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:

$ReceiveEvents = Get-MailDetailSpamReport -RecipientAddress $ID1 -StartDate $StartDate -EndDate $EndDate

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:

$ReceiveEventsCount = @($ReceiveEvents).count

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:

$ResultsExport | Export-CSV $A22\"$File1.CSV" –NoTypeInformation -Encoding utf8

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

# Define variables for a start date, and End date

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

# Define variables that contain the folder names

$A20 = "C:\INFO\Spam mail Reports"

$A21 = "$A20\Summary Spam Report"

$A22 = "$A21\1. Count RECEIVED spam mail report - Each Unique E-mail Address"

# Create folders Structure that contains the exported information

# C:\INFO\Spam mail Reports

IF (!(Test-Path -path $A20))

{ New-Item $A20 -type directory | Out-Null }

# Summary Spam Report

IF (!(Test-Path -path $A21))

{ New-Item $A21 -type directory | Out-Null }

# 2. Count SENT + RECEIVED spam mail report – each Unique E-mail Address - <Date>

IF (!(Test-Path -path $A22))

{ New-Item $A22 -type directory | Out-Null }

# Get the complete content of the Exchange Online spam mail Log file (all existing spam mail events in the last 30 days)

$SpamMailLogFileContent =

for ($c = 1; $c -lt 1001; $c++)
{

    if ((Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate -PageSize 5000 -Page $c).count -gt 0)
    {

        Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate -PageSize 5000 -Page $c

    }

    else

    { break; }

}

# Create a list of unique E-mail addresses that appear in the spam mail report | Spam Mail receiver (recipients that spam mail sent to them)

$ALLSpamMailRecipients = $SpamMailLogFileContent | Group-Object -Property RecipientAddress | Sort-Object name -descending | Select-Object name, count

# Define an array (hash) that will store information about spam mail events that fetched from the mail spam mail report

$Results = @()

ForEach ($SpamEvent in $ALLSpamMailRecipients)
{

    # Specific Unique identity (E-mail address)

    $ID1 = $SpamEvent.name

    # Display progress bar information on the PowerShell console

    Write-Progress -Activity "Export to Log Files - SPAM mail Report for $ID1 E-mail address - last 30 days"

    # Define a variable that store ALL spam mail events of emails that were received by (sent to) specified recipient

    $ReceiveEvents = Get-MailDetailSpamReport -RecipientAddress $ID1 -StartDate $StartDate -EndDate $EndDate

    # Count the number of - all spam emails events of E-mail that sent to the specified recipient

    $ReceiveEventsCount = @($ReceiveEvents).count

    write-host -ForegroundColor white ----------------------------------------------------------------------------

    write-host -ForegroundColor white "Spam mail information about - Exchange Online User " -NoNewline; Write-Host $ID1 -ForegroundColor white -BackgroundColor DarkGreen

    write-host -ForegroundColor white "(The last 30 days)"

    write-host

    write-host -ForegroundColor white "* Number of RECEIVED spam emails:" $ReceiveEventsCount

    write-host -ForegroundColor white ----------------------------------------------------------------------------

    # Define that array of properties, that appears in the spam mail report

    $Properties = @{

        "E-mail address"     = $ID1

        "Spam mail Received" = $ReceiveEventsCount

    }

    # Define a variable that store the information about each identity

    $Results += New-Object psobject -Property $properties

    $ResultsExport = $Results | Select-Object "E-mail address", "Spam mail Received"

}

# Define the variable for the exported Filename

$File1 = "Received Spam emails per unique E-mail Address"

# Export information about - Sent SPAM mail Report

$ResultsExport | Export-CSV $A22\"$File1.CSV" -NoTypeInformation -Encoding utf8

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

The current scenario is similar to the one we reviewed in the previous section.

The main variance from the previous 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:

$ReceiveEvents = Get-MailDetailSpamReport -SenderAddress <E-mail address> -

$SENTEventsCount = @($SENTEvents).count

write-host "The Number of Sent spam emails is: $SENTEventsCount"

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

# Define variables for a start date, and End date

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

# Define variables that contain the folder names

$A20 = "C:\INFO\Spam mail Reports"

$A21 = "$A20\Summary Spam Report"

$A22 = "$A21\2. Count SENT spam mail report - Each Unique E-mail Address"

# Create folders Structure that contains the exported information

# C:\INFO\Spam mail Reports

IF (!(Test-Path -path $A20))

{ New-Item $A20 -type directory | Out-Null }

# Summary Spam Report

IF (!(Test-Path -path $A21))

{ New-Item $A21 -type directory | Out-Null }

# 2. Count SENT + RECEIVED spam mail report – each Unique E-mail Address - <Date>

IF (!(Test-Path -path $A22))

{ New-Item $A22 -type directory | Out-Null }

# Get the complete content of the Exchange Online spam mail Log file (all existing spam mail events in the last 30 days)

$SpamMailLogFileContent =

for ($c = 1; $c -lt 1001; $c++)
{

    if ((Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate -PageSize 5000 -Page $c).count -gt 0)
    {

        Get-MailDetailSpamReport -StartDate $StartDate -EndDate $EndDate -PageSize 5000 -Page $c

    }

    else

    { break; }

}

# Create a list of unique E-mail addresses that appear in the spam mail report | Spam Mail Sender (E-mail address that sent spam mail)

$ALLSpamMailSenders = $SpamMailLogFileContent | Group-Object -Property SenderAddress | Sort-Object name -descending | Select-Object name, count

# efine an array (hash) that will store information about spam mail events that fetched from the mail spam mail report

$Results = @()

ForEach ($SpamEvent in $ALLSpamMailSenders)
{

    # Specific Unique identity (E-mail address)

    $ID1 = $SpamEvent.name

    # Display progress bar information on the PowerShell console

    Write-Progress -Activity "Export to Log Files - SPAM mail Report for $ID1 E-mail address - last 30 days"

    # Define a variable that store ALL spam mail events of emails that where SENT by specified recipient

    $SENTEvents = Get-MailDetailSpamReport -SenderAddress $ID1 -StartDate $StartDate -EndDate $EndDate

    # Count the number of - Spam emails that was sent by (Sent from) a specific E-mail Address

    $SENTEventsCount = @($SENTEvents).count

    write-host -ForegroundColor white ----------------------------------------------------------------------------

    write-host -ForegroundColor white "Spam mail information about - Exchange Online User " -NoNewline; Write-Host $ID1 -ForegroundColor white  -BackgroundColor DarkGreen

    write-host -ForegroundColor white "(The last 30 days)"

    write-host

    write-host -ForegroundColor white "* Number of SENT spam emails is: " -NoNewline; Write-Host $SENTEventsCount -ForegroundColor white  -BackgroundColor Darkred

    write-host -ForegroundColor white ----------------------------------------------------------------------------

    # Define that array of -properties, that appears in the spam mail report

    $Properties = @{

        "E-mail address" = $ID1

        "Spam mail SENT" = $SENTEventsCount

    }

    # Define a variable that store the information about each identity

    $Results += New-Object psobject -Property $properties

    $ResultsExport = $Results | Select-Object "E-mail address", "Spam mail SENT"

}

# Define the variable for the exported Filename

$File1 = "SENT Spam emails per unique E-mail Address"

# Export information about - Sent SPAM mail Report

$ResultsExport | Export-CSV $A22\"$File1.CSV" -NoTypeInformation -Encoding utf8

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:

$GetMBXUser = Get-MailBox -Filter '(RecipientTypeDetails -eq "UserMailbox")' | Where-Object {$_.name -notlike '*DiscoverySearchMailbox*'} | Sort-Object -Property Displayname

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

# Define variables for a start date and End date

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

# All Exchange Online USER Mailboxes

$GetMBXUser = Get-MailBox -Filter '(RecipientTypeDetails -eq "UserMailbox")' | Where-Object { $_.name -notlike '*DiscoverySearchMailbox*' } | Sort-Object -Property Displayname

# Define variables that contain the folder names

$A30 = "$A10\C. Dedicated Spam Mail Report For Each E-mail Address"

$A31 = "$A30\1. Exchange Online users"

$A32 = "$A31\1.1 - RECEIVED Spam mail report"

# Create folders Structure that contains the exported information

# C:\INFO\Spam mail Reports

# C. Dedicated Spam Mail Report For Each E-mail Address

IF (!(Test-Path -path $A30))

{ New-Item $A30 -type directory | Out-Null }

# 1. Exchange Online users

IF (!(Test-Path -path $A31))

{ New-Item $A31 -type directory | Out-Null }

# 1.1 - RECEIVED Spam mail report

IF (!(Test-Path -path $A32))

{ New-Item $A32 -type directory | Out-Null }

# Loop via the list of existing Exchange Online mailbox users | locate information about the spam mail event for each E-mail address

ForEach ($Mailbox in $GetMBXUser)
{

    # Specific Exchange Online mailbox identity | Specific member from mailboxes Array

    $ID1 = $Mailbox.Displayname

    $ID2 = $Mailbox.PrimarySmtpAddress

    #  Define variable - Spam report for a specified Exchange Online user

    # Define a variable that store all spam mail event of E-mail that sent to the specified recipient

    $ReceiveEvents = Get-MailDetailSpamReport -RecipientAddress $ID2 -StartDate $StartDate -EndDate $EndDate

    # Count the number of - all spam mail event of E-mail that sent to the specified recipient

    $ReceiveEventsCount = @($ReceiveEvents).count

    # Verify if the specified Exchange Online user RECEIVE any spam mail

    IF (!$ReceiveEventsCount)
    {

    }

    Else
    {

        # Define variables that contain the folders name for the dedicated Exchange Online user spam report folder

        $A40 = "$A32\$ID1"

        # Dynamically create a dedicated folder that will store spam mail report

        # <User name>

        IF (!(Test-Path -path $A40))
        {

            New-Item $A40 -type directory | Out-Null

        }

        # Define the variable for the exported Filename

        $File1 = "Spam emails RECEIVED"

        # Export information about - Received Spam emails by specific Exchange Online recipient

        $ReceiveEvents | Export-CSV $A40\"$File1.CSV" -NoTypeInformation -Encoding utf8

    }

}

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

The current scenario, is similar to the one we review in the previous section.

The main variance from the previous 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

# Define variables for a start date and End date

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

# All Exchange Online USER Mailboxes

$GetMBXUser = Get-MailBox -Filter '(RecipientTypeDetails -eq "UserMailbox")' | Where-Object { $_.name -notlike '*DiscoverySearchMailbox*' } | Sort-Object -Property Displayname

# Define variables that contain the folders names

$A30 = "$A10\C. Dedicated Spam mail Report For Each E-mail Address"

$A31 = "$A30\1. Exchange Online users"

$A33 = "$A31\1.2 - SENT Spam mail report"

# Create folders Structure that contains the exported information

# C:\INFO\Spam mail Reports

# C. Dedicated Spam Mail Report For Each E-mail Address

IF (!(Test-Path -path $A30))

{ New-Item $A30 -type directory | Out-Null }

# 1. Exchange Online users

IF (!(Test-Path -path $A31))

{ New-Item $A31 -type directory | Out-Null }

# 1.2 - SENT Spam mail report

IF (!(Test-Path -path $A33))

{ New-Item $A33 -type directory | Out-Null }

# Loop via the list of existing Exchange Online mailbox users | locate information about spam mail event for each E-mail address

ForEach ($Mailbox in $GetMBXUser)
{

    # Specific Exchange Online mailbox identity | Specific member from mailboxes Array

    $ID1 = $Mailbox.Displayname

    $ID2 = $Mailbox.PrimarySmtpAddress

    #----------------------------------------------------------------------------------

    #  Define variable - Spam report for a specified Exchange Online user

    #----------------------------------------------------------------------------------

    # Display progress bar information on the PowerShell console

    Write-Progress -Activity "Export to Log Files - SPAM mail Report for $ID1 E-mail address - last 30 days"

    # Define a variable that store ALL spam mail events of emails that where SENT by specified recipient

    $SENTEvents = Get-MailDetailSpamReport -SenderAddress $ID2 -StartDate $StartDate -EndDate $EndDate

    # Count the number of - Spam emails that was sent by (Sent from) a specific E-mail Address

    $SENTEventsCount = @($SENTEvents).count

    # Verify if the specified Exchange Online user SENT any spam mail

    IF (!$SENTEventsCount)
    {

    }

    Else
    {

        # Define variables that contain the folders name for the dedicated Exchange Online user spam report folder

        $A40 = "$A33\$ID1"


        # Dynamically create a dedicated folder that will store spam mail report


        # <User name>

        IF (!(Test-Path -path $A40))
        {

            New-Item $A40 -type directory | Out-Null

        }

        # Define the variable for the exported Filename

        $File1 = "Spam emails SENT"

        # Export information about - SENT Spam emails by specific Exchange Online recipient

        $SENTEvents | Export-CSV $A40\"$File1.CSV" -NoTypeInformation -Encoding utf8

    }

}

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 previous scenarios in which we count the spam mail 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:

$ReceiveEvents = Get-MailDetailSpamReport -SenderAddress <E-mail address> -

$SENTEvents = Get-MailDetailSpamReport -SenderAddress Angelina@o365info.com -StartDate $StartDate -EndDate $EndDate

$SENTEventsCount = @($SENTEvents).count

IF ($SENTEventsCount -lt 10)
{

    write-host "The Number of Sent spam emails is: $SENTEventsCount"

    write-host “This is less than required number 🙁 “

}

Else
{

    write-host "The Number of Sent spam emails is: $SENTEventsCount"

    write-host “This is a dangerous sender !!!!”

}

This is what the PowerShell script looks like:

# Define variables for start date and End date

$EndDate = Get-Date

$StartDate = $EndDate.Adddays(-30)

$ThresHold = Read-Host "Type the Number of threshold "

$A20 = "C:\INFO\Spam mail Reports - $Datef"

$A21 = "$A20\C. E-mail addreses that Pass a certain spam mail threshold"

$A22 = "$A21\1.1 RECEIVED spam mail - ThresHold"

$A23 = "$A21\1.2 SENT spam mail - ThresHold"

#  Create folders Structure that contains the exported information to TXT, CSV and HTML files

#---------------------------------------------------------------------------------------------------

# C:\INFO\Spam mail Reports - <Date>

IF (!(Test-Path -path $A20))

{ New-Item $A20 -type directory | Out-Null }

# C. E-mail addresses that Pass a certain spam mail threshold

IF (!(Test-Path -path $A21))

{ New-Item $A21 -type directory | Out-Null }

# 1.1 RECEIVED spam mail - ThresHold

IF (!(Test-Path -path $A22))

{ New-Item $A22 -type directory | Out-Null }

# 1.2 SENT spam mail - ThresHold

IF (!(Test-Path -path $A23))

{ New-Item $A23 -type directory | Out-Null }

$SpamMailLogFileContent =

for ($c = 1; $c -lt 1001; $c++)
{

    if ((Get-MailDetailSpamReport -StartDate $global:StartDate -EndDate $global:EndDate -PageSize 5000 -Page $c).count -gt 0)
    {

        Get-MailDetailSpamReport -StartDate $global:StartDate -EndDate $global:EndDate -PageSize 5000 -Page $c

    }

    else

    { break; }

}

# Define variables that will store the list of Exchange Online users (mailbox users) + all E-mail addresses that appear in the spam mail log file

# Create a list of unique E-mail address that appear in the spam mail report | Spam Mail Sender (E-mail address that sent spam mail)

$ALLSpamMailSenders = $SpamMailLogFileContent | Group-Object -Property SenderAddress | Sort-Object name -descending | Select-Object name, count

# Define an array (hash) that will store information about spam mail events that fetched from the mail spam mail report

$Results = @()

# Loop via the list of All existing E-mail addresses | locate information about spam mail event for each E-mail address

ForEach ($SpamEvent in $ALLSpamMailSenders)
{

    # Specific Unique identity (E-mail address)

    $ID1 = $SpamEvent.name

    #  Define variable - Spam report for a specified Exchange Online user

    # Define a variable that store all spam mail event of E-mail that sent to the specified recipient

    $SENTEvents = $LogFileContent | Where-Object { $_.SenderAddress -eq "$ID1" }

    # Count the number of - Spam emails that was sent by (Sent from) a specific E-mail Address

    $SENTEventsCount = @($SENTEvents).count

    # Verify if the specific E-mail address passes the specified spam mail threshold

    IF ($SENTEventsCount -lt $ThresHold)
    {

    }

    Else
    {

        write-host "The number of spam emails that was sent by : " -nonewline; write-host $ID1 -ForegroundColor Yellow -BackgroundColor blue -nonewline; write-host

        write-host "is - " -ForegroundColor white -nonewline; write-host $SENTEventsCount -ForegroundColor white -BackgroundColor Darkred -nonewline; write-host " spam emails" -ForegroundColor white

        # Define that array of -properties, that appears in the spam mail report

        $Properties = @{

            "E-mail address" = $ID1

            "Spam mail Sent" = $SENTEventsCount

        }

        # Define a variable that store the information about each identity

        $Results += New-Object psobject -Property $properties

        $ResultsExport = $Results | Select-Object "E-mail address", "Spam mail Sent"

    }

    # Define the variable for the exported Filename

    $File1 = "Recipients that Send more then $ThresHold spam emails"

    # Export information to the following file formats:  TXT + CSV + HTML files

    $ResultsExport | Export-CSV $A23\"$File1.CSV" -NoTypeInformation -Encoding utf8

}

In the next article, we will look into How to use the spam mail report PowerShell script.

o365info Team

o365info Team

This article was written by our team of experienced IT architects, consultants, and engineers.