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

Hi all ! My posts are pretty rare these days, some projects are still on the pipe but the pipe froze last winter…

Anyways I wanted to share with you guys the principle and methodology to use our excellent PowerBI to analyze and visualize for you the Exchange Message Tracking Logs. This has been brought up and created by our very Canadian Bernard Chouinard, former Microsoftee, now independent contractor, and who is our excellent Architect of messaging systems for organizations with more than 150,000 mailboxes, multiple datacenters, and taking advantages of all the bells and whistles that Exchange has to offer.

Note: the systems that Bernie architected never encountered any performance or technical issues other than flooding or datacenter explosions (not due to the Exchange design or planning issues , and yes, one of the datacenters exploded), and users never had service interruption thanks to Bernie’s great High Availability planning, design and deployment of his solution: the Disaster Recovery site seamlessly took over the service.

In this instructional video, Bernie first demonstrate the dashboards he created, how he created the filters to get the relevant data, and then he shows you how to load the message tracking log data, showing each step to load and fix data so that PowerBI fully interpret them (remove extra lines that are by default on Exchange tracking logs for example – that has to be done only once and no need to do this anymore after, fix the date/time columns, make PowerBI to treat multi-recipient message as multiple messages to have accurate data).

1- before loading the message tracking logs:

2- After loading the tracking logs:

• > This dashboard happens to be very useful for Exchange Architects when we have to define the Users Profiles when designing a new Exchange architecture: we can easily get from there the users sent/receives messages per day, on one day or on multiple days by just selecting boxes, and also the average message size that shows up on one of the graphs.
• > We can also get message usage patterns and trends, and then troubleshoot, adjust or evolve the infrastructure accordingly if needed.

If you don’t feel like following all the steps from the video, Bernie gently provides us the PowerBI template from which you can just change the path where your Exchange 2010+ Message tracking logs are located, and just see the magic operate. Below is how to change the template’s path on the PowerBI Exchange Message Tracking Log template: go to “Edit Queries” menu, and change the “Var_LogFolder” Folder path to the one containing your Message Tracking Logs.

The template is located here.

And a small bonus from our friend Bernie, to get Exchange tracking logs using Powershell : GetTrackinglog...

Thanks very much to Bernard Chouinard (Canada) for this awesome effort and sharing it with the community, and I wanted to highlight how humble he is about all the awesome work he does (Bernie is what we call a “Silent Hero”).