free disk space overview using InsightsMetrics

Overview

I want to transfrom my query from using the table Virtual machines \ Perf to Virtual machines \ InsightsMetrics.

Status quo

I use the following query to get an overview of free disk space on all azure VMs in one subscription:

Perf
    | where ObjectName == "LogicalDisk" or // the object name used in Windows records
        ObjectName == "Logical Disk" // the object name used in Linux records
    | where CounterName == "Free Megabytes"
    | where InstanceName != "_Total"
    | where InstanceName !startswith "HarddiskVolume"
    | summarize arg_max(TimeGenerated, *) by CounterPath // arg_max over TimeGenerated returns the latest record
    | project TimeGenerated, Drive=InstanceName, bin(FreeGB = CounterValue / 1024, 0.1), Computer
    | join kind=inner (Perf
        | where ObjectName == "LogicalDisk" or // the object name used in Windows records
        ObjectName == "Logical Disk" // the object name used in Linux records
        | where CounterName == "% Free Space"
        | where InstanceName != "_Total"
        | where InstanceName !startswith "HarddiskVolume"
        | summarize arg_max(TimeGenerated, *) by CounterPath // arg_max over TimeGenerated returns the latest record
        | project TimeGenerated, Drive=InstanceName, bin(FreePercent = CounterValue, 1), Computer)
     on Drive, Computer
    | project TimeGenerated, Computer, Drive, FreeGB, FreePercent
    | order by Computer asc

Returns something like this:

TimeGenerated	Computer	Drive	FreeGB	FreePercent
23.8.2021, 14:33:35.557	hostname.domain.com	C:	81.9	64
23.8.2021, 14:33:14.857	hostname.domain.com	D:	73.6	98

This works fine if the Windows Performance counters are gathered by the Log Analytics agent.

Goal

I've noticed that kind of the same data are already gathered by VM Insights in the table InsigtsMetrics. Now I would like to transform my query so I can use this Virtual machines \ InsightsMetrics table to get the same results which means I could stop gather the counters via Log Analytics agent.

If I query this table for one vm with the counters and columns I need, the results look like this (It's not the same host as above):

TimeGenerated	Computer	Namespace	Name	Val	Tags
8/23/2021, 7:59:57.000 AM	hostname.domain.com	LogicalDisk	FreeSpaceMB	73,375	{"vm.azm.ms/mountId":"C:","vm.azm.ms/diskSizeMB":129545.99609375}
8/23/2021, 7:59:57.000 AM	hostname.domain.com	LogicalDisk	FreeSpacePercentage	56.641	{"vm.azm.ms/mountId":"C:"}

I do have some troubles to use these tags for join and summarize. Is there a way to convert these tags to regular columns or how should I approach this?

Explanation

The query above uses snippets I've found online (sources not known by me) and have been adjusted to meet my needs. It might not be most performant but it will rarely be executed (once a week or so) and only until proper monitoring has been installed.

Solution

I was now able to transform the query to use the table Virtual machines \ InsightsMetrics by using parse_json as described here by Shweta Lodha

Here my query:

InsightsMetrics
| where Name == 'FreeSpaceMB'
| summarize arg_max(TimeGenerated, *) by Tags, Computer
| extend Drive = tostring(parse_json(Tags)["vm.azm.ms/mountId"])
| extend Size = toreal(parse_json(Tags)["vm.azm.ms/diskSizeMB"])
| project TimeGenerated, Computer, Drive, bin(SizeGB = Size / 1024, 0.1), bin(FreeGB = Val / 1024, 1)
| join kind=inner (InsightsMetrics
    | where Name == "FreeSpacePercentage"
    | summarize arg_max(TimeGenerated, *) by Tags, Computer
    | extend Drive = tostring(parse_json(Tags)["vm.azm.ms/mountId"])
    | project TimeGenerated, Computer, Drive, bin(FreePercent = Val, 1.1))
on Computer, Drive
| project TimeGenerated, Computer, Drive, SizeGB, FreeGB, FreePercent
| order by Computer asc

Result looks like this:

TimeGenerated	Computer	Drive	SizeGB	FreeGB	FreePercent
24.8.2021, 06:33:27.000	hostname.domain.com	C:	126.5	102	80.3