Im trying to create a KQL query to get Azure VMs not patched since 30days feature but it seems something in the query is wrong, but i’ve in pain figuring out what.

Here is the query using the let feature :

let Table01 = patchassessmentresources
| parse ["id"] with * "virtualMachines/" VmName "/patchAssessmentResults/" *
| extend publishedDateTime = todatetime(properties.publishedDateTime)
| extend patchName = properties.patchName
| extend kbId = properties.kbId
| extend patchId = properties.patchId
| extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
| where TimeGenerated > ago(31d)
| where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
| extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]), "Unsupported", properties.classifications[0]))
| where classification contains "critical" or classification contains "security" or classification contains "Other"
| summarize FirstTimeSeen = arg_min(TimeGenerated, *) by VmName, subscriptionId;
let Table02 = patchassessmentresources
| parse ["id"] with * "virtualMachines/" VmName "/patchAssessmentResults/" *
| extend publishedDateTime = todatetime(properties.publishedDateTime)
| extend patchName = properties.patchName
| extend kbId = properties.kbId
| extend patchId = properties.patchId
| extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
| where TimeGenerated > ago(31d)
| where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
| extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]),  "Unsupported", properties.classifications[0]))
| where classification contains "critical" or classification contains "security" or classification contains "Other" 
| summarize LastTimeSeen = arg_max(TimeGenerated, *) by VmName, subscriptionId;
Table01
| join kind=inner Table02 on $left.['id'] == $right.['id']

I copied the approach from one of my previous query witch is weirdly working fine.

I hope someone could have a look on it and help 🙂

I’ve tried using the simple join feature without success, ‘tags’ appears empty but should contains Azure VMs tags :

patchassessmentresources
| parse ["id"] with * "virtualMachines/" VmName "/patchAssessmentResults/" *
| extend publishedDateTime = todatetime(properties.publishedDateTime)
| extend patchName = properties.patchName
| extend kbId = properties.kbId
| extend patchId = properties.patchId
| extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
| where TimeGenerated > ago(31d)
| where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
| extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]), "Unsupported", properties.classifications[0]))
| where classification contains "critical" or classification contains "security" or classification contains "Other" 
| summarize FirstTimeSeen =  arg_min(TimeGenerated, *) by VmName, subscriptionId
| join kind=leftouter (
    patchassessmentresources
    | parse ["id"] with * "virtualMachines/" VmName "/patchAssessmentResults/" *
    | extend publishedDateTime = todatetime(properties.publishedDateTime)
    | extend patchName = properties.patchName
    | extend kbId = properties.kbId
    | extend patchId = properties.patchId
    | extend TimeGenerated = todatetime(properties.lastModifiedDateTime)
    | where TimeGenerated > ago(31d)
    | where type == "microsoft.compute/virtualmachines/patchassessmentresults/softwarepatches"
    | extend classification = iff(properties.classifications[0] =~ "UpdateRollUp", "UpdateRollup", iff(isempty(properties.classifications[0]),  "Unsupported", properties.classifications[0]))
    | where classification contains "critical" or classification contains "security" or classification contains "Other" 
    | summarize LastTimeSeen =  arg_max(TimeGenerated, *) by VmName, subscriptionId
    ) on ['id']
| join kind=leftouter (
    resources
    | where type =~ "microsoft.compute/virtualmachines"
    | extend OsType = properties.storageProfile.osDisk.osType
    | extend vmName = name
    | project vmName, id, location, tags, OsType
    ) on ['id']
| extend DifferenceBetweenFirstandLast = datetime_diff("day", LastTimeSeen, FirstTimeSeen)
| project VmName, subscriptionId, resourceGroup, location, FirstTimeSeen, LastTimeSeen, DifferenceBetweenFirstandLast, id, patchName, patchId, classification, ['tags']