How to efficiency and easily read the information in the Exchange Online message trace historical search CSV file

Exchange Online, offer us a very in-depth and comprehensive information about each mail flow transaction, which occurs in our mail infrastructure. The information about each incoming and outgoing mail transaction which passes via the Exchange Online server is kept for 90 days.The Exchange Online web based management interface enables us to look at this “Log information” using a very user-friendly and effective interface named – message trace.

Extended Message Trace in Office 365 | Article Series

The current article series, including the following articles:

• Performing an Extended Message Trace in Office 365
• How to efficiency and easily read the information in the Exchange Online message trace historical search CSV file

Although the Exchange Online message trace is a convenient tool that enables us to view log information of the mail flow, this tool doesn’t include a built-in option of exporting the data for further analysis.

In addition, the information that we can view by using the message trace is a partial information because, the “original” Exchange Online log file, include many additional details, which are not revealed to us when we use the message trace tool.

The little secret that most of us don’t know is, that Exchange Online includes a powerful feature named – historical search that we can use for reviling this hidden information!

The two prominent advantages of the Exchange Online historical search features are:

1. The ability to export the data to a file

The Exchange Online historical search feature, enables us to export the mail flow log information to a CSV file. The CSV file is used for further analysis and documentation of the mail flow transaction. By using tools such as Excel, we can implement various actions, such as – filter the data, search for a specific value and so on.

2. Additional information about mail transaction that doesn’t appear when using message trace

When using the option of “Exchange Online historical search,” we can get many additional details about the mail transaction, that doesn’t appear in the standard Exchange Online message trace interface.

In a scenario of advanced mail troubleshooting, we need this “additional information,” and we need to be able to export the information to a file that enables us to analyze large amounts of information.

The two disadvantages of the Exchange Online historical search feature are:

1. The time it takes for data export

When we “activate” the option of historical search, Exchange Online adds the request to a pool of tasks.” At the current time, there is no accurate assessment of the period of time, which required to Exchange Online for exporting the data to a CSV file.
A rough estimation is between 2-8 hours.

2. The ability to clearly understand the information in the CSV file.

The information in the CSV file is a very detailed information, but for most of us, the first impression is a feeling of TMI (too much information).

The information in the CSV file is a “raw data.” data”. Without making use of a suitable tool, it is hard to understand or look for a specific information in this “pile of information.”

The challenge of analyzing exporting information using a CSV file

Our current article is dedicated to the subject of – Tips and tricks for effective and easy use of the CSV that is exported by the Exchange Online historical search.

If you didn’t experience the task of – analyzing a significant chunk of data, the first impression that you probably get from looking at the CSV file is the feeling of “mess,” and non-readable “gibberish.”

Another interesting thing regarding the “Exchange Online historical search” is that although we use the Exchange Online message trace web interface for “activating” the Exchange Online historical search; the message trace interface doesn’t include a special menu or a “hint” that will instruct us how to use the historical search.

The secret way that we use for activating the Exchange Online historical search is by defining a custom search scope that includes more than 7 days.

Only after we use the custom search scope the option of – historical search will be reviled.

Activating Exchange Online historical search using PowerShell

In the current article, we will review how to use the Exchange Online historical search by using the Exchange Online admin interface. It’s important to mention that an additional option for using the historical search is by using PowerShell.

The PowerShell command that we use is:

-Start-HistoricalSearch

How to activate the Exchange Online historical search | Exchange Online admin interface

In the current section, we will briefly review how to use the Exchange Online historical search by using the Exchange Online admin interface.

• Login to Exchange Online admin portal
• On the left menu bar select – mail flow
• On the top menu bar select – message trace

The default “Date range” for the search is 48 hours.
We will need to “extend” this range, to a time range larger than 7 days.
Even if the time range that you need is smaller than 7 days, you will need to “ask” from Exchange Online a larger time range, so the Exchange Online historical search will be activated.

• In the section named – *Date range choose – custom
• In the section named – *Start date and time: choose – time range bigger than 7 days

