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:

• Used space
• Remaining space
• Allocated space
• Max storage

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?

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

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 

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.