Patch installation failure for your machines - build in Log Analytics Query

Question

Hi,
I just noticed that there were this kinda build in query but it seems there are some issues or it is not working properly.

Query:

// Patch installation failure for your machines 
// List for each machine the installation status of the updates where the installation was not successful. 
// To create an alert for this query, click '+ New alert rule'
UpdateRunProgress
| where TimeGenerated>ago(1d) 
| where InstallationStatus == "NotStarted" 
| summarize by Title, InstallationStatus, SourceComputerId, UpdateId, Computer, ResourceId
| join kind= inner (
    UpdateRunProgress
    | where TimeGenerated>ago(1d) 
    | where InstallationStatus != "NotStarted" 
    | summarize by Title, InstallationStatus, SourceComputerId, UpdateId, Computer
) on UpdateId 
| where InstallationStatus1 != "Succeed"
| summarize by Title, InstallationStatus, Computer, ResourceId

This query gives me 72 items as results which is not really right which would mean that all my patches failed last night.

Running following query gives me 71 results which means that only 1 of updates did not went from 'Succeeded'. FYI: I know by fact that only 1 update failed or did not go trough right

UpdateRunProgress
| where TimeGenerated>ago(1d) 
| where InstallationStatus == "Succeeded"

and running following query gives me the one update that did fail

UpdateRunProgress
| where TimeGenerated>ago(1d) 
| where InstallationStatus contains "fail"

What need to be changed in query so it would display only updates that went from NotStarted to != "Succeed" (so basically failed or not did even start).

Answer 1

Hi,
I would assume there is a typo in line 14. It should be ' | where InstallationStatus1 != "Succeeded"' instead of '| where InstallationStatus1 != "Succeed"'

Update: all Possible states are documented here.

NotStarted - job not triggered yet.
Failed - job started but failed with an exception.
InProgress - job in progress.
MaintenanceWindowExceeded - if execution was remaining but maintenance window interval reached.
Succeeded - job succeeded.
InstallFailed - update failed to install successfully.
NotIncluded
Excluded

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.