SOLVED

System Updates queries, how to find counts and list machines

Brass Contributor

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 right queries. I have tried to describe the needed queries below. Does anyone have something they would share with me - or similar queries, which can inspire me.

 

My customer has made their own definitions/grouping:

// CRITICAL NON-SECURITY UPDATES (monthly): Critical Updates
// IMPORTANT SECURITY UPDATES (monthly): Security Updates, Security-only update, Update Rollups, Monthly Rollups
// IMPORTANT FEATURE UPDATES (planned): Feature Packs, Updates, Servicing Stack Updates, Upgrades, Service Packs

 

// Output: Pie Chart
// Need Critical Updates = CriticalUpdatesCountTotal
// Need Important Security Updates = SecurityUpdatesCountTotal
// Need Important Feature Updates = FeatureUpdatesTotal
// Up to date = NoPendingTotal

 

-------------------------------

Basically I want to find 

  • number of machines, which has CRITICAL NON-SECURITY UPDATES pending, which are more than 1 months old to exclude this months updates
  • number of machines, which have IMPORTANT SECURITY UPDATES pending which are more than 1 months old to exclude this months updates
  • number of machines, which have IMPORTANT FEATURE UPDATES pending which are more than 1 months old to exclude this months updates
  • number of machines, which have NO pending updates which are more than 1 months old to exclude this months updates

Hints ??

 

// ----------------------------------------------------------------------------------
// Query 1: Count of machines (dashboard)
// ----------------------------------------------------------------------------------
Update
// find all Windows updates - excluding Definition Updates and Drivers
| where OSType!="Linux" and Optional==false and Classification != "Definition Updates" and Classification != "Drivers"

 

// Find newest updates entries per computer
| summarize hint.strategy=partitioned arg_max(TimeGenerated, *) by Computer,SourceComputerId,UpdateID

 

// Summarize number of machines, which has CRITICAL NON-SECURITY UPDATES pending, which are more than 1 months old to exclude ths months updates
CriticalUpdatesCountTotal = UpdateState=~"Needed" and Approved!=false and Classification contains "Critical Updates" and PublishedDate < now(-31d)

 

// Summarize number of machines, which have IMPORTANT SECURITY UPDATES pending which are more than 1 months old to exclude ths months updates
SecurityUpdatesCountTotal = UpdateState=~"Needed" and Approved!=false and ( (Classification contains "Security Updates") or (Classification contains "Rollups") ) and PublishedDate < now(-31d)

 

// Summarize number of machines, which have FEATURE UPDATES pending which are more than 1 months old to exclude ths months updates
FeatureUpdatesTotal = UpdateState=~"Needed" and Approved!=false and ( (Classification contains "Feature Packs") or (Classification == "Updates") or (Classification == "Upgrades") or (Classification contains "Service Packs") ) and PublishedDate < now(-31d)

 

// Summarize number of machines, which have NO pending updates which are more than 1 months old to exclude ths months updates
NoPendingTotal = UpdateState=~"Needed" and Approved!=false and ( (Classification contains "Feature Packs") or (Classification == "Updates") or (Classification == "Upgrades") or (Classification contains "Service Packs") ) and PublishedDate < now(-31d)


// Output: Pie Chart
// Need Critical Updates = CriticalUpdatesCountTotal
// Need Important Security Updates = SecurityUpdatesCountTotal
// Need Important Feature Updates = FeatureUpdatesTotal
// Up to date = NoPendingTotal

 

// ----------------------------------------------------------------------------------

Query 2: List of machines which has CRITICAL NON-SECURITY UPDATES pending, which are more than 1 months old
// ----------------------------------------------------------------------------------
Query 3: List of machines which has IMPORTANT SECURITY UPDATES pending, which are more than 1 months old
// ----------------------------------------------------------------------------------
Query 4: List of machines which has FEATURE UPDATES pending, which are more than 1 months old

// ----------------------------------------------------------------------------------
Query 5: List of machines which has NO pending updates, which are more than 1 months old

 

// ----------------------------------------------------------------------------------
// Microsoft Definition
// ----------------------------------------------------------------------------------
// Critical update - A widely released fix for a specific problem that addresses a critical, non-security-related bug.
// Definition update - A widely released and frequent software update that contains additions to a product’s definition database. Definition databases are often used to detect objects that have specific attributes, such as malicious code, phishing websites, or junk mail.
// Driver - Software that controls the input and output of a device.
// Feature pack - New product functionality that is first distributed outside the context of a product release and that is typically included in the next full product release.
// Security update - A widely released fix for a product-specific, security-related vulnerability. Security vulnerabilities are rated by their severity. The severity rating is indicated in the Microsoft security bulletin as critical, important, moderate, or low.
// Service pack - A tested, cumulative set of all hotfixes, security updates, critical updates, and updates. Additionally, service packs may contain additional fixes for problems that are found internally since the release of the product. Service packs my also contain a limited number of customer-requested design changes or features.
// Tool - A utility or feature that helps complete a task or set of tasks.
// Update - A widely released fix for a specific problem. An update addresses a noncritical, non-security-related bug.
// Update rollup - A tested, cumulative set of hotfixes, security updates, critical updates, and updates that are packaged together for easy deployment. A rollup generally targets a specific area, such as security, or a component of a product, such as Internet Information Services (IIS).
// Security-only update - An update that collects all the new security updates for a given month and for a given product, addressing security-related vulnerabilities
// Monthly Rollup - A tested, cumulative set of updates. They include both security and reliability updates that are packaged together. The Monthly Rollup is product specific, addresses both new security issues and nonsecurity issues in a single update and will proactively include updates that were released in the past. Security vulnerabilities are rated by their severity. The severity rating is indicated in the Microsoft security bulletin as critical, important, moderate, or low. This Monthly Rollup would be displayed under the title Security Monthly Quality Rollup when you download or install. This Monthly Rollup will be classified as an "Important" update on Windows Update and will automatically download and install if your Windows Update settings are configured to automatically download and install Important updates.
// Servicing Stack Updates (SSU) - The "servicing stack" is the code that installs other operating system updates. Additionally, it contains the "component-based servicing stack" (CBS), which is a key underlying component for several elements of Windows deployment, such as DISM, SFC, changing Windows features or roles, and repairing components. The CBS is a small component that typically does not have updates released every month.

6 Replies
best response confirmed by Morten_Knudsen (Brass Contributor)
Solution

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) 

 

Annotation 2019-05-28 194554.jpg

Click on the compyter to go to 

Annotation 2019-05-28 194629-2.jpg

 

 

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 Where is the query for these below - exactly those I need :)

2019-05-28_2229.png

 

@Morten_Knudsen 

 

Critical & Security I think is:

//critical & sec
Update 
| where UpdateState == "Needed"  and (Classification == "Security Updates" or Classification == "Critical Updates")
| distinct  Computer
| count

Non-compliant and not-assessed I gave you.

 

Other I need to take a look....

Thank you - I managed to solve my queries by using your hints
If you have any samples to add, it would be great to post them back here for others to see?

@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
------------------------------------------------------------

1 best response

Accepted Solutions
best response confirmed by Morten_Knudsen (Brass Contributor)
Solution

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) 

 

Annotation 2019-05-28 194554.jpg

Click on the compyter to go to 

Annotation 2019-05-28 194629-2.jpg

 

 

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

 

 

 

 

View solution in original post