Visualize Exchange Message Tracking Logs with PowerBI

The Exchange Message Tracking Logs are not only helpful to analyze MailFlow problems, but are also great for generating statistics. My script "Exchange Reporter" uses the message tracking logs to generate some statistics on received/sent mails. Likewise, the Message Tracking GUI on the message tracking logs. However, the Exchange Reporter only generates very static statistics and admittedly does not work very efficiently. In small environments, it still works quite well to search the message tracking logs with PowerShell, but as soon as the environment gets a little larger, it no longer works very well.

Rather by chance, I came across a very elegant option in Technet. Bernhard Chouinard has published a template for PowerBI that makes it possible to visualize tracking logs from large environments quickly and efficiently:

• Exchange 2010 and later - use PowerBI to visualize Message Tracking log stats and use as an User Profile Analyzer

Microsoft PowerBI can be downloaded and used free of charge and is designed to process large amounts of data. The trick is to first copy the message tracking logs from the Exchange servers to the local computer and then process and display them with PowerBI.

You can download everything you need here:

• Microsoft PowerBI Desktop
• Ex_Trackinglog (PowerBI template) (Download: see update at the end of the article)

As a test, I only copied the tracking logs of a single Exchange server to my computer. To do this, I created a folder with the server name (in this case the server is simply called "Exchange") in the folder F:\TrackingLogs and copied the Exchange message tracking logs into the directory:

If there are several Exchange servers, simply create several directories with the corresponding server names (e.g. F:\TrackingLogs\Exchange1; F:\Trackingslogs\Exchange2; etc). The path of the Exchange Server Message Tracking Logs can be easily found via PowerShell:

Get-TransportService | ft name,MessageTrackingLogEnabled,MessageTrackingLogPath

As soon as the TrackingLogs have been copied to the local computer with PoiwerBI installed, the template from Bernhard Chouinard can be loaded (Ex_Trackinglog.pbix). No data is displayed yet, as the data is expected in a different directory. However, only the path to the message tracking logs needs to be specified by clicking on "Edit queries":

The path can then be adjusted under the variable "Var_LogFolder":

In my case, I only need to specify the path to the parent folder. The PowerBi template then analyzes all subordinate directories:

Once the path has been changed, the changes can be confirmed:

Loading the data from the message tracking logs works very quickly, light years faster compared to the CMDlet Get-MessageTrackingLog:

As soon as the data has been loaded, the time period can be selected via the "Date" field and the graphs are updated directly. Various statistics can now be called up here and the template is divided into 4 pages with different statistics:

Bernhard has also published a YouTube video in which he describes how the template can be used:

https://www.youtube.com/watch?v=PnJ61q_sB_w

As the tracking logs provide even more information, further data can also be visualized. It's worth trying out a little here!

Update 22.01.2018:

The PowerBI template Ex_Trackinglog is no longer available on the download page. I have therefore posted the download here:

If the download is not desired by the author, I will of course remove it immediately. A short e-mail is sufficient.