CODEWITHSUNDEEP
kqlazure-log-analytics
Bombbe
Bombbe

Reputation: 165

Convert disk size from megabytes to gigabytes in KQL query

I have following query that helps me data from vm disk

    InsightsMetrics
| where Namespace == "LogicalDisk"
| extend Tags = todynamic(Tags)
| extend Drive=tostring(todynamic(Tags)["vm.azm.ms/mountId"])
| extend DiskSize=tostring(todynamic(Tags)["vm.azm.ms/diskSizeMB"])
| summarize 
Free_space_percentage = avgif(Val, Name == 'FreeSpacePercentage'),
Free_Gigabytes = avgif(Val, Name == 'FreeSpaceMB') /1024
by Computer, Drive
| join (
InsightsMetrics
| where Namespace == "LogicalDisk"
| extend Tags = todynamic(Tags)
| extend DiskSize=tostring(todynamic(Tags)["vm.azm.ms/diskSizeMB"])
| extend Drive=tostring(todynamic(Tags)["vm.azm.ms/mountId"])
) on Computer, Drive
| where DiskSize has "."
| summarize  by Computer,Drive , Free_space_percentage, Free_Gigabytes, DiskSize

Issue is now that DiskSize is displayed in megabytes when all the rest are in gigabytes. I have now tried several hours to try convert it to gigas without luck. Could someone help me where and how should i do my convert in my query?

Upvotes: 3

Views: 1436

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44991

It seems your issue is not with converting MB to GB, but with structuring a query that will give you the average values as well as the disk size.
Assuming the disks' sizes are not changed during the query period, take_any() will do the trick.

InsightsMetrics
// | where TimeGenerated between(datetime(2022-04-01) .. datetime(2022-04-01 00:00:10))
| where Namespace == "LogicalDisk"
| extend Tags = todynamic(Tags)
| extend Drive = tostring(Tags["vm.azm.ms/mountId"])
| extend diskSizeGB = Tags["vm.azm.ms/diskSizeMB"]/1024.0
| summarize 
     avg_FreeSpacePercentage = avgif(Val, Name == 'FreeSpacePercentage')
    ,avg_FreeSpaceGB = avgif(Val, Name == 'FreeSpaceMB') /1024
    ,take_any(diskSizeGB)
    by Computer, Drive
Computer Drive avg_FreeSpacePercentage avg_FreeSpaceGB diskSizeGB
DC00.na.contosohotels.com C: 74.9538803100586 94.8232421875 126.50976181030273
DC00.na.contosohotels.com D: 91.4168853759766 14.6240234375 15.998043060302734
SQL12.na.contosohotels.com C: 57.7019577026367 72.998046875 126.50976181030273
SQL12.na.contosohotels.com D: 92.02197265625 29.4443359375 31.998043060302734
SQL12.na.contosohotels.com F: 99.9144668579102 127.7626953125 127.87304306030273
AppBE01.na.contosohotels.com C: 73.2973098754883 92.7275390625 126.50976181030273
AppBE01.na.contosohotels.com D: 91.3375244140625 14.611328125 15.998043060302734

Fiddle

Upvotes: 3

Related Questions