How to use the spam mail report PowerShell script | Part 3#3 5/5 (3) 15 min read

In the current article, we review the PowerShell menu script that I created for exporting and viewing information stored in Exchange Online spam mail log file.

The purpose of the PowerShell script

The current PowerShell script is based on the PowerShell cmdlet –  Get-MailDetailSpamReport

The PowerShell cmdlet – Get-MailDetailSpamReport, (available only in an Exchange Online environment), enables us to view (display on the PowerShell console), and export to file information about “spam mail events.”

The main purpose of the current PowerShell script is, to simplify and optimize the use of this useful PowerShell cmdlet.
The PowerShell script menu options, enable you to export various types of “spam mail reports” using various file type formats (TXT, CSV, and HTML), that will help you to get a deeper understanding of spam mail events in your Office 365 (Exchange Online) environment.

For example:

  • Internal spam – identify and deal with, events in which the spam mail is “generated” by a legitimate organization user\s.
  • Spoof events – identify and deal with, events in which hostile element tries to spoof the identity of a legitimate organization user (send spam mail using his identity).
  • Spam mail attacks – identify and deal with, events in which hostile element targets a specific recipient or recipients by flooding them with spam mail.

The logic and the concepts of the Office 365 spam mail reports PowerShell script

The following PowerShell script is configured to automatically export many different spam mail reports, based on a predefined “search queries” and filters (that will be explained later on).

The purpose of the different type of spam mail reports is, to provide the ability to get insight and draw conclusions, about “spam mail events” that occur in our organization environment.

The concept of “E-mail addresses entities”

The spam mail report relates to the identity of the “entity” that sends spam mail or receives the spam mail, by specifying the E-mail address of this entity.

The “E-mail address” can be classified as the E-mail address of organization recipient, meaning E-mail address that includes our organization domain name suffix or E-mail address of non-organization recipients, meaning any E-mail address that includes a domain name suffix that doesn’t include our organization domain name.

When we use the term “organization E-mail address,” the meaning can be translated into many types of Exchange Online recipients such as – Distribution Group, contact, Public Folder, Shared mailbox, Room mailbox, User mailbox and so on.

Most of the times, our main concern is to analyze information that relates to our organization recipients (E-mail addresses that their suffix includes our organization domain name).

Although that we want to get information about each spam mail event that involved E-mail address of our organization recipients, in many cases, we want to use additional “classification,” in which our main target is to review and investigate spam mail events, that are related to specific types of Exchange Online recipient – Exchange user (the technical term is User Mailbox).

To enable you to get more “closer views” about spam mail events that relate to – Exchange Online users, each type of the spam mail report (summary spam mail report and spam mail events reports), include a dedicated section that “reveal” spam mail events that relate only to E-mail address that “belong” to Exchange Online users.

The PowerShell script will “fetch” from Exchange Online a list of Exchange Online Users and their E-mail addresses, and in the next step, “scan” the Exchange Online spam mail log file, looking for any type of spam mail events that include the “E-mail address” that appears on this list.

Organization E-mail address of non-existing recipients

Many times, when we look at the results of the spam mail reports, we will find information about E-mail address that includes “our domain name suffix” which allegedly “look like” organization E-mail address but in reality, there are no such E-mail addresses!

Most of the times, this phenomenon is realized when hostile elements try to “guess” E-mail addresses of our organization recipients, and try to send mail to “non-existing” recipients (non- existing E-mail addresses).

E-mail addresses entities1

Summary spam mail report Versus spam mail events reports  

The current PowerShell script will generate two types of spam mail reports:

“Summary spam mail report,” and “spam mail events reports.”

These terms are not formal terms, but instead, terms that I use for describing the different type of spam mail reports that are automatically generated by the PowerShell script.

Summary spam mail report

Summary spam mail reports are created by using a manipulation of the data that appears in the “original” Exchange Online spam mail log file. The Summary spam mail report is created by using a formula that counts the spam mail events, that relate to a specific E-mail address in a specific time period (30 days is the default time period used in the current PowerShell script).

The purpose of the “Summary spam mail report” is, to provide a quick insight about the “volume” of spam mail events that are related to a specific E-mail address.

For example, in case that we find in the report that there are many spam mail events that relate to emails that were sent from E-mail address of one of our organization users, this could be a sign to a scenario, in which the user account was comprised, and it used by hostile elements for distributing spam mail using the legitimate identity of the organization recipient.

Spam mail events reports

The report which I described as – “spam mail events reports,” are reports that include a detailed description of each specific “spam mail event.”

For example, a “spam mail event” will include information such as – the E-mail address of the sender, the E-mail address of the recipient, the specific date, the E-mail message subject, the direction (inbound or outbound) and so on.

 Summary spam mail report Versus spam mail events reports

Generate spam mail report automatically using menu 2

The current PowerShell script includes two types of menus:

  • Fully automated menu (menu 2).
  • Generate spam mail report by using as a specific search query

Menu number 2 was created for implementing automation of the following tasks:

  1. Fetch the content of the Exchange Online spam mail log
  2. Automatically generate various types of spam mail reports. Each of these reports will help you to get a different “angle” of the information in the spam mail log.

