Forum Discussion

Brass Contributor

May 28, 2019

Solved

System Updates queries, how to find counts and list machines

Hi, I'm trying to make a bunch of queries + new dashboard, that is similar to the built-in queries in the System Update Assessment in Azure LogAnalytics. But I simply don't know, how I can make the r...

azure monitor

Log

CliveWatson

May 28, 2019

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

CliveWatson

Silver Contributor

May 28, 2019

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

Morten_Knudsen

Brass Contributor

May 29, 2019

Thank you - I managed to solve my queries by using your hints

CliveWatson
Silver Contributor
May 30, 2019
If you have any samples to add, it would be great to post them back here for others to see?
- Morten Knudsen
  Copper Contributor
  May 30, 2019
  CliveWatson
  Here are the queries I have built for servers - there are similar ones for workstations. In general, I only focus on updates which are more than 31 days old, so I don't see this months patches. We use it to find any incompliant machines, which have not been patched properly during the monthly patch-window. I also ignore KBID890830 (MS Removal toolkit)
  
  I'm also joining data from a Custom table, CustomerDeviceConfigCMDB_CL, where I merge information about server placement, management-scope (Admin-IT vs. Prod-IT), etc.
  
  | join kind=leftouter (
  CustomerDeviceConfigCMDB_CL
  | extend Computer = strcat(DeviceName_s)
  | summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by DeviceName_s
  | project DeviceDescription_s, CountryPlacement_s, DeviceManagementScope_s,Computer = DeviceName_s, DeviceName_s
  ) on DeviceName_s
  | project DeviceName_s, DeviceDescription_s, CountryPlacement_s, DeviceManagementScope_s
  ---------
  Servers - Need Feature Updates - count
  Update
  | where TimeGenerated > now(-30d)
  | where OSType!="Linux" and Optional==false
  | where (UpdateState =~ "Needed") and ((Classification == "Feature Packs") or (Classification == "Updates") or (Classification == "Upgrades") or (Classification == "Service Packs")) and (Approved!=false) and (PublishedDate < ago(31d))
  | where (Product contains "server") and (Product !contains "drivers")
  | summarize arg_max(TimeGenerated, *) by Computer,UpdateID
  | distinct Computer
  | count
  ------------------------------------------------------------
  Servers - Need Important Security updates - count
  Update
  | where TimeGenerated > now(-30d)
  | where OSType!="Linux" and Optional==false
  | where (UpdateState =~ "Needed") and ((Classification == "Security Updates") or (Classification == "Update Rollups") or (Classification == "Monthly Rollups")) and (Approved!=false) and (PublishedDate < ago(31d)) and (KBID != "890830")
  | where (Product contains "server") and (Product !contains "drivers")
  | summarize arg_max(TimeGenerated, *) by Computer,UpdateID
  | distinct Computer
  | count
  ------------------------------------------------------------
  Servers - Need Critical Non-Security Updates - count
  Update
  | where TimeGenerated > now(-30d)
  | where OSType!="Linux" and Optional==false
  | where (UpdateState =~ "Needed") and ((Classification == "Critical Updates")) and (Approved!=false) and (PublishedDate < ago(31d))
  | where (Product contains "server") and (Product !contains "drivers")
  | summarize arg_max(TimeGenerated, *) by Computer,UpdateID
  | distinct Computer
  | count
  ------------------------------------------------------------
  Servers - Cannot collect patch status - check computer - count
  Heartbeat
  | where TimeGenerated>ago(30d) 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(30d) 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(30d) 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(30d) 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
  |count
  ------------------------------------------------------------
  Servers - Need Feature Updates - list
  Update
  | where TimeGenerated > now(-30d)
  | where OSType!="Linux" and Optional==false
  | where (UpdateState =~ "Needed") and ((Classification == "Feature Packs") or (Classification == "Updates") or (Classification == "Upgrades") or (Classification == "Service Packs")) and (Approved!=false) and (PublishedDate < ago(31d))
  | where (Product contains "server") and (Product !contains "drivers")
  | summarize arg_max(TimeGenerated, *) by Computer,UpdateID
  | distinct Computer
  ------------------------------------------------------------
  Servers - Need Important Security updates - list
  Update
  | where TimeGenerated > now(-30d)
  | where OSType!="Linux" and Optional==false
  | where (UpdateState =~ "Needed") and ((Classification == "Security Updates") or (Classification == "Update Rollups") or (Classification == "Monthly Rollups")) and (Approved!=false) and (PublishedDate < ago(31d)) and (KBID != "890830")
  | where (Product contains "server") and (Product !contains "drivers")
  | summarize arg_max(TimeGenerated, *) by Computer,UpdateID
  | distinct Computer
  ------------------------------------------------------------
  Servers - Need Critical Non-Security Updates - list
  Update
  | where TimeGenerated > now(-30d)
  | where OSType!="Linux" and Optional==false
  | where (UpdateState =~ "Needed") and ((Classification == "Critical Updates")) and (Approved!=false) and (PublishedDate < ago(31d))
  | where (Product contains "server") and (Product !contains "drivers")
  | summarize arg_max(TimeGenerated, *) by Computer,UpdateID
  | distinct Computer
  ------------------------------------------------------------
  Servers - Cannot collect patch status - check computer - list
  Heartbeat
  | where TimeGenerated>ago(30d) 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(30d) 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(30d) 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(30d) 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
  | extend DeviceName_s = toupper(Computer)
  | project DeviceName_s
  | join kind=leftouter (
  CustomerDeviceConfigCMDB_CL
  | extend Computer = strcat(DeviceName_s)
  | summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by DeviceName_s
  | project DeviceDescription_s, CountryPlacement_s, DeviceManagementScope_s,Computer = DeviceName_s, DeviceName_s
  ) on DeviceName_s
  | project DeviceName_s, DeviceDescription_s, CountryPlacement_s, DeviceManagementScope_s
  | sort by DeviceName_s desc
  ------------------------------------------------------------