Jump to content

SQL to clean up patching tables

Recommended Posts

Got this little snippet at one time from CWSupport, maybe want to run this periodically to clean things up :)

/*Patching Cleanups*/

/*Eliminate extra policy approvals from system if the policy is gone*/
DELETE FROM `patchapprovalsettings` WHERE ApprovalPolicyID NOT IN (SELECT ApprovalPolicyID FROM `patchgroupapprovalpolicies`);

/*Clean out Hotfix Table if the computer no longer exist*/
DELETE FROM `hotfix` WHERE computerid NOT IN (SELECT computerid FROM computers);

/*S&H patch numbers that do not match the agents
  Issue:  If a missing patch is superseeded before S&H can see it has been installed, then it becomes an orphened missing patch.
  To fix, removed the orphaned patches from the plugin_sap table */
/*[MetaMSP] I have commented this block out because the schema for this plugin's tables have changed since this snippet was obtained.
  Thanks @QComer for pointing out the error!
DELETE FROM `plugin_sap_snapshotpatchstats` WHERE HotfixID NOT IN(SELECT hotfixid FROM hotfix WHERE ComputerID = hotfix.ComputerID);
DELETE FROM `plugin_sap_snapshotpatchstats` WHERE InstalledPatch = 0 AND HotfixID NOT IN(SELECT hotfixid FROM hotfix WHERE ComputerID = hotfix.ComputerID AND Installed = '1'); */

/*Clean out the old patches that do not exist anymore.  But leave those with Ignore/Deny set just in case*/
DELETE FROM `patchapprovalsettings` WHERE approval NOT IN(1,4) AND HotfixID NOT IN (SELECT hotfixid FROM `hotfix`);
DELETE FROM `hotfixdata` WHERE hotfixid NOT IN (SELECT HotFixID FROM `patchapprovalsettings`);


Edited by MetaMSP
commenting out obsolete query block
  • Thanks 3
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.

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