Search code examples
azurekqlazure-monitoringazure-alerts

Query to show missing critical updates and security updates from 15days ago and longer

I am trying to create a query that will show me the missing critical updates and security updates on VM but only from 15days ago and longer but not within 15days. So I have created this query...

Update
| where Classification in ("Security Updates", "Critical Updates")
| where UpdateState == 'Needed' and Optional == false and Approved == true
| where TimeGenerated > ago(15d)
| summarize count() by Classification, Computer, _ResourceId

but when I run this query it gives me missing updates within 15 days, but what I am trying to achieve is missing updates from 15 days ago.

Any contribution will be appreciated. Thanks

Solution

  • The Update events are reported many times per day. Youn need to filter the last report and check the PublishedDate.

    Update
| where TimeGenerated > ago(1d)
| where PublishedDate < ago(15d)
| where Classification in ("Security Updates", "Critical Updates")
| where Optional == false
| summarize arg_max(TimeGenerated, Classification, UpdateState, Approved) by KBID, Computer, _ResourceId
| where UpdateState == 'Needed' and Approved == true
| summarize dcount(KBID) by Classification, Computer, _ResourceId