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

Comments

• Anonymous
  June 19, 2017
  If we you the visual studio for exchange2010 with the powerBI.
  • Anonymous
    June 19, 2017
    Hi Hassan, you can't need Visual Studio to install PowerBI for Desktop, the product has his own independant installer...Thanks,Sam.
• Anonymous
  June 19, 2017
  cool stuff thanks
• Anonymous
  January 22, 2018
  Hi,the Template is no longer available?!
  • Anonymous
    February 15, 2018
    Hi Peter, just saw your message - working on it now!
• Anonymous
  February 05, 2018
  Love this article. Only just starting to look at Power BI but after spending quite a bit of time writing VBA macros to manipulate data, for just one view of tracking logs information I thought there must be a better way. Thank you so much Sammy and Bernie for sharing this information. It has helped me produce some information much quicker than I would have done without it. I hope to progess my Power BI knowledge further.