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

CodePudding user response：

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

CodePudding user response：

• When data of 15 days ago is needed, filter of the query should be TimeGenerated < ago(15d)
• ago(15d) will give the date 15 days before of current date.
• So, when we give Timegenerated > ago(15d) as a filter condition, the data from 15 days ago to current day will be displayed.

I tried to repro with sample table Covid19_map2 in Azure data explorer. This table has LastRefreshed column which is of datetime type. ago() function is applied to this LastRefreshed column and output is captured with both cases, lastRefreshed >ago(15d) and lastRefreshed <ago(15d).

lastRefreshed >ago(15d):

There are no data which got refreshed within 15 days. So there are no rows to display here.

lastRefreshed < ago(15d):

Data which are older than 15 days are displayed

Refer Microsoft document on ago function.