Spam mail report PowerShell script menu11

When you “hit” the menu option number “2,” the PowerShell script will start an automatic process that includes the following sequence of steps:

1.  Create folder hierarchy

The PowerShell script starts by creating a custom folder hierarchy, that will be used to store the various spam mail reports.

For example, the folder that stores the primary spam mail report will be created in C: drive, using the following folder path: C:\INFO\Spam mail Reports – <Date>

2.  Fetch information from Exchange Online spam mail log file

Next, the PowerShell script connects Exchange Online and fetches all the information that is stored in Exchange Online spam mail log file for a period of 30 days.

The information that was fetched, will be saved in the “Primary spam mail report.”

We can define this spam mail report as-  a “row” data because, the information appears as it is, without any customization or search query that “filter” specific type of data.

3.  Create the various types of spam mail reports.

The “foundation stone” for the various spam mail reports is – the “main spam mail report.”
This primary spam mail report, include all the available information about – every spam event that appears in the Exchange Online spam mail Log file, for the last 30 days period.

The PowerShell script uses the information that is stored in the “Primary spam mail report file” for – creating additional custom spam mail reports. We can relate to the additional spam mail reports, as a derivative of the primary spam mail report.

Spam mail reports structure and hierarchy -01

Spam mail reports structure and folder hierarchy.

The Spam mail reports folder hierarchy is built using a structure that is similar to the concept of Matryoshka doll.

Spam mail reports structure and hierarchy -02

First-level folder hierarchy

In the following diagram, we can see the “first level” of the folder hierarchy that will be automatically created by the spam mail PowerShell script:

Spam mail reports structure and hierarchy -03

A. ALL Spam mail events – Last 30 days

This folder will store the “Primary spam mail report file.”

B. Summary Spam Report – Count (Sum) RECEIVED & SENT spam emails

This folder will store the spam mail reports, which I describe as “Summary Spam Report.”
This term is not a formal technical term, but instead, a term that is used for describing a custom type of spam mail reports that count (sum) the spam events that related to a specific E-mail address.

C. Dedicated Spam Mail Report For Each e-mailed address.

This folder will store the spam mail reports, which I describe as “Dedicated Spam Report.”
The PowerShell script will automatically create a dedicated folder, for each E-mail address that appears in the spam mail report.

Second level folder hierarchy

Each of the “first-level level folders,” include an additional subfolder (Second level folder) that used for storing the spam mail reports, that are based on a specific classification (filtered view) of the “row” spam mail data.

To demonstrate the Second-level folder hierarchy, let’s review the hierarchy of “first level” folder named – “B. Summary Spam Report – Count RECEIVED & SENT spam emails

Inside this folder, we will find the “Second level folder hierarchy” that includes the following two folders:

1.  Exchange Online users

This folder includes a spam mail report that relates only to specific types of E-mail addresses: The E-mail addresses of Exchange Online recipients who describe as “Mailbox user.”
The purpose of this classification is, to enable us to focus on “spam mail events” that relate to our organization users (technically speaking, Exchange Online includes many types of recipients besides the “user recipient”).

2.  All E-mail Addresses

This folder includes a spam mail report, that relates to – ALL the E-mail addresses that appear in the spam mail report.
In this case, the report will not relate to a specific type of E-mail addresses, but instead, relate to each of the E-mail addresses that appear in the spam log (organization recipients E-mail addresses and non-organization E-mail addresses).

Spam mail reports structure and hierarchy -04

Third-level folder hierarchy

This is the “last folder hierarchy.” For example, the “1. Exchange Online users” folder, will include additional two folders:

1.1 Count RECEIVED spam mail

This folder includes a spam mail report that filters the information from the primary spam mail report, by using the following two criteria:

  • Spam mail events that related to the E-mail address of Exchange Online users.
  • Spam mail events which described as – RECEIVED spam mail. The meaning is – spam mail that was sent to (received by) Exchange Online user.

1.2 Count SENT spam mail

This folder includes a spam mail report that filters the information from the primary spam mail report, by using the following two criteria:

  • Spam mail events that related to the E-mail address of Exchange Online users.
  • Spam mail events which described as – SENT spam mail. The meaning is – spam mail that was sent from (sent by) E-mail address of the Exchange Online user.

 Spam mail reports structure and hierarchy -05

Spam mail reports type.

The current PowerShell script is configured to export the various spam mail reports to three different file formats: text file, SCV (Comma Separated Values) file, and HTML file.

Spam mail reports – CSV file format -01

Each file format has his strengths and weaknesses.
For example, the HTML spam mail report is very “friendlily” and easy to read. The “weakness” of the HTML file format is that there is no option for “manipulating” or editing the data.

Regarding the CSV file format, most of the times we use an application such as Microsoft Excel to view and edit the content of the CSV file.

This is an example of the CSV spam mail summary report that was created.

Spam mail reports – CSV file format -02

Quick tip – to optimize the way we view the “data,” we can use the option of – Table view.

  • Select one of the cells that include data (number 1)
  • Select the menu Insert and the submenu – Table (number 2)

Spam mail reports – CSV file format -03