The Exchange Online message trace interface “enforce” us to use at least one additional filter, besides of the “time range filter.”

In our scenario, we want to get information about all the mail flow that our organization recipient sent (outgoing mail).

To be able to define a filter that relates to all the senders from a particular domain, we can use the following syntax – *@<Domain name>

In our scenario, the domain name is o365info.com

• Click on the add sender… button
• We will write the search query *@o365info.com in the text box – Check names

To activate the option of the detailed log of the historical search, we need to select the option – Include message events and routing details with report

The process of creating the Exchange Online historical search can take On average 2-8 hours.

In case that you want that Exchange Online will notify you when the CSV file that contains the log information is ready, you can add your E-mail address in the section
named – Notification email address:

When we hit the “search” button, the following message appears.

Your message trace has been submitted. An email message will be sent to you when it’s available. You can also check under the pending and completed traces to see the progress of the trace.

The message informs that our request for information (log file) was successfully registered.

Notice the difference between the historical search and the standard Exchange Online message trace. When we use the Exchange Online message trace, the results will immediately appear in an “HTML search result” window.
When we activate the Exchange Online historical search, the request is sent to a “pool of tasks” that will be executed by Exchange Online later.

When the CSV log file is ready, we can come back to the Exchange Online message trace management interface and select the link – View pending or completed traces

In the following screenshot, we can see that the log file is ready (complete).
Select the Download this report link to download the CSV file.

How to efficiency and easily read the information in the Exchange Online message trace historical search CSV file

The historical search information is exported using a CSV (comma separated value) file format.

The CSV file format created for saving data in a table style format which include column’s headers and data rows.

Theoretically, we can use simple text edition for edit CSV file, but in reality, it will be very difficult to understand the data.

The solution for reading and editing CSV file in an easy and friendly way is, by an application
such as – Microsoft Excel.

In the following screenshot, we can see the “look” of the CSV file when we use Excel as an editor. Excel “know” how to arrange the data in a table format, but the information is still looking like a big mess!

Displaying the information in a friendlier way using Format as Table | Exchange Online Historical search log

The Excel feature that we use for making the data more digestible is – Format as Table

All we need to do is click on one of the Excel cells that include data and then click on the icon
named – Format as Table

Excel offers us a variety of design options for the table styling.
Choose the option that you like.

Excel will try to recognize the “boundary” of the table automatically.

• Select the option – My table has headers
• Click on the OK button

In the following screenshot, we can see the results:

The Excel table wizard, color to row in a different shade of colors, so it is easier to differentiate between the data rows.

Also, Excel adds a “small arrow ” on the right side of each column header.
The “little arrow” enables us to use, filter and search options for each of the table columns.

How to sort the information | Exchange Online Historical search log

The Exchange Online historical search Log information includes many “columns” of data.

One of the most basic requirements is the option to sort the data by a particular column.

Example 1 – sorting the data by E-mail message date

The common assumption is that the exported data sorted by the E-mail message dates, but in reality, the data is exported “unsorted.”

To be able to sort the information about the mail flow transaction, by the time we sort the information in the column date_time

• Select the column date_time
• Click on the small arrow.
• Select the option – Sort A to Z

Example 2 – sorting the data by E-mail address

In this scenario, we want to track E-mail message that was sent by a particular sender.
To be able to display a clear view of all the E-mail messages that was sent by “sender,” on the column header – sender_address, click on the small arrow.

• Select the option – Sort A to Z

In the following screenshot, we can see results. The senders grouped by their names.

Date and Time format | Exchange Online Historical search log

A very important issue that I would like to review is, the subject of the Date\time information, that is used in the Log file that we get from the Exchange Online historical search process.

Let’s start with a simple example that we help us to understand better the “time format issue” in the Exchange Online based environment.

Office 365 user named Brad, send E-mail to another recipient.
The E-mail sends at 06:37 AM.

When we perform a simple Exchange Online message trace search, looking for information about the E-mail that Brad sent, we can see a strange phenomenon – the information about the E-mail says that the E-mail was sent at 03:37 AM

