How to query Azure SQL Metrics using Powershell

by | Oct 8, 2020 | Azure, Technology | 0 comments

This article is contributed. See the original author and article here.

One way you can quickly search and query metrics data is using Azure Portal, where you have chart data. But maybe you want to get the raw data and query it yourself. Find below a Powershell sample to get this data


 


Find below a sample I build based on one I got from https://docs.microsoft.com/en-us/azure/azure-sql/database/scripts/monitor-and-scale-database-powershell


 


And you can get the other possible metric names to send it in as parameter in this other document: https://docs.microsoft.com/en-us/azure/azure-monitor/platform/metrics-supported#microsoftsqlserversdatabases


 


Find full sample at https://github.com/FonsecaSergio/ScriptCollection/blob/master/Powershell/AzureSQL%20-%20Read%20Azure%20SQL%20Metrics.ps1


 


But the main idea is using Get-AzMetric powershell command. You will get results as table, that you can save it in the format you want or save it to a database.


 


 

$MonitorParameters = @{
ResourceId = "/subscriptions/$($SubscriptionID)/resourceGroups/$($ResourceGroup)/providers/Microsoft.Sql/servers/$($ServerName)/databases/$($DBName)"
TimeGrain = $TimeGrain
MetricNames = $MetricName
StartTime = (Get-Date).AddDays($DaysToLook)
}
$Metrics = Get-AzMetric @MonitorParameters -DetailedOutput

 


 

TimeStamp           Average Metric                 
---------           ------- ------                 
07/10/2020 11:07:00       0 dtu_consumption_percent
07/10/2020 11:07:00      10 dtu_limit              
07/10/2020 11:07:00       0 dtu_used               
07/10/2020 11:12:00       0 dtu_consumption_percent
07/10/2020 11:12:00      10 dtu_limit              
07/10/2020 11:12:00       0 dtu_used               
07/10/2020 11:17:00    19,6 dtu_consumption_percent
07/10/2020 11:17:00      10 dtu_limit              
07/10/2020 11:17:00    1,96 dtu_used               
07/10/2020 11:22:00   34,85 dtu_consumption_percent
07/10/2020 11:22:00      10 dtu_limit              
07/10/2020 11:22:00   3,485 dtu_used               
07/10/2020 11:27:00    30,1 dtu_consumption_percent
07/10/2020 11:27:00      10 dtu_limit              
07/10/2020 11:27:00    3,01 dtu_used               
07/10/2020 11:32:00    27,7 dtu_consumption_percent
07/10/2020 11:32:00      10 dtu_limit              
07/10/2020 11:32:00    2,77 dtu_used               
07/10/2020 11:37:00       0 dtu_consumption_percent
07/10/2020 11:37:00      10 dtu_limit              
07/10/2020 11:37:00       0 dtu_used               
07/10/2020 11:42:00       0 dtu_consumption_percent
07/10/2020 11:42:00      10 dtu_limit              
07/10/2020 11:42:00       0 dtu_used

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment