question

AshishJaviya-3877 avatar image
0 Votes"
AshishJaviya-3877 asked SwathiDhanwada-MSFT edited

Azure Update Manager query

In recently we had an issue - where we have setup few scheduled task to do patching of the server for critical and important update using update manager. it failed and we missed to get any information - which caused us many issues. Hence we have decided to build the dashboard to help us to over all status. I have 2 question : 1) Query needs : Server name, Subscription, Owner, Last time when it was patched (may be using Jobs/Scheduler), critical updates pending, longest critical update pending. 2) Is KUSTO query gives you result limited to that subscription ? How can be build the log analytics report on over all resources.

azure-automation
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

SwathiDhanwada-MSFT avatar image
0 Votes"
SwathiDhanwada-MSFT answered AshishJaviya-3877 commented

@AshishJaviya-3877 Welcome to Microsoft Q & A Community Forum. Here is query which lists out the information like ComputerId, Computer Name , missedCriticalUpdatesCount , missingSecurityUpdatesCount, missingOtherUpdatesCount, lastAssessedTime. If in case, you want to limit the records to specific subscription , you can just add "where subscriptionId= "<<provide subscription id>>" to the query.

 Heartbeat
 | where TimeGenerated>ago(12h) and OSType=="Linux" and notempty(Computer)
 | summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId 
 | where Solutions has "updates"
 | extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=1, environment=iff(ComputerEnvironment=~"Azure", 1, 2), scopedToUpdatesSolution=true, lastUpdateAgentSeenTime=""
 | join kind=leftouter
 (
     Update
     | where TimeGenerated>ago(5h) and OSType=="Linux" and SourceComputerId in ((Heartbeat
     | where TimeGenerated>ago(12h) and OSType=="Linux" 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, Product, Computer, ComputerEnvironment) by SourceComputerId, Product, ProductArch
     | summarize Computer=any(Computer), ComputerEnvironment=any(ComputerEnvironment), missingCriticalUpdatesCount=countif(Classification has "Critical" and UpdateState=~"Needed"), missingSecurityUpdatesCount=countif(Classification has "Security" and UpdateState=~"Needed"), missingOtherUpdatesCount=countif(Classification !has "Critical" and Classification !has "Security" and UpdateState=~"Needed"), 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 id=SourceComputerId, displayName=Computer, sourceComputerId=SourceComputerId, scopedToUpdatesSolution=true, missingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=1, environment=iff(ComputerEnvironment=~"Azure", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2)
 | union(Heartbeat
 | where TimeGenerated>ago(12h) and OSType=~"Windows" and notempty(Computer)
 | summarize arg_max(TimeGenerated, Solutions, Computer, ResourceId, ComputerEnvironment, VMUUID) by SourceComputerId
 | where Solutions has "updates"
 | extend vmuuId=VMUUID, azureResourceId=ResourceId, osType=2, environment=iff(ComputerEnvironment=~"Azure", 1, 2), scopedToUpdatesSolution=true, lastUpdateAgentSeenTime=""
 | 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 id=SourceComputerId, displayName=Computer, sourceComputerId=SourceComputerId, scopedToUpdatesSolution=true, missingCriticalUpdatesCount=coalesce(missingCriticalUpdatesCount, -1), missingSecurityUpdatesCount=coalesce(missingSecurityUpdatesCount, -1), missingOtherUpdatesCount=coalesce(missingOtherUpdatesCount, -1), compliance=coalesce(compliance, 4), lastAssessedTime, lastUpdateAgentSeenTime, osType=2, environment=iff(ComputerEnvironment=~"Azure", 1, 2), ComplianceOrder=coalesce(ComplianceOrder, 2) )
 | order by ComplianceOrder asc, missingCriticalUpdatesCount desc, missingSecurityUpdatesCount desc, missingOtherUpdatesCount desc, displayName asc 
 | project-away ComplianceOrder

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

First of all - thanks for quick response. Much Appreciated !!

Running this query without "where subs.." still restrict the result to one subscription. so what is the best way to run this to get details of all the servers under company tanent.

Additionally , I see some discrepancy between the data shown on azure dashboard and out come of query.

![76841-image.png][2]

Additionally, i am interested in seeing the value in the tab "History" Which says last update "failed"

where i can find that out ?

76842-image.png

I too assume that summary show on the top of the page - is that using Kusto as well ? 12/12 failed types.





[2]: /answers/storage/attachments/76757-image.png

0 Votes 0 ·
image.png (4.1 KiB)
image.png (26.1 KiB)

@AshishJaviya-3877 To know the details of the across tenant (assuming data is distributed across different log analytics ), you can execute cross query across workspaces . To learn more, refer this document. I don't have environment to test it, and provide you exact query but I hope this should help you.

To know the update of the deployment status of a scheduled deployment, the information is logged in "UpdateRunProgress" table. You can query the table by filtering it with Installation status. List of installation status that are logged are

77026-image.png


0 Votes 0 ·
image.png (22.1 KiB)