Import perfmon data to PowerBI with relog
Updated: Jan 23, 2021
Recently for a client, there was a need to start capturing some basic server performance stats and combine them with user report performance data from Reporting Services and BI Office performance logs. There was no lacking of monitoring options, but none that covered the 3 things that we wanted to monitor which was
CPU utilization of the Analysis Server(s)
Report Performance as logged in Reporting Services Databases
Query Performance as logged by BI Office from Pyramid.
Clearly, we could manually compare results from existing monitoring of CPU against SQL queries executed against the SQL DB's that Reporting Services and BI Office use, but that is a manual task and the actual grain of data that we could get for CPU usage was relatively large (5 minutes chucks). Clearly, PowerBI would be suitable to combine data and provide the consolidated view that we wanted, once we overcome how to automate the extraction of data from Performance Monitor logs.
Performance Monitor logs the captured data into a binary file, which you can open via perfmon. PowerBI can't connect directly to Perfmon files, but you can extract the contents of Perfmon into a text file by using relog.exe which is a standard part of the windows OS.
An example of how to use it is;
relog.exe "Source Perfmon File.blg" -o "MyPerfMonData.csv" -f CSV
-o identifies the output path for the counters
-f identifies the file type of the output
More settings are available which can be found here.
Once you have your data in a text file, the rest is straight forward. In my solution, the following was done;
Setup Perfmon traces that auto-restart after a server reboot
Schedule a twice-daily extract of perfmon binary data to a text file via a windows scheduled task. (NB - I call a dos batch file from my scheduled task. The batch file contains my relog command)
Create a PowerBI report that connects to relog outputted text files and reporting services/bi office logs
Publish my PowerBI reports and schedule them to refresh.
Now I can monitor my combined CPU and Query performance data from a single dashboard like the following one.
Simple, once I discovered relog.
Stay tuned for a complementary power bi file that allows you to monitor your on-premise reporting services instances.