Jul 25 2019
01:05 AM
- last edited on
Apr 07 2022
06:01 PM
by
TechCommunityAP
Jul 25 2019
01:05 AM
- last edited on
Apr 07 2022
06:01 PM
by
TechCommunityAP
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
Jul 25 2019 02:23 AM
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:
// 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.
Jul 25 2019 06:28 AM
Jul 25 2019 07:19 AM - edited Jul 26 2019 01:07 AM
Jul 25 2019 07:19 AM - edited Jul 26 2019 01:07 AM
SolutionI 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.
Jul 26 2019 12:16 AM
@Dennis_Vind_Nielsen Just to clarify by "last Update Time" you mean the last time of when the scan was performed by Update Management?
Jul 26 2019 01:17 AM
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
Jul 26 2019 01:29 AM
Jul 26 2019 01:34 AM
@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
Jul 25 2019 07:19 AM - edited Jul 26 2019 01:07 AM
Jul 25 2019 07:19 AM - edited Jul 26 2019 01:07 AM
SolutionI 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.