SOLVED

Windows update query

Copper Contributor

Hi.

 

I would like to see the total missing updates pr "server" in a column. I have one query but it count wrong :(

 

This is my query that i use right now

 

UpdateSummary
| where TimeGenerated>ago(12h)
| summarize any(SourceComputerId), any(SourceComputerId) by SourceComputerId
| join kind=leftouter
(
Update
| where TimeGenerated>ago(14h) and OSType!="Linux" and SourceComputerId in ((Heartbeat
| where TimeGenerated>ago(12h) and OSType=~"Windows" and notempty(Computer)
| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId
| where Solutions has "updates"
| distinct SourceComputerId))
| summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification, Title, Optional, Approved, Computer, ComputerEnvironment) by Computer, SourceComputerId, UpdateID
| summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has "Critical" and UpdateState=~"Needed" and Approved!=false), missingSecurityUpdatesCount=countif(Classification has "Security" and UpdateState=~"Needed" and Approved!=false), missingOtherUpdatesCount=countif(Classification !has "Critical" and Classification !has "Security" and UpdateState=~"Needed" and Optional==false and Approved!=false), lastAssessedTime=max(TimeGenerated), lastUpdateAgentSeenTime="" by SourceComputerId
| extend compliance=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0, 2, 1)
| extend ComplianceOrder=iff(missingCriticalUpdatesCount > 0 or missingSecurityUpdatesCount > 0 or missingOtherUpdatesCount > 0, 1, 3)
)
on SourceComputerId
| project displayName=Computer, missingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), ComplianceOrder=coalesce(ComplianceOrder, 2)
| order by ComplianceOrder asc, missingCriticalUpdatesCount desc, missingSecurityUpdatesCount desc, missingOtherUpdatesCount desc, displayName asc
| project-away ComplianceOrder
 
 
Any help is appreciated
 
/Dennis 
11 Replies

@Dennis_Vind_Nielsen 

 

Can you give us a clue to where you think the error is?    What count is wrong?

 

The two main queries you see on the Update Management Dashboard are here:

https://github.com/Azure/Azure-Security-Center/blob/master/Legacy%20Log%20Analytics%20dashboards/Not... 

 

// compliant
Update 
| where UpdateState != "Needed"  and (Classification == "Security Updates" or Classification == "Critical Updates")
| distinct  Computer
| count
 
// non-compliant
Update 
| where UpdateState == "Needed"  and (Classification == "Security Updates" or Classification == "Critical Updates")
| summarize count() by Computer

 

I have the 'not assessed' one as well.

@CliveWatson 

your query is the first.... mine the second

Annotation 2019-07-25 152628.jpgAnnotation 2019-07-25 152816.jpg

best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

Hi@Dennis_Vind_Nielsen 

I would assume that you have taken that query from Update Management. They are doing some more complex calculation like if the server is up, etc. because of that their query is very complex. I would assume you want a little bit more simplified one. The below one is the simplest I could build. Let me know if it works for you:

 

 
Update
| summarize arg_max(TimeGenerated, *) by Computer, Title, Classification, UpdateID
| where UpdateState == "Needed" 
| summarize MissingUpdatesCount = count() by Computer 
 

 I just saw that someone else started to respond on this thread so apologies that I am interfering in the thread.

@Stanislav Zhelyazkov 

 

Thanks... Nice simple query

If i want to have "last Update Time" as well?

@Dennis_Vind_Nielsen Just to clarify by "last Update Time" you mean the last time of when the scan was performed by Update Management?

No, i ment the LastUpdateApplied date :)

@Dennis_Vind_Nielsen 

 

Not sure how accurate is UpdateSummary table but here you go:

let summary = UpdateSummary
| summarize arg_max(TimeGenerated, *) by Computer
| project Computer, LastUpdateApplied;
Update
| summarize arg_max(TimeGenerated, *) by Computer, Title, Classification, UpdateID
| where UpdateState == "Needed"  
| summarize MissingUpdatesCount = count() by Computer 
| join (
    summary
) on Computer
Worked really fine :) Thanks a lot

Can you for format the LastUpdateApplied to MM-dd-yyyy

@Dennis_Vind_Nielsen Sure

These small things you should try doing in your own though as it will help you in long term building Kusto queries. If you search in google: 'Kusto format date' you will click on the first result, look at the example and implement it. These small operators are easy to implement. Here is the query:

let summary = UpdateSummary
| summarize arg_max(TimeGenerated, *) by Computer
| project Computer, format_datetime(LastUpdateApplied, 'MM-dd-yyyy ');
Update
| summarize arg_max(TimeGenerated, *) by Computer, Title, Classification, UpdateID
| where UpdateState == "Needed"  
| summarize MissingUpdatesCount = count() by Computer 
| join (
    summary
) on Computer
You are the king

Thanks
1 best response

Accepted Solutions
best response confirmed by Stanislav Zhelyazkov (MVP)
Solution

Hi@Dennis_Vind_Nielsen 

I would assume that you have taken that query from Update Management. They are doing some more complex calculation like if the server is up, etc. because of that their query is very complex. I would assume you want a little bit more simplified one. The below one is the simplest I could build. Let me know if it works for you:

 

 
Update
| summarize arg_max(TimeGenerated, *) by Computer, Title, Classification, UpdateID
| where UpdateState == "Needed" 
| summarize MissingUpdatesCount = count() by Computer 
 

 I just saw that someone else started to respond on this thread so apologies that I am interfering in the thread.

View solution in original post