Monitoring Windows Updates Health

[Teeevv 4]

As we all know managing windows updates can be a pain sometimes, and thanks to the report created by Gavsto (https://www.gavsto.com/free-report-get-a-second-opinion-on-your-patching/) we've discovered the patch compliance reporting within Automate can be very misleading. Using this report I started to dig into the anomalies and discovered two problems

1. The report showed the last Cumulative Update and when the installation was attempted, whether it was successful or not
2. I didn't want to rely on a manual task to review a report, I wanted automate to raise a ticket when a machine fell behind by 35 days so we can go fix the issue

Based on these issues, I decided the best way to proceed was to expand upon Gavsto's idea. Since we're trying to verify the actual current patch level of every computer, I didn't want to rely on the information already existing in Automate in case it was incorrect. I figured the most accurate source of the current patch level would be the computer itself. I created two new extra data fields and populated them with a small powershell script. The logic for the powershell script followed the same principal as the report, list out the latest cumulative update's that were installed but with the added condition of making sure the update was successful. The EDF's I created and the powershell scripts to populate them were

• Last Successful Patch - The Title of the patch that was last installed

$Result = @();
$Session = New-Object -ComObject Microsoft.Update.Session;
$Searcher = $Session.CreateUpdateSearcher();
$HistoryCount = $Searcher.GetTotalHistoryCount();
$Result = (
	$Searcher.QueryHistory(0,$HistoryCount) | Where-Object {
		(
			$_.ResultCode -eq 2 -or 
			$_.ResultCode -eq 3
		) -and (
			$_.Title -like '*Security Monthly Quality*' -or 
			$_.Title -like '*Servicing Stack Update*' -or 
			$_.Title -like '*Cumulative Security Update*' -or 
			$_.Title -like '*Cumulative Update For*' -or 
			$_.Title -like '*Feature update to*'
		) -and (
			$_.Title -notlike '*Cumulative Security Update For Internet Explorer*' -and 
			$_.Title -notlike '*Cumulative Security Update for ActiveX*' -and 
			$_.Title -notlike '*Cumulative Update for .NET Framework*'
		)
	} | Sort-Object Date);
If ($Result.count -gt 0) {
	return ($Result[-1]).Title 
} Else { 
	Return "No CU Patching Information Available" 
};

• Last Successful Patch Timestamp - The date and time this patch was installed, in a YYYYMMDD-HHMMSS format

$Result = @(); 
$Session = New-Object -ComObject Microsoft.Update.Session; 
$Searcher = $Session.CreateUpdateSearcher(); 
$HistoryCount = $Searcher.GetTotalHistoryCount(); 
$Result = (
	$Searcher.QueryHistory(0,$HistoryCount) | Where-Object {
		(
			$_.ResultCode -eq 2 -or 
			$_.ResultCode -eq 3
		) -and (
			$_.Title -like '*Security Monthly Quality*' -or 
			$_.Title -like '*Servicing Stack Update*' -or 
			$_.Title -like '*Cumulative Security Update*' -or 
			$_.Title -like '*Cumulative Update For*' -or 
			$_.Title -like '*Feature update to*'
		) -and (
			$_.Title -notlike '*Cumulative Security Update For Internet Explorer*' -and 
			$_.Title -notlike '*Cumulative Security Update for ActiveX*' -and 
			$_.Title -notlike '*Cumulative Update for .NET Framework*'
		)
	} | Sort-Object Date); 
If ($Result.count -gt 0) { 
	return ($Result[-1]).Date.ToString('yyyyMMdd-HHmmss') 
} Else {
	Return "No CU Patching Information Available" 
};

 

The added benefit from this approach was it didn't matter how the patch was installed, whether through automate, manually or by a third party, it should always appear which is great to get an accurate sense of patch level when on boarding a new client.

Now we had the latest patch information from the computer in Automate I simply created an internal monitor to compare the timestamp and log a ticket for anything that hasn't successfully patched in over 35 days.

 

So far this has been working for me, it's also highlighted a few machines that haven't been patched since last year that didn't show up in the report. A few ideas we've had so far to improve upon this

• Add some verification to only log a ticket if the machine has been online during the past 35 days
• Run some autofix actions prior to logging a ticket such as
  • Force the EDF's to update and revalidate
  • Attempt to run a patch job

I'd love to hear peoples feedback on this approach, any gotcha's you might see catching us out in future, or ideas to improve on the solution. If nothing else hopefully this posts helps some others detect systems that aren't patching properly.

 

 

[myordy 1]

Thank you for the great info! I started implementing this today and am excited about the results so far.

[Dayrak 0]

@Teeevv

@Gavsto

Thanks for sharing this great way to monitor Updates.

Would you please share the steps of how you created two new extra data fields and populated them with a small powershell script?

Cheers.

Edited August 5, 2019 by Dayrak

[HickBoy 4]

Great idea...I added the following to the first line of each script to squelch extra error messages from the script polluting the result EDF.  I am also working on a report to pull the data from the EDF in spreadsheet format for easy viewing.

$ErrorActionPreference= 'silentlycontinue';

 

[Dave C 1]

One issue I have now is that a PC isnt reporting the Patching to Automate correctly.

Seems it cant send Patch Inventory.
I can see it has later updates in the History but not from Automate.
Any ideas?

Thanks

 

 

 

 

Edited August 8, 2019 by Dave C

[Dave C 1]

On 6/23/2019 at 9:06 PM, Teeevv said:

As we all know managing windows updates can be a pain sometimes, and thanks to the report created by Gavsto (https://www.gavsto.com/free-report-get-a-second-opinion-on-your-patching/) we've discovered the patch compliance reporting within Automate can be very misleading. Using this report I started to dig into the anomalies and discovered two problems

1. The report showed the last Cumulative Update and when the installation was attempted, whether it was successful or not
2. I didn't want to rely on a manual task to review a report, I wanted automate to raise a ticket when a machine fell behind by 35 days so we can go fix the issue

Based on these issues, I decided the best way to proceed was to expand upon Gavsto's idea. Since we're trying to verify the actual current patch level of every computer, I didn't want to rely on the information already existing in Automate in case it was incorrect. I figured the most accurate source of the current patch level would be the computer itself. I created two new extra data fields and populated them with a small powershell script. The logic for the powershell script followed the same principal as the report, list out the latest cumulative update's that were installed but with the added condition of making sure the update was successful. The EDF's I created and the powershell scripts to populate them were

• Last Successful Patch - The Title of the patch that was last installed

$Result = @();
$Session = New-Object -ComObject Microsoft.Update.Session;
$Searcher = $Session.CreateUpdateSearcher();
$HistoryCount = $Searcher.GetTotalHistoryCount();
$Result = (
	$Searcher.QueryHistory(0,$HistoryCount) | Where-Object {
		(
			$_.ResultCode -eq 2 -or 
			$_.ResultCode -eq 3
		) -and (
			$_.Title -like '*Security Monthly Quality*' -or 
			$_.Title -like '*Servicing Stack Update*' -or 
			$_.Title -like '*Cumulative Security Update*' -or 
			$_.Title -like '*Cumulative Update For*' -or 
			$_.Title -like '*Feature update to*'
		) -and (
			$_.Title -notlike '*Cumulative Security Update For Internet Explorer*' -and 
			$_.Title -notlike '*Cumulative Security Update for ActiveX*' -and 
			$_.Title -notlike '*Cumulative Update for .NET Framework*'
		)
	} | Sort-Object Date);
If ($Result.count -gt 0) {
	return ($Result[-1]).Title 
} Else { 
	Return "No CU Patching Information Available" 
};

• Last Successful Patch Timestamp - The date and time this patch was installed, in a YYYYMMDD-HHMMSS format

$Result = @(); 
$Session = New-Object -ComObject Microsoft.Update.Session; 
$Searcher = $Session.CreateUpdateSearcher(); 
$HistoryCount = $Searcher.GetTotalHistoryCount(); 
$Result = (
	$Searcher.QueryHistory(0,$HistoryCount) | Where-Object {
		(
			$_.ResultCode -eq 2 -or 
			$_.ResultCode -eq 3
		) -and (
			$_.Title -like '*Security Monthly Quality*' -or 
			$_.Title -like '*Servicing Stack Update*' -or 
			$_.Title -like '*Cumulative Security Update*' -or 
			$_.Title -like '*Cumulative Update For*' -or 
			$_.Title -like '*Feature update to*'
		) -and (
			$_.Title -notlike '*Cumulative Security Update For Internet Explorer*' -and 
			$_.Title -notlike '*Cumulative Security Update for ActiveX*' -and 
			$_.Title -notlike '*Cumulative Update for .NET Framework*'
		)
	} | Sort-Object Date); 
If ($Result.count -gt 0) { 
	return ($Result[-1]).Date.ToString('yyyyMMdd-HHmmss') 
} Else {
	Return "No CU Patching Information Available" 
};

 

The added benefit from this approach was it didn't matter how the patch was installed, whether through automate, manually or by a third party, it should always appear which is great to get an accurate sense of patch level when on boarding a new client.

Now we had the latest patch information from the computer in Automate I simply created an internal monitor to compare the timestamp and log a ticket for anything that hasn't successfully patched in over 35 days.

 

So far this has been working for me, it's also highlighted a few machines that haven't been patched since last year that didn't show up in the report. A few ideas we've had so far to improve upon this

• Add some verification to only log a ticket if the machine has been online during the past 35 days
• Run some autofix actions prior to logging a ticket such as
  • Force the EDF's to update and revalidate
  • Attempt to run a patch job

I'd love to hear peoples feedback on this approach, any gotcha's you might see catching us out in future, or ideas to improve on the solution. If nothing else hopefully this posts helps some others detect systems that aren't patching properly.

 

 

If it helps anyone else I found that some servers had installed the Preview Update instead so had to add this line to both Scripts.
            $_.Title -like '*Preview of Monthly Quality*' -or 
 

To make anyones lives Easier ive attached a copy of the Script I used to populate the EDFs I created.

 

Last Successful Patch Date.xml

Edited August 8, 2019 by Dave C

[HickBoy 4]

Anyone Created a decent DataView or Report template they want to share that pulls from their EDF's?

[Dayrak 0]

8 minutes ago, HickBoy said:

Anyone Created a decent DataView or Report template they want to share that pulls from their EDF's?

This works.

https://www.gavsto.com/free-report-get-a-second-opinion-on-your-patching/

[HickBoy 4]

9 minutes ago, Dayrak said:

This works.

https://www.gavsto.com/free-report-get-a-second-opinion-on-your-patching/

Wow... that's a great report.  It does work and is a great canvas to add some features.  Thx for pointing me in that direction...

[Dayrak 0]

1 hour ago, HickBoy said:

Wow... that's a great report.  It does work and is a great canvas to add some features.  Thx for pointing me in that direction...

Indeed. You'r welcome.

[Circle 0]

Big thanks for posting this. Really helps with getting our patches under control.

Just one small change from an issue I noticed,

$Result = (

Should be

$Result += (

Had a server that returned only one result, so $Result changed from an array to the object that was returned and then failed the condition afterwards.

Cheers

[connectsys 0]

On 8/8/2019 at 9:26 PM, Dave C said:

If it helps anyone else I found that some servers had installed the Preview Update instead so had to add this line to both Scripts.
            $_.Title -like '*Preview of Monthly Quality*' -or 
 

To make anyones lives Easier ive attached a copy of the Script I used to populate the EDFs I created.

 

Last Successful Patch Date.xml 4.04 kB · 16 downloads

Hi Dave

Thanks a lot for your XML, I imported it and it worked well and I now have the EDFs populated. I tried to copy your monitor (apart from changing the timescale)

 

but I get the following error when I try to use it

Anyone know where I might have gone wrong?

[drinkxon 5]

On 9/17/2019 at 10:46 AM, connectsys said:

 

Anyone know where I might have gone wrong?

The monitor is referencing table v_extradatacomputers which is dynamically recreated every x minutes. Give it a few for your new EDF fields to appear.

Edited September 26, 2019 by drinkxon

[NickBurns 0]

@Teeevv I have tested these powershell scripts on multiple machines. Most of the time I am receiving "No CU Patching Information Available". Have you experienced this?

I figured this out. If a patch is hung in the in progress stat (Result Code 1) it will return No CU Patching Information available.

Edited November 7, 2019 by NickBurns

[BGags 5]

A bunch of us have found it true that Automate's own mechanisms and reporting don't tell the true tale of everything that's going on with patching. In determining how to fix it, I've found that it's not only important to determine the true patch state of our systems, but also to figure out what Automate itself is (and isn't!) doing. To that end, all I've got so far is a query inspired by Gavsto's, with a couple extra columns including most recent patch job per system, most recent patch job with an installation attempt, most recent patching error with date of error, and (perhaps most importantly), the date of the most recently discovered patch in a system's inventory. If a system is reporting 0 patches missing, but the most recent patch it knows about is 90 days old, something's wrong with its inventory collection and you gotta fix that.

One thing that a lot people miss about Automate's inventory collection mechanisms: Inventory operations run by the agent on the agent's assigned inventory schedule will NOT return a success/fail result code anywhere in the database, since they're not Commands issued to the agent by the server. You can schedule a Send Hotfix inventory every two hours, and the Computer screen will happily tell you that the agent ran a Resend Patches inventory two hours ago, but it won't tell you that the inventory failed silently on the agent and your data is bunk.

Anyway, here, have some SQL:

SELECT Clients.`Name` `Client`
, computers.`Name` `System`
, computers.`ComputerID` `ComputerID`
, computers.`OS`
, computers.`Version`
, CAST(IF(ISNULL(PatchesMissing.MissingCount),'0',PatchesMissing.MissingCount) AS UNSIGNED) `Patches Missing`
, computers.`LastContact` `Last Contact Date`
, IF(ISNULL(NewestPatches.`NewestPatchAvailable`),'No Inventory',NewestPatches.`NewestPatchAvailable`) `Newest Patch In Inventory`
, IF(ISNULL(PatchTries.`PatchTryDate`),'Never',PatchTries.`PatchTryDate`) `Last Patch Attempt Date`
, IF(ISNULL(PatchJorbs.`JorbDate`), 'Never', PatchJorbs.`JorbDate`) `Last Patch Job Date On Record`
, IF(ISNULL(PatchErrors.`ErrorDate`), 'N/A', PatchErrors.`ErrorDate`) `Most Recent Error Date`
, IF(ISNULL(PatchErrors.`ErrorCode`), 'N/A', PatchErrors.`ErrorCode`) `Most Recent Patching Error`

FROM computers
LEFT JOIN v_extradatacomputers AS vcomp ON vcomp.`computerid`=computers.`ComputerID`
LEFT JOIN clients ON computers.`ClientID`=clients.`ClientID`
LEFT JOIN 

-- Derived table full of missing patch counts

	(SELECT hotfix.`ComputerID`, 
	COUNT(hotfix.`HotFixID`) AS `MissingCount`
	FROM hotfix
	WHERE hotfix.`Approved`='2'
	AND hotfix.`Installed`='0'
	GROUP BY hotfix.`ComputerID`)
	AS `PatchesMissing` ON Computers.`ComputerID`=PatchesMissing.ComputerID

-- Derived table full of most recent patch jobs that actually tried to patch something

LEFT JOIN
	(SELECT pjp.`ComputerID` `ComputerID`
	, MAX(pj.`FinishDate`) `PatchTryDate`
	FROM patchjobpatches pjp
	LEFT JOIN patchjobs pj ON pj.`ComputerID`=pjp.`ComputerID` AND pjp.`PatchJobGuid`=pj.`PatchJobGuid`
	GROUP BY pjp.`ComputerID`) AS `PatchTries` ON computers.`ComputerID`=PatchTries.`ComputerID`
	
-- Derived table full of most recent supposedly-executed patch jobs listed per computer
-- (patch jobs with nothing to do CAN exist if there are no patches to install, but if there
-- are missing patches on a system, this column should NOT be newer than the column containing
-- patch jobs that executed a patch command)

LEFT JOIN
	(SELECT pj.`ComputerID` `ComputerID`
	, MAX(pj.`FinishDate`) `JorbDate`
	FROM patchjobs pj
	GROUP BY pj.`ComputerID`) AS `PatchJorbs` ON computers.`ComputerID`=PatchJorbs.`ComputerID`

-- Derived table with dates of the most recent hotfix listed in each computer's inventory;
-- If the most recent hotfix that a system knows about is more than 30 days old, there's
-- a freaking problem.
LEFT JOIN
	(SELECT DISTINCT computers.`ComputerID` `ComputerID`
	, MAX(hfd.`Date_Added`) `NewestPatchAvailable`
	FROM computers
	LEFT JOIN hotfix ON computers.`ComputerID`=hotfix.`ComputerID`
	LEFT JOIN hotfixdata hfd ON hotfix.`HotFixID`=hfd.`HotFixID` AND hotfix.`OS`=hfd.`OS`
	GROUP BY computers.`ComputerID`) AS `NewestPatches` ON computers.`ComputerID`=NewestPatches.`ComputerID`

-- Derived table full of most recent patch errors per system; this one's complicated!
LEFT JOIN
	(SELECT JobErrors.computerid `ComputerID`
	, MAX(JobErrors.JobFinished) `ErrorDate`
	, JobErrors.ErrorCode `ErrorCode`
	 FROM 
		(SELECT 
		pj.computerid AS ComputerID,
		pj.FinishDate AS JobFinished, 

		CASE WHEN (pjp.`HResult` <> 0) THEN CONCAT('0x',RIGHT(HEX(pjp.`HResult`),8) ) 
			WHEN (pjld.LogDetails LIKE '%Exception from HRESULT:%') THEN RIGHT(LEFT(pjld.LogDetails,(INSTR(pjld.LogDetails,'0x') + 9) ),10)
			END AS ErrorCode

		FROM patchjobs AS pj
		LEFT JOIN patchjoblogdetails pjld ON pj.ComputerID=pjld.ComputerID AND pj.PatchJobGuid=pjld.PatchJobGuid
		LEFT JOIN patchjobpatches pjp ON pj.ComputerID=pjp.ComputerID AND pj.PatchJobGuid=pjp.PatchJobGuid

	) AS JobErrors

	WHERE (JobErrors.ErrorCode IS NULL) <> '1'
	GROUP BY JobErrors.computerid) AS `PatchErrors` ON computers.`ComputerID`=PatchErrors.ComputerID

-- Remove computers that haven't checked in over the last six months
WHERE computers.`LastContact` > DATE_SUB(NOW(), INTERVAL 6 MONTH)

-- Make sure this system has an active, applied Windows Update policy (this will filter out Non-Windows 
-- systems, systems with a local override, and include our BDR units at non-patching clients).
AND computers.`ComputerID` IN
	(SELECT DISTINCT cpp.`ComputerID`
	FROM computerpatchpolicies cpp
	WHERE cpp.`InstallPolicy` IN 
		(SELECT DISTINCT id FROM installsoftwarepolicies isp WHERE isp.`UpdateMode` <> '3')
	)

-- Filter for "problematic" criteria
AND 
	-- Include systems whose newest patch in its patch inventory is over 30 days old
	-- (suggests something's wrong with its inventory collection)
	((CAST(NewestPatches.`NewestPatchAvailable` AS DATETIME) < DATE_SUB(NOW(), INTERVAL 30 DAY) )
	OR
	-- Include systems that haven't attempted a patch installation within the last 30 days
	(DATE(PatchTries.`PatchTryDate`) < DATE_SUB(NOW(), INTERVAL 30 DAY) )
	)
ORDER BY clients.`Name`

 

Edited November 11, 2019 by BGags
Clarity of prose and clarification of SQL origin

[NickBurns 0]

@BGags Thanks! Are you running this as RAWSQL in an Internal Monitor and firing off a ticket?

[BGags 5]

@NickBurnsI'm doing something a little different than that directly; I'm taking the results and creating per-computer alerts that detail the type of problem detected, then those either get auto-fix scripts thrown at them or a single ticket created per-client for manual attention.

[axiomcrs2 0]

@BGags I really like that sql.  It would be nice to have this run on a weekly basis and have the results emailed.  Would you be able to suggest changes to that effect?

Edited November 26, 2019 by axiomcrs2

[SteveYates 16]

If it helps anyone we make use of a few monitors.  One to check for PCs that don't have patches more recent than "n" (60 in this case) days:

computers.lastcontact > DATE_ADD(NOW(),INTERVAL -1 DAY) and computers.os like '%microsoft%' AND
computers.OS NOT LIKE '%Server 2003%' AND
(
SELECT COUNT(hotfix.HotFixID) FROM hotfix 
INNER JOIN hotfixdata ON hotfix.HotFixID=hotfixdata.HotFixID
WHERE 
hotfix.computerid=computers.computerid AND
hotfixdata.Date_Added > DATE_ADD(NOW(), INTERVAL -60 DAY)
) = 0

Another for empty patching tabs:

computers.lastcontact > DATE_ADD(NOW(),INTERVAL -5 DAY) 
and computers.os like '%microsoft%' 
and (SELECT COUNT(hotfixid)<=0 FROM hotfix WHERE hotfix.computerid=computers.computerid)

(note recently Win10 PCs seem to get into that state but restarting the CWA agent and resending patches fixes it)

A third for PCs missing approved patches...this one was much more useful back when Win7 had 10-15 patches per month...now there are only a handful per month.  We run this monthly a week or two after we release patches:

computers.lastcontact > DATE_ADD(NOW(),INTERVAL -5 DAY) and computers.os like '%microsoft%' and 
(
SELECT COUNT(hotfixid) FROM hotfix WHERE hotfix.computerid=computers.computerid AND approved=2 AND installed=0
) > 2

All just have a generic "computers.ComputerID NotEquals 0" type of check condition so the logic is in the conditions.

[NickBurns 0]

On 11/12/2019 at 1:04 PM, BGags said:

@NickBurnsI'm doing something a little different than that directly; I'm taking the results and creating per-computer alerts that detail the type of problem detected, then those either get auto-fix scripts thrown at them or a single ticket created per-client for manual attention.

@BGags Sounds interesting. Would this work as a RAWSQL internal monitor?

[harryboyne 2]

We've been trying the PowerShell script and it's been working well for us.

 

The only adjustment we've need to make is that, if there isn't a patch, it reports the date the OS was installed:

Return ([WMI]'').ConvertToDateTime((Get-WmiObject Win32_OperatingSystem).InstallDate).ToString('yyyyMMdd-HHmmss')

This way, any feature updates don't cause the script to report incorrect information.

 

However, we've bumped into another issue. A Spanish PC was reporting that it was only installing a very old update.

That's because the updates have names like:

• Actualización de características a Windows 10, versión 1909
• 2019-11 Actualización acumulativa para Windows 10 Version 1903

Without adding every language under the sun into this script, just wondering if anyone has come across this yet?

H

[timbo83251 5]

What is the best way to exclude operating systems that are no longer receiving updates due to end-of-support (e.g. Windows 7, Server 2008)? There's no point in alerting on these...  I'm thinking just adding additional WHERE to the monitor condition like "AND computers.os NOT LIKE '%server 2008% and computers.os NOT LIKE '%windows 7%'".  Unless there is some other table out there that classifies OS versions as supported or not... or would using the version # be better?

[SteveYates 16]

We had already done that with 2003 like I noted above, as sadly we still had a couple, so yes just used NOT LIKE '%server 200%' and similar for XP through 7.

Since I'm posting, re: my comment above about empty patching tabs, we've seen multiple problems since last fall, basically either:

1) patching tab is completely empty
2) patching tab only has old patches
3) patching tab only has some patches (often missing the non-Windows patches)

In all cases restarting the agent and resending patches detects the full list.  We've had a ticket open for several months, since in late December almost all our Win10 PCs got into this state one time or another.  It has been more sporadic before and after that.  And yes this is even for patches CWA had previously installed.

[timbo83251 5]

@SteveYatesThanks.   Automate patching and reporting needs another overhaul especially with WIn10 in mind.

[Brant 0]

I find a lot of what has been shared here to be very helpful and I'm glad to see that I'm not the only one wanting to rip my hair out over patching. I'm very interested (and this may not be the best thread to post it in) in what people are doing when it comes to reporting of patching. I personally feel the Patch Compliance report is garbage and am getting ready to recommend we take it out of circulation to our customers. So what is everyone else doing when it comes to reporting patch status etc. to their customers?