Thursday, December 24, 2015

Exposing Azure Storage Account Monitoring/Logs Via PowerBI

Monitoring Azure services is a complex task that is constantly evolving.  Not only are features changing (Azure Security Center, Operational Insights, Application Insights), the exact view they provide into a particular application can be spotty at best.  Obviously this is an area that Microsoft is working heavily on, and recent announcements regarding OMS are strides in this direction.  The goal, of course, is to provide a central, unified view into an application and the various components that support it.

One service that is core to almost everything provisioned in Azure is storage.  The storage team has worked on providing several metrics/monitoring/logging tools to help customers.  That is a good step one: access to the data.  Visualizing this data, however, leaves some room for improvement.


1) You can use the old portal
The old portal provides a monitoring page that you can access.  You can add metrics to it, and view those metrics over a pre-defined period.  You can learn more about this toolset here.

2) You can use the new portal
The new portal is constantly under development.  Right now, you can select your storage blades and add a tile from a pre-set list of visualizations.  This is pretty cool feature, and combining this with the "dashboard" feature of the new portal will lead to some interesting monitoring capabilities.

The ultimately problem with the above two solutions is they are currently Azure specific.  In this day and age, it is rare to have an entire solution living in one single environment.  The visualizations provided are also determined by Microsoft.

In comes PowerBI

PowerBI is almost like the swiss-army knife of reporting.  It combines some really cool visualizations, a versatile query language, and the ability to add multiple data sources to a single report/dashboard.  This really allows us to start building application wide monitoring dashboards.

From an Azure storage perspective, the team has done a great job by making the monitoring/logging data accessible to everyone.  You can read more about this here.  In short, monitoring metrics are stored in non-visible tables in Azure Table Storage located on the storage account that you enabled monitoring.  As such, you can use this as a data source into powerbi.

Unfortunately, at time of writing, PowerBI does not support Azure Tables as a data source directly from the web interface.  So, for this you will have to use PowerBI desktop.

You can read this post to learn how to add Azure Table Storage as a data source in your PowerBI dashboards.  The one caveat here is that the tables for monitoring will not be returned from any list command (which is what PowerBI uses to get the list of tables).  As such, you need to use the following code snip-it to access your monitoring tables:

    Source = AzureStorage.Tables("<<storage account name>>"),
    HourlyMetricsTable = Source{[Name="$MetricsHourPrimaryTransactionsBlob"]}[Data],

If you put that at the start of your query (see this link for information on how to get to the advanced query editor), you will be able to access the tables and perform any post tasks you require.

You can find a list of the monitoring tables and schema information here.

One you have established the access, you can create whatever your heart desires (or your client for that matter).  Here is an example I hacked up.

I personally plan to use this type of reporting for performance baseline/monitoring purposes.  Have fun!