Jump to content
sstanley

Extremely flexible Patch management configuration

Recommended Posts

I decided we were going to update our patch policies to have a dropdown with all 24 hours available for patching, with a set patching window of 4 hours. I wanted to have every day available to select, as well as the end of the quarter. I also wanted to be able to toggle daytime patching with an EDF.
 
Some quick math tells us that this would be 8*24*2 groups, or 384 groups, and 384 searches for your EDFs. This would be extremely tedious, and would absolutely cripple any automate database. I also have EDFs at the computer and location level, and allow computer specific settings to overwrite location based settings.  So these searches are particularly complex. So, how to accomplish this?
To start with,  I named all groups <Day> <Hour> Day<+/-> and nested each of them under a specific Patch Manager group, and each day under it's own group
Custom Patch Manager > Fri > Fri 12 Day-
Custom Patch Manager > Fri > Fri 12 Day+
Custom Patch Manager > Fri > Fri 13 Day-
etc
 
This is still a ton of work, so to generate those groups I used the following powershell scriptlet
#This should be 1 higher than the current SELECT MAX(GroupID) from mastergroups;
$GroupID = 3033#Change these as needed for setting up your group names, they are referenced in the 3rd query.
$Day = 'End of Quarter'
$pm = ('-','+')#Adjust your loops as needed as well. I happened to have a need for a +/- in my group names here
$pm | Foreach-Object {
For ($hour = 0; $hour -lt 24; $hour++) {
#First statement uses the direct parent of whatever group you are creating, second statement has all parents in the chain for the FIND_IN_SET
@"
INSERT INTO MasterGroups (ParentID,Parents,NAME,Depth,fullname,Children,GroupType,`GUID`) 
  (SELECT GroupID,CONVERT(CONCAT(Parents,GroupID,',') USING latin1),
  'New Group',
  (SELECT `Depth` + 1 FROM `MasterGroups` WHERE `GroupId`=2552),
  CONVERT(CONCAT(Fullname,'.New Group') USING latin1),',',GroupType,UUID() FROM MasterGroups WHERE GroupID=2552);UPDATE MasterGroups 
SET Children=CONCAT(Children,'$($GroupID),')  
WHERE FIND_IN_SET(GroupID,'2382,1179,2384,2552');UPDATE MasterGroups 
SET NAME='$($Day) $($hour) Day$($_)',
Permissions=0,Notes='',Template=0,GroupType=0,MaintenanceID=0,AutoJoinScript=0,MASTER=0,NetworkJoin=0,NetworkJoinOptions=0,ContactJoin=0,ContactJoinOptions=0,Priority=5,Control=0,ControlID=0,MaintWindowApplied=NOW(),LimitToParent=0 
WHERE GroupID=$($GroupID);UPDATE mastergroups mg SET mg.fullname=f_GroupFullName(mg.GroupID) 
WHERE FIND_IN_SET(mg.groupid,'$($GroupID)');
"@ | out-file -append -filepath ".\GeneratedSQL.txt"$GroupID++}
}
 
You basically run this once for each day, using it's specific parent group . You can see here my end of quarter stuff since it's the last thing I did. This generates the SQL needed to create the groups
 
Once the groups are created, you do the following:
 
INSERT INTO patchgrouppolicies 
SELECT GroupId, `Name` AS GroupName, NULL AS Priority, 1 AS Membership, -1 AS InstallPolicy, -1 AS SoftwarePolicy, -1 AS RebootPolicy, -1 AS ComputerLevelOverride 
FROM mastergroups WHERE parentid IN (2552);
This links the groups to the patch manager. At this point we need to create actual update policies that match the settings you want
INSERT INTO installsoftwarepolicies
SELECT NULL AS Id, `Name`, 5 AS UpdateMode, 7 AS `Day`, TRIM(RIGHT(SUBSTRING_INDEX(`Name`,' ',4),2)) AS StartTime, 
14 AS Duration, 1 AS CustomAction, 1 AS Dates, 1170 AS MonthlyOccurrence, 0 AS LastDay, 16 AS Occurrence, 1 AS CustomDays, 
IF(`Name` LIKE '%+',68,64) AS `Options`, 0 AS Uptime, 0 AS CVSS, 0 AS PromptInterval, 0 AS RebootDeadline, '' AS SoftwareUpdateMessage, 
0 AS IsThirdParty, '' AS BeforeScript, '' AS AfterScript, 0 AS DaysAfter, 0 AS ServiceBranch, -1 AS FeatureUpdatesDelay, -1 AS QualityUpdatesDelay
FROM mastergroups WHERE parentid = 2552;
This accomplishes that. For the vast majority of groups the
TRIM(RIGHT(SUBSTRING_INDEX(`Name`,' ',4),2)) AS StartTime,
needs to be
TRIM(RIGHT(SUBSTRING_INDEX(`Name`,' ',2),2)) AS StartTime,
This is just based on the number of spaces in your group name. End of Quarter 22 has 3 spaces before the time, whereas Fri 22 has only 1, so you're taking the 4th, or the second value respectively.
Once this is done you need to link your newly created policies back to the groups using this query, and everything is set up and ready to go.
UPDATE patchgrouppolicies
JOIN installsoftwarepolicies ON patchgrouppolicies.`GroupName`=installsoftwarepolicies.`Name`
SET InstallPolicy = installsoftwarepolicies.`ID
Now we have to tackle the problem of not putting a million searches in your database. this is done with the following script:
image.thumb.png.b193bd885747796983c23cf4bf245765.png
 
You'll replace the groups in step 3 with your own groupIDs that you created. The 6th step in this script is as follows:
INSERT INTO subgroups (computerid,groupid) VALUES (%computerid%,(
SELECT groupid FROM mastergroups WHERE `name` =(
SELECT 
  CONCAT(IF(IFNULL(efd3.Value,'Default')='Default',IF(IFNULL(efd4.Value,'Fri')='Default','Fri',IFNULL(efd4.Value,'Fri')),efd3.Value), ' ', 
  IF(IFNULL(efd1.Value,0)=0,IF(IFNULL(efd2.Value,0)=0,1,IF(efd2.Value='Default',1,efd2.Value)),IF(efd1.Value='Default',IF(IFNULL(efd2.Value,0)=0,1,IF(efd2.Value='Default',1,efd2.Value)),efd1.Value)), ' ',
  IF(IFNULL(efd5.Value,0)=0,IF(IFNULL(efd6.Value,0)=0,'Day+','Day-'),IF(efd5.Value=1,'Day-','Day+'))) AS PatchGroup
FROM computers 
LEFT JOIN extrafielddata AS efd1 ON computers.computerid=efd1.`ID` AND efd1.`ExtraFieldID`=959
LEFT JOIN extrafielddata AS efd2 ON computers.locationid=efd2.`ID` AND efd2.`ExtraFieldID`=960
LEFT JOIN extrafielddata AS efd3 ON computers.computerid=efd3.`ID` AND efd3.`ExtraFieldID`=898
LEFT JOIN extrafielddata AS efd4 ON computers.locationid=efd4.`ID` AND efd4.`ExtraFieldID`=899
LEFT JOIN extrafielddata AS efd5 ON computers.computerid=efd5.`ID` AND efd5.`ExtraFieldID`=890
LEFT JOIN extrafielddata AS efd6 ON computers.locationid=efd6.`ID` AND efd6.`ExtraFieldID`=892
WHERE computers.computerid = %computerid%)))
You'll have to use your extrafield IDs. my evenly numbered ones are my location EDFs and my odd numbered ones are my computer based EDFs.
 
At this point you now have a flexible patch management system that lets you define a starting hour and day, as well as daytime or not for any location or computer, that doesn't rely on the built in search system. You just schedule your script to run daily spread your runs out over a period of time that makes sense for your agent count

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