In the following screenshot, we can see that Excel format that data in a “Table style.”

In addition, Excel adds to each column header, a “small arrow,” that enables us to manipulate the data. For example, filter specific values, sort the information and so on.

Spam mail reports – CSV file format -04

The Excel “issue” with long path and file names

In some scenario, when you try to open a CSV file, the following error appears:

Sorry we couldn’t find XYZ file. It is possible it was moved, renamed or deleted? “.
This error is related to Excel “issue” that relates to file that stored in a deep folder hierarchy or have a very long file name.

The solution, in this case, will be to shorten the CSV file name or move the file to “top folder”

Brief review – the two types of spam mail reports

As mentioned, the spam mail reports PowerShell script, will generate two types of reports:

The summary spam mail reports

In the following screenshot, we can see an example of summary spam mail reports.
The information table includes only two columns:

The E-mail address that appears in the spam mail log file + sum of the spam events that are “related” to the specific E-mail address.

In the current example, the report includes information only about the Exchange Online user recipient who received spam mail.

Using this “received spam mail summary report”, could help us to identify a scenario in which hostile element try to “flood” specific Exchange Online user\user, identify who are the organization user who suffers most from spam mail attack and so on.

Exchange Online users - Count RECEIVED spam mail - HTML

The “Dedicated Spam Mail Report”

I use the term “Dedicated Spam Mail Report” to highlight the special way that the current PowerShell script use for generating spam mail reports.

The spam mail reports PowerShell script, is configured to create a list of Exchange Online User E-mail address.
in the next step, the PowerShell script will scan the “primary spam mail report,” looking for information about each of the E-mail addresses in the list (spam events that are related to each of these E-mail addresses).

Given that there were spam mail events that relate to the specific E-mail address, the PowerShell script will automatically create a dedicated folder for each Exchange Online user, that will be named by using the Exchange Online user display name.

In the following screenshot, we can see an example of the results from the “Dedicated Spam Mail Report.” A Dedicated folder will be created for each Exchange Online user who (in this scenario) received spam emails.

Dedicated Spam mail Report -01

The “Dedicated user folder,” will contain the three-spam mail reports using the following file formats – Text, CSV, and HTML.

Dedicated Spam mail Report -02
In the following screenshot, we can see an example of a “Dedicated spam mail report” that includes all the spam mail events that are related to Exchange user named – Angelina.

Dedicated Spam mail Report -03

2.2 – PowerShell script menus | Description

PowerShell script includes the following sections:

Connect to Exchange Online using Remote PowerShell

General information – to be able to manage Exchange Online infrastructure, you must first create a Remote PowerShell session.

Menu 1 – Login to Exchange Online using Remote PowerShell

To create the required Remote PowerShell session, select the menu “1”.
A user credential’s window will appear.
You will need to provide your Office 365 Global Administrator credentials.

SECTION A: Export Various type of spam mail reports

Menu 2 – Export Spam Mail Reports | Last 30 days

This is the main menu option.
To be able to generate all the various spam mail reports that were described in the former sections, all you need to do is just hit the number “2”, and the PowerShell script will do all to work for you.

The spam mail reports will be stored by using the following folder hierarchy:

  1. ALL Spam mail events – Last 30 days
  2. Summary Spam Report – Count RECEIVED & SENT spam emails
  3. Exchange Online users

1.1 Count RECEIVED spam mail

1.2 Count SENT spam mail

  1. All E-mail Addresses

2.1 Count RECEIVED spam mail – ALL E-mail Address

2.2 Count SENT spam mail – ALL E-mail Address

  1. Dedicated Spam Mail Report For Each E-mail Address
  2. Exchange Online users

1.1 – RECEIVED Spam mail report

1.2 – SENT Spam mail report

  1. All E-mail Addresses

2.1 – RECEIVED Spam mail report

2.2 – SENT Spam mail report

 

SECTION B: Display + Export about spam mail senders or receivers that passes a certain threshold

Menu 3 – Display + Export Spam Mail Reports | E-mail addresses that pass a certain spam mail threshold

The purpose of the following menu is, enable you to locate “E-mail addresses” that involved in spam mail activity that passes a certain threshold.
Based on the specified threshold that you provide, the PowerShell script will scan all the events that appear in the Exchange Online spam mail (using date range of a 30-day period) looking for the following E-mail addresses:

  • E-mail address that RECEIVED number of spam emails, that are equal or higher from the specified threshold value.
  • E-mail address that SENT number of spam emails, that are equal or higher from the specified threshold value.

SECTION C: Spam mail report | Top 10 spam mail Receiver and Senders

Menu 4 – Export Spam Mail Reports | Dedicated spam events report for Top 10 spam mail Receiver and Senders

The purpose of the following menu is, to automatically create a dedicated spam mail events report, for each E-mail address in the spam mail log file, that answers the following criteria:

  • The Top 10 spam mail SENDERS.
  • The Top 10 spam mail RECEIVER.


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.

 

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


One Response to “How to use the spam mail report PowerShell script | Part 3#3”

  1. Hi,

    Great articles but unfortunately in all download link isn’t working.

    Br
    Konrad

Leave a Reply

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