Question 104740 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – 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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0
    • 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.
    enter image description here

    lastRefreshed < ago(15d):

    Data which are older than 15 days are displayed
    enter image description here

    Refer Microsoft document on ago function.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search