Teeevv 4 Posted June 23 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 The report showed the last Cumulative Update and when the installation was attempted, whether it was successful or not 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. 4 Quote Share this post Link to post Share on other sites
myordy 1 Posted July 25 Thank you for the great info! I started implementing this today and am excited about the results so far. Quote Share this post Link to post Share on other sites
Dayrak 0 Posted August 5 (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 August 5 by Dayrak Quote Share this post Link to post Share on other sites
HickBoy 4 Posted August 5 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'; Quote Share this post Link to post Share on other sites
Dave C 0 Posted August 7 (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 August 8 by Dave C Quote Share this post Link to post Share on other sites
Dave C 0 Posted August 8 (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 The report showed the last Cumulative Update and when the installation was attempted, whether it was successful or not 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 by Dave C Quote Share this post Link to post Share on other sites
HickBoy 4 Posted August 12 Anyone Created a decent DataView or Report template they want to share that pulls from their EDF's? Quote Share this post Link to post Share on other sites
Dayrak 0 Posted August 12 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/ Quote Share this post Link to post Share on other sites
HickBoy 4 Posted August 12 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... Quote Share this post Link to post Share on other sites
Dayrak 0 Posted August 12 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. Quote Share this post Link to post Share on other sites
Circle 0 Posted August 22 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 Quote Share this post Link to post Share on other sites
connectsys 0 Posted September 17 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? Quote Share this post Link to post Share on other sites
drinkxon 4 Posted September 26 (edited) 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 by drinkxon Quote Share this post Link to post Share on other sites
NickBurns 0 Posted November 5 (edited) @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 by NickBurns Quote Share this post Link to post Share on other sites
BGags 5 Posted November 11 (edited) 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 by BGags Clarity of prose and clarification of SQL origin 1 Quote Share this post Link to post Share on other sites
NickBurns 0 Posted November 12 @BGags Thanks! Are you running this as RAWSQL in an Internal Monitor and firing off a ticket? Quote Share this post Link to post Share on other sites
BGags 5 Posted November 12 @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. Quote Share this post Link to post Share on other sites
axiomcrs2 0 Posted November 26 (edited) @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 by axiomcrs2 Quote Share this post Link to post Share on other sites
SteveYates 3 Posted December 3 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. Quote Share this post Link to post Share on other sites
NickBurns 0 Posted Tuesday at 02:55 PM 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? Quote Share this post Link to post Share on other sites
harryboyne 2 Posted Wednesday at 09:28 PM 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 Quote Share this post Link to post Share on other sites