Category Archives: PowerBI

Monitoring Azure VMs with Power BI

I had been into the situation where we were running some performance test on the Azure VM and need to monitor their CPU’s

Azure portal (Feb 2017) provides nice metric blade to monitor most of the resources in Azure like Web Apps, Storage Account, SQL Azure, etc. I need to monitor IaaS VM’s.

I was running Linux VM, and there is nice blog on the Azure docs, which show’s how to enable Linux diagnostic on the VM and collect the metric

This works for me, but had two issues. First, Azure metric blade just gives me three filter for time duration. It like, past hour, entire day and pass week. There wasn’t any provision for custom duration say, CPU for yesterday between 1 pm to 3 pm.

AzureDurationLimitation.png

Second, I had create a custom dashboard on the Azure Portal for monitoring all my VM’s CPU. But, I can’t share this with non-Azure users. Our performance testers, Managers, etc. didn’t have the Azure subscriptions or any Azure knowledge. To create read-only users with particular dashboard resource group access and getting them the learning curve to view CPU was big ask.

I knew this monitoring reading from the Linux diagnostic accounts are save in Azure Storage Tables,

AzureStorageTables.png

Linux diagnostic tables in Azure storage as seen in Visual Studio 2015 Cloud explorer

and I can use PowerBI to connect Azure Storage Tables. Power BI report can be publish to Web and can be viewed without need of Azure Subscription account. So, most of my needs were answered.

I create a PowerBI report where I connect to Azure tables. Here, before you click load, you need to filter out the records otherwise, it would download the entire table data to PowerBI which would be in GB’s

I edited to add filter for past week data and then load filter data in Power BI data model (Yes, Power BI stores it own data). Once data is loaded in the Power BI data model, I need to add few new calculated columns in the data model, which I will use this calculated columns to define my new Time Hierarchy.

By default, power BI provides Time hierarchy in Year, Quarter, Month and Date. But, for the data I want Month, Date, Hours and minute

I create my new time hierarchy and create the report out of it. I had create basic report using Column chart which show max CPU by minute, hour, day and month. i.e. Even for 5 min in a hour, if the CPU hit 100%, it would show in cumulative hour as 100%. Same logic will role up to day and month. It helps us in devops to get things in large perceptive of the VM usage.

I am aware, there are better tools in the market like Azure OMS, and from third parties like DataDog and SysDig. But, this is like DIY project rather than using tools.

Word of caution, when using PowerBI with Azure Table Storage, every time PowerBI hits Azure storage table to fetch data, there will be outguess charges on Azure Storage. You can use something call as PowerBI embedded and host Power BI report in same region where your storage account is to avoid these charges.

The whole action I have capture in this youtube video, which

  1. Connect to Azure Table
  2. Filter the Data from Azure Table
  3. Added columns in Data Model for new Time Hierarchy
  4. Create report with new data model
  5. Exploring drill down in power BI

If you have suggestion or comment, do let me know.