Jump to content
Teeevv

Monitoring Windows Updates Health

Recommended Posts

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.

image.png.f474594453c4f895442024ce8f75338c.png

 

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.

 

 

  • Like 4

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


Link to post
Share on other sites

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';

 

Share this post


Link to post
Share on other sites
Posted (edited)

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 by Dave C

Share this post


Link to post
Share on other sites
Posted (edited)
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.

image.png.f474594453c4f895442024ce8f75338c.png

 

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 by Dave C

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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)

Monitor.thumb.JPG.a050fe7bc21f302badc6335dd25f164a.JPG

 

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

Error.JPG.70456343ef6bd850f7e58bf6c01224a6.JPG

Anyone know where I might have gone wrong?

Share this post


Link to post
Share on other sites
On 9/17/2019 at 10:46 AM, connectsys said:

 

Error.JPG.70456343ef6bd850f7e58bf6c01224a6.JPG

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 by drinkxon

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by BGags
Clarity of prose and clarification of SQL origin
  • Thanks 1

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...