Convert disk size from megabytes to gigabytes in KQL query

Question

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?

Answer 1

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