The simple explanation for this “time difference” is related to the time zone.

The information that Exchange Online writes to the LOG file is written by using GMT0 time.

In our scenario, Brad is located in different time zones – GMT+3

The solution to this “time issue,” is solved by adjusting the information that displayed for the specific user time zone.

In our scenario, if we add to the time that appears in the Exchange Online log (03:37) the “3 hours” of Brad’s time zone. The result is the time – 06:37.

06:37 is the time which Brad sends his E-mail.

In the following screenshot, we can see the date_time column.
At first look, the information looks like a random collection of number and characters.

To be able to understand the “time information” that appears, let’s take one “block” of information and reviews each of the different parts.

The “time blocks information,” includes the date of the mail flow transaction as it registered at the Exchange Online server. In our example, the E-mail transaction date is – 2016/06/06

After the date, we see the “T” letter stand for “time”

At the end of the date/time block, we can see the letter “Z.”
The letter “Z” stand for “Zulu time Zone” which defines a GMT0 time zone.

In other words, the time that appears in the log is based on GMT0

The meaning is, that we need to adjust the information to a particular time zone in which our recipient is located.

In case that the sender / recipient time zone is GMT+3, the “translation” to the “right time” will be: 18:14 + 3 = 21:14

Additional information that I would like to mention is regarding the – date and format.
In the following diagram, we can see the date and the time format.

• The date format is – “Year / Month / day”
• The time format is – “Hour / minute / second”

Non-English characters | Exchange Online Historical search log

In the following section, I would like to review a scenario in which the Exchange Online historical search log, include a “Non-English characters” character.

For example, information about the E-mail message subject that includes characters in languages such a Hebrew, Arabic, etc.

In the following screenshot, we can see an example to such a scenario in which the E-mail message includes “Non-English characters” characters.

The reason that Excel displays the information using the “strange characters” is because Excel doesn’t have the built-in ability to apply file formats such as UTF8 or Unicode.

The good news is that we can use a little trick, that will help us to overcome this “Excel limitation.”

The trick that we can use is implemented by open the Exchange Online historical search log using the windows built-in text editor – the Notepad.

Using the Notepad, we will save the CSV file a UTF8 file format, that will enable us to display the “special non-English” characters.

Convert log data to UTF8 format

Right-click on the Exchange Online historical search log CSV file and select the
option – Open with and then Notepad.

• Select the File menu
• Select – Save As…

• File name: write the file name or just add to the existing name additional description. Verify that the file name includes the suffix – .csv
• Save as type: select the option All Files (*.*)
• Encoding: select the UTF8 encoding
• Save the file

In the following screenshot, we can see that when we open the CSV file using Excel,
the issue with the “gibberish characters” was fixed.

Now we can see the mail subject information that includes the non-English characters.

Search / Filter a specific column | Exchange Online Historical search log

In the following section, we review how to search and filter information in a specific column.

This option is very useful in the case that we want to focus in a specific “chunk” of the data.

For example, we would like to display all the available information about a specific
sender – Brad.

To be able to filter the required information, we click on the small arrow in the sender_addrress column.

In the Text Filters text box, we will write the name of the sender that we look for.
Notice that Excel knows to automatically “display” only the values that are compatible with our search query.

In our example, we click on the required sender E-mail address – bradp@o365info.com

In the following screenshot, we can see the results:

The information that appears is a “filtered information” that displays only the records (the rows) that have the value – bradp@o365info.com in the sender_addrress column.

Notice that the row number color, is changed to blue. This color change informs us that we see a “filtered data” and not the “full original data.”

Another sign that appears is that the small arrow icon change into a filter icon. The “new filter icon” help us to understand that the data filtered by using the specific column header.

In case that we want to “revert back” and cancel the filter that was applied, we need to click on the filter icon and select the option – Clear Filter From “sender_address” or select the option – select all

The previous article in the current article series

Performing an Extended Message Trace in Office 365