KQL – Working with version numbers using parse_version

by

Overview

If you have versions numbers that contain major and minor decimal places such as 1.2, 1.3, 1.3.1, 1.5 you may have noticed ordering by these fields doesn’t really work well. As we can see from the example below order by doesn’t properly order the version number column. 1.21.1 is a higher version number than 1.7

Using parse_version()

The parse_version() function can be used to convert version numbers into a comparable decimal number. This function accepts a scalar expression of type string that specifies the version to be parsed. If conversion is successful, the result will be a decimal. If conversion is unsuccessful, the result will be null.

This function works a bit differently when using in the Resource Graph Explorer vs Log Analytics. In resource graph the function returns the value as a decimal in the form of json. In Log Analytics the function also returns a decimal value with the version and parsed version numbers.

Resource Graph

Log Analytics

Examples

Order by version number

In this example we return version numbers for machine extensions and then order by version number

Resource Graph

resources
| where type contains "microsoft.compute/virtualmachines/extensions" or 
        type contains "microsoft.hybridcompute/machines/extensions" or
        type contains "microsoft.compute/virtualmachinescalesets/extensions"
| extend version = tostring(properties.typeHandlerVersion)
| extend versionParsed = parse_version(version)
| order by versionParsed desc 
| project name, version, versionParsed

Log Analytics

let versions = datatable(name:string, version:string)
[
    "AzureMonitorAgent", "1.0.0.16,1.0.0.17,1.0.0.18,1.0.0.20,1.0.0.22,1.0.0.23,1.0.0.25,1.0.0.26,1.0.0.27,1.0.0.28,1.0.0.29,1.0.0.30,1.0.0.31,1.0.0.32,1.0.0.33,1.0.0.34,1.0.0.35,1.0.0.36,1.0.1.0,1.0.1.1,1.0.1.2,1.0.1.3,1.0.2.1,1.0.3.0,1.0.3.1",
    "Defender for Endpoint", "1.0.0.16,1.0.0.17,1.0.0.18,1.0.0.20,1.0.0.22,1.0.0.23,1.0.0.25,1.0.0.26,1.0.0.27,1.0.0.28,1.0.0.29,1.0.0.30,1.0.0.31,1.0.0.32,1.0.0.33,1.0.0.34,1.0.0.35,1.0.0.36,1.0.1.0,1.0.1.1,1.0.1.2,1.0.1.3,1.0.2.1,1.0.3.0,1.0.3.1",
    "Azure Guest Configuration", "0.9.5,1.10.1,1.10.5,1.10.7,1.10.9,1.12.2,1.14.5,1.14.7,1.15.1,1.15.2,1.15.3,1.17.5,1.19.3,1.21.1,1.22.2,1.24.2,1.5.126,1.5.127,1.5.133,1.6.2,1.7.0,1.7.1,1.9.1"
];
versions
| extend version = todynamic(version)
| mv-expand split(version, ',')
| extend versionParsed = parse_version(tostring(version))
| project name, version, versionParsed
| order by versionParsed

Get the highest version number

In this example we return version numbers for machine extensions and then get the highest version number by extension

Resource Graph

resources
| where type contains "microsoft.compute/virtualmachines/extensions" or 
        type contains "microsoft.hybridcompute/machines/extensions" or
        type contains "microsoft.compute/virtualmachinescalesets/extensions"
| extend version = tostring(properties.typeHandlerVersion)
| extend versionParsed = parse_version(version)
| summarize arg_max(versionParsed, *) by name
| project name, version

Log Analytics

let versions = datatable(name:string, version:string)
[
    "AzureMonitorAgent", "1.0.0.16,1.0.0.17,1.0.0.18,1.0.0.20,1.0.0.22,1.0.0.23,1.0.0.25,1.0.0.26,1.0.0.27,1.0.0.28,1.0.0.29,1.0.0.30,1.0.0.31,1.0.0.32,1.0.0.33,1.0.0.34,1.0.0.35,1.0.0.36,1.0.1.0,1.0.1.1,1.0.1.2,1.0.1.3,1.0.2.1,1.0.3.0,1.0.3.1",
    "Defender for Endpoint", "1.0.0.16,1.0.0.17,1.0.0.18,1.0.0.20,1.0.0.22,1.0.0.23,1.0.0.25,1.0.0.26,1.0.0.27,1.0.0.28,1.0.0.29,1.0.0.30,1.0.0.31,1.0.0.32,1.0.0.33,1.0.0.34,1.0.0.35,1.0.0.36,1.0.1.0,1.0.1.1,1.0.1.2,1.0.1.3,1.0.2.1,1.0.3.0,1.0.3.1",
    "Azure Guest Configuration", "0.9.5,1.10.1,1.10.5,1.10.7,1.10.9,1.12.2,1.14.5,1.14.7,1.15.1,1.15.2,1.15.3,1.17.5,1.19.3,1.21.1,1.22.2,1.24.2,1.5.126,1.5.127,1.5.133,1.6.2,1.7.0,1.7.1,1.9.1"
];
versions
| extend version = todynamic(version)
| mv-expand split(version, ',')
| extend versionParsed = parse_version(tostring(version))
| summarize arg_max(versionParsed, *) by name
| project name, version, versionParsed
| order by versionParsed
%d