Jump to content
BGags

Improve Daytime Patching with a RAWSQL Monitor

Recommended Posts

Summary: I think the Automate Patch Manager's stock Daytime Patching (DTP) functions give up way too easily. So I wrote a RAWSQL monitor that you can use to drive patch delivery scripts during the day to systems missing patches.

The monitor is built to use stock Patch Manager features relating to Microsoft Update Policies, so it should be pretty universal. The configured criteria as written:

  • System is online
  • Windows OS
  • No servers
  • No reboot pending
  • Has an effective Microsoft Update Policy that has Daytime Patching enabled
  • Has more than 0 missing updates
  • Hasn't run a patch job that delivered updates in the past 24 hours
  • Not actively running a Patch Install command
  • Hasn't recently failed a Patch Install command

I'm running this monitor every five minutes, with an alert action set to a straight-up Install Missing Approved Patches Now script. I'll leave that step to you folks! I just rolled this out today, and patch delivery production has been... enthusiastic.

One thing I haven't done yet is trained it to avoid DTP during a system's regular overnight patch windows as governed by their Microsoft Update Policies. Right now, I'm handling that in the Alert Template, running it only from 7am to 11pm. I'd like to include something more dynamic and elegant. Watch this space!

If you're not familiar with RAWSQL monitors, I'd suggest reading Gavsto's excellent blog article on the subject. Remember, if your monitor isn't running your script properly, make sure that in the alert template you've actually enabled and checked off the days on which you want the alert template to run! 'Cause if you didn't, that would be foolish (hi)!

Feedback welcome! Enjoy, Geeks.

SELECT DISTINCT CAST(IFNULL(MissingP.numMissing,'0') AS UNSIGNED) AS TestValue
, CONCAT(computers.Name,':',computers.ComputerID) AS IdentityField
, computers.ComputerID AS ComputerID

, acd.NoAlerts
, acd.UpTimeStart
, acd.UpTimeEnd

FROM computers
LEFT JOIN agentcomputerdata AS acd ON computers.`ComputerID`=acd.`ComputerID`
LEFT JOIN commands ON computers.`ComputerID`=commands.`ComputerID`
LEFT JOIN clients ON computers.`ClientID`=Clients.`ClientID`
LEFT JOIN locations ON computers.`LocationID`=Locations.`LocationID`

LEFT JOIN 
-- Derived table full of missing patch counts
	(SELECT hotfix.`ComputerID`, 
	COUNT(hotfix.`HotFixID`) AS `numMissing`
	FROM hotfix
	WHERE hotfix.`Approved`='2'
	AND hotfix.`Installed`='0'
	GROUP BY hotfix.`ComputerID`)
	AS `MissingP` ON Computers.`ComputerID`=MissingP.ComputerID
	
WHERE DATE_SUB(NOW(), INTERVAL 5 MINUTE) < computers.`LastContact`
AND computers.`OS` LIKE '%Windows%'
AND computers.`OS` NOT LIKE '%Server%'
AND computers.`flags` & 1024 <> 1024

-- Make sure this system has an active, applied Windows Update policy with Daytime Patching enabled.
AND computers.`ComputerID` IN
	(SELECT DISTINCT cpp.`ComputerID`
	FROM computerpatchpolicies AS cpp
	LEFT JOIN installsoftwarepolicies AS isp ON cpp.`InstallPolicy`=isp.`ID`
	WHERE isp.`Options` & 4 = 4)

-- Include systems with missing patches
AND (CAST(IFNULL(MissingP.numMissing,'0') AS UNSIGNED)) > 0

-- Exclude systems that have run a patch install job within the past 24 hours
AND computers.`ComputerID` NOT IN
	(SELECT DISTINCT pjp.`ComputerID`
	FROM patchjobpatches AS pjp
	LEFT JOIN patchjobs AS pj ON pjp.`PatchJobGuid`=pj.`PatchJobGuid` AND pjp.`ComputerID`=pj.`ComputerID`
	WHERE pj.`FinishDate` > DATE_SUB(NOW(), INTERVAL 24 HOUR) )

-- Make sure the system isn't already running an Install Patch command, or hasn't failed one recently
AND computers.`ComputerID` NOT IN
	(SELECT DISTINCT computerid FROM commands WHERE commands.`Command`='100' AND commands.`Status` IN ('2','4'))

ORDER BY (CAST(IFNULL(MissingP.numMissing,'0') AS UNSIGNED)) DESC
LIMIT 10

 

Edited by BGags

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