kqlazure-data-explorerazure-log-analyticskusto-explorerazure-log-analytics-workspace

Azure Log Analytics :: List all databases with their size in GB


I was following this guide and I found a good piece of code that could help me find a list of SQL Databases along with their:

The code is this:

AzureMetrics
| where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
| where TimeGenerated > ago(1h)
| where MetricName == "storage"
| extend AverageMB = Average / 1000000
| summarize MaxMB = max(AverageMB) by bin(TimeGenerated, 1h), Resource
| project TimeGenerated, Resource, MaxMB

Unfortunately when I run it it shows multiple entries for the same database.

Why?


Solution

  • I have reproduced in my environment and below rea my expected results:

    Note:

    When ever you use summarize() on time, you will always get multiple entries, because it is grouping results on time also which is not recommended as you will get duplicate or multiple entries like below:

    Below is example KQL query :

    AzureMetrics
    | where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
    | extend AverageMB = Average / 1000000
    | summarize MaxMB = max(AverageMB) by bin(TimeGenerated, 1h), Resource
    | project TimeGenerated, Resource, MaxMB
    

    enter image description here

    here, there are multiple entries. To remove these use below query(integrate below query into yours query):

    AzureMetrics
    | where ResourceProvider == "MICROSOFT.SQL" and Resource != "MASTER"
    | project Resource,TimeGenerated,Average
    | extend AverageMB = Average / 1000000
    | summarize MaxMB = max(AverageMB) by  Resource 
    

    enter image description here

    So, I would suggest not to use summarize() on time as it does grouping on time. You will get results grouped on a particular time and size.