Search code examples
extendkql

Extend data to int value from string - Retuning no results in extended column

Hello knowledgeable KQL people,

I am looking to run a comparison against the software version within the DeviceTvmSoftwareInventory table in "Defender 365 Advanced Hunting". ALL of the data is retuned as strings, which means I can't run a < or > (greater than/less than) comparison. I have attempted to extend the SoftwareVersion to an integer, which does extend the results and presents me with an extra column which is an integer - as seen with getschema - Unfortunately none of the values are moved across to the new column and each of the items has a null value.

Any assistance is appreciated

DeviceTvmSoftwareInventory
    | where SoftwareVendor == "openssl"
    | extend soft_ver = toint(SoftwareVersion)
    | project DeviceId,DeviceName,SoftwareVendor,SoftwareVersion,soft_ver

Results retuned with 'getschema'

getschema_result

Results retuned with the extend column

results_returned

Solution

  • depending on what type of comparison you want to perform, you could either:

    1. use the built-in parse_version() function, or
    2. parse the version parts (major, minor, build, revision) from your input string, and then compare those according to your desired logic

    for example:

    datatable(v:string)
["0.0.0.5","0.0.7.0","0.0.3","0.2","0.1.2.0","1.2.3.4","1","7777.0.0.0"]
| extend parsed = parse_version(v)
| extend parts = split(v, ".")
| extend major = toint(parts[0]), minor = toint(parts[1]), build = toint(parts[2]), revision= toint(parts[3])
| project-away parts
    v parsed major minor build revision
    0.0.0.5 5 0 0 0 5
    0.0.7.0 700000000 0 0 7 0
    0.0.3 300000000 0 0 3
    0.2 20000000000000000 0 2
    0.1.2.0 10000000200000000 0 1 2 0
    1.2.3.4 1000000020000000300000004 1 2 3 4
    1 1000000000000000000000000 1
    7777.0.0.0 7777000000000000000000000000 7777 0 0 0