System Updates queries, how to find counts and list machines

Hello Morten_Knudsen 

Have you also looked under "Update Management" - this is found from the Azure Portal - Automation Accounts - <acct name> - Update Management.  You can pin that as a dashboard tile.

Clicking on each entry takes you through to the latest Log Analytics syntax (so might have a better query to start with) 

Click on the compyter to go to 

The  Azure Automation solution also has some extra metadata, I've shared the 3 scenarios below :

1. Compliant  (NOTE:  I never went back to this, but its often one computer missing - so there must be another value to check)

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

2. non-compliant 

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

3. Not assessed (which was hard to convert when I did it last year)

Heartbeat
| where TimeGenerated>ago(12h) and OSType=~"Windows" and notempty(Computer)
| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId, Computer
| where Solutions has "updates"
| distinct SourceComputerId, Computer
| join  kind=leftouter 
(
    Update
    | where TimeGenerated>ago(14h) and OSType!="Linux"
    | summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Approved, Optional, Classification) by SourceComputerId, UpdateID, Computer
    | distinct SourceComputerId, Classification, UpdateState, Approved, Optional, Computer
    | summarize WorstMissingUpdateSeverity=max(iff(UpdateState=~"Needed" and (Optional==false or Classification has "Critical" or Classification has "Security") and Approved!=false, iff(Classification has "Critical", 4, iff(Classification has "Security", 2, 1)), 0)) by SourceComputerId, Computer
)
on SourceComputerId
| extend WorstMissingUpdateSeverity=coalesce(WorstMissingUpdateSeverity,-1)
| summarize computersBySeverity=count() by WorstMissingUpdateSeverity , Computer
| union (Heartbeat
| where TimeGenerated>ago(12h) and OSType=="Linux" and notempty(Computer)
| summarize arg_max(TimeGenerated, Solutions) by SourceComputerId, Computer
| where Solutions has "updates"
| distinct SourceComputerId , Computer
| join  kind=leftouter 
(
    Update
    | where TimeGenerated>ago(5h) and OSType=="Linux"
    | summarize hint.strategy=partitioned arg_max(TimeGenerated, UpdateState, Classification) by SourceComputerId, Product, ProductArch, Computer
    | distinct SourceComputerId, Classification, UpdateState, Computer
    | summarize WorstMissingUpdateSeverity=max(iff(UpdateState=~"Needed", iff(Classification has "Critical", 4, iff(Classification has "Security", 2, 1)), 0)) by SourceComputerId)
on SourceComputerId
| extend WorstMissingUpdateSeverity=coalesce(tolong(WorstMissingUpdateSeverity), -1)
)
| where WorstMissingUpdateSeverity ==-1
| summarize NotAssessedTrue=count(WorstMissingUpdateSeverity) by Computer