I’m trying to find out if I can access Azure Monitor Metrics directly from Power BI, rather than recreate data that appears to already be stored in Azure. I would like to access storage account data (specifically, the UsedCapacity value over time) for a storage group.
Additional Information
-------------------------------
Azure makes storage consumption data available through Azure Monitor Metrics. The Azure Monitor Metrics Explorer view for the UsedCapacity metric presents us with a graph of storage consumption over time out of the box. I would like to ingest this Azure Storage Metric data into Power BI so that I can report on the following at the storage account layer:
I’m looking to use Power BI as I’m very familiar with the product and it makes it very easy to share the results with our customers. I have already created the required Power BI reports using dummy data and now wish to automate the data ingestion from Azure Metrics.
The following KB is a good entry point into Azure Monitor Metrics:
What can you do with Azure Monitor Metrics? (https://docs.microsoft.com/en-us/azure/azure-monitor/essentials/data-platform-metrics#what-can-you-d...)
The KB presents options available for Metric retrieval, export and archival. I have looked at each of these options with the following conclusions:
Retrieval
------------
Retrieval looks like a viable option: I can querying Azure Storage Metrics for my storage account to return the UsedCapacity metric via PowerShell. The following does the trick:
$resourceId = “/subscriptions/11111111-1111-1111-1111-1111111111111/resourceGroups/rg-lab-storage-prod/providers/Microsoft.Storage/storageAccounts/labprodstorage”
($metric = Get-AzMetric -ResourceId $resourceId -Metricname “UsedCapacity” -TimeGrain 01:00:00).Data | select Timestamp,Average
Output is returned similar to the following for a single data point:
28/02/2022 13:48:00
93930362282
However, I cannot seem to get Get-AzMetric to return time series data for UsedCapacity (to allow me to access all available historical consumption data points already stored by Azure Monitor Metrics). I have raised this question with the documentation team as they reference a timeseries element but it appears to be empty. As a worst case scenario though I could configure an Azure function to run this PowerShell cmdlet say once a day and to append the data to a .CSV file held in Azure Blob storage. I can then ingest the data into Power BI to feed my reports. However, I’m wondering if there is a more direct way of accessing the time series data given that Azure Metrics Explorer shows a graph of storage account UsedCapacity over time for any storage account ‘out of the box’. Is it possible for me to access this time series of data directly from Power BI in some way?
Export
---------
I thought that I was on to something when I found the following KB: https://docs.microsoft.com/en-us/azure/azure-monitor/essentials/diagnostic-settings?tabs=CMD. This suggests that it is possible to redirect Azure Metrics for permanent storage. You can redirect Azure Metrics data to the following targets:
- Blob storage
- A Log Analytics workspace
- Event Hub
- Partner solution
I could then pull the data from Power BI and there would be no need to sample data via PowerShell on a schedule. However, if I go into my storage account and select Diagnostic setting, only the following option is available in this context:
It looks like redirection of UsedCapacity metrics for storage accounts is not possible.
Archive
-----------
The third option that Microsoft mentions in “What can you do with Azure Monitor Metrics?” is archival. However, this does not appear to relate to Azure Storage Metrics for UsedCapacity.
I thought I hit the jackpot when I came across the following article that describes how to “Visualize Azure Resource Metrics Data in Power BI”:
https://www.mssqltips.com/sqlservertip/6623/azure-reporting-for-resource-usage-with-power-bi/
The high-level steps in this article are:
- [Create a Log Analytics workspace to receive the Azure Resource Metrics that we want to subscribe to]
- Navigate to the Log Analytics workspace where the Azure Monitor Logs are collected
- Select a Resource Type to query its data
- Export the Power BI (M Query) for the metric
- Create the visual in Power BI Desktop
- Publish to Power BI service and configure the Data source Credentials.
Unfortunately, this method leverages data held in an Azure Log Analytics workspace, and as we’ve seen above under the ‘Export’ and ‘Archive’ options above, these options do not pertain to the Azure Storage UsedCapacity Metric for some reason.
I know that Microsoft Grafana includes built-in support for connectivity to Azure Metric Explorer graphs, but as I understand it, Grafana is aimed at NOC-like scenarios where you wish to develop rich dashboards for wall displays.
Am I missing something fundamental here? Is it possible to access Azure Metric data directly from Power BI, or to redirect data to a storage account or other repository for easy ingestion? If not, I can go with my backup plan of an Azure Function running PowerShell on a schedule to sample data over time, which feels like it should be unnecessary.
Thanks in advance!
