Jump to content
PaulTM

Internal Monitor to see devices that haven't received a Cumulative Update (or equivalent) recently (45 days)

Recommended Posts

Posted (edited)

So, a little background here:

The out-of-the-box "No Updates" relies on hoodoo, and witchcraft (not really, it just uses a generic date, which is updated by too many things). I wanted to see a bird's eye view on if patching is happening (a dipstick if you will), and I figured that the easiest way to do this is to look for devices that have checked in recently, and patched recently. Here's what this monitor is looking for.

1.) Checked in recently: This is looking for devices that have checked in within the last 45 days. This number is pretty generous, but is intended to get devices that are somewhat regularly used, and not a "Hot Spare" sitting in the closet for months at a time.

2.) Received a CU or Equivalent patch recently: Since MS has moved to the "One Patch To Rule Them All" method, everything's in a rollup of some sort (mostly). If you're getting those updates, you're pretty well set. Each month's cumulative update receives the Security and Stability/Functionality updates for every release prior. Since some folks like to make sure things are secure, but reduce probability of breaking things, they've released the "Security Only" and "Preview of Quality Update" channel as well. For our purposes, the Cumulative Update is the same as both the Security Only and the previous month's Preview of Quality Update.  

Now the juicy bits (the query) that I am currently using to find these things, or rather find devices that DON'T have both of the above criteria met:

 

SET @patchDay = 
   (
      IF( NOW() >= 
		(
			SELECT CONCAT(YEAR(CURRENT_DATE), '-',IF(MONTH(CURRENT_DATE) < 10, CONCAT(0,MONTH(CURRENT_DATE)), MONTH(CURRENT_DATE)), '-',IF(n < 10, CONCAT(0,n), n), ' 00:00:00') AS 'patchTuesday'
			FROM 
			(
				SELECT @rownum := @rownum + 1 AS 'n'
				FROM computers, (SELECT @rownum := 7) AS r
				LIMIT 7
			) AS numbers
         WHERE
            DAYNAME( CONCAT(YEAR(CURRENT_DATE), '-', IF(MONTH(CURRENT_DATE) < 10, CONCAT(0, MONTH(CURRENT_DATE)), MONTH(CURRENT_DATE)), '-', IF(n < 10, CONCAT(0, n), n), ' 00:00:00')) = 'Tuesday' 
      )
, 0, 1 ) 
   )
;
SET
   @LM = 
   (
      SELECT
         IF(@patchDay = 0, 1, 2)
   )
;
SET
   @TM = 
   (
      SELECT
         IF(@patchDay = 0, 0, 1)
   )
;
SELECT DISTINCT
   c.computerid AS testvalue,
   c.name AS identityfield,
   c.computerid AS computerid,
   acd.noalerts,
   acd.uptimestart,
   acd.uptimeend 
FROM
   computers c 
   LEFT JOIN
      agentcomputerdata acd 
      ON (c.computerid = acd.computerid) 
   JOIN
      extrafielddata 
      ON extrafielddata.id = c.computerid 
WHERE
   extrafieldid LIKE '224' 
   AND extrafielddata.VALUE LIKE '1' 
   AND c.lastcontact > DATE_ADD(NOW(), INTERVAL - 45 DAY) 
   AND c.locationid NOT IN 
   (
      '1',
      '2',
      '3'
   )
   AND c.computerid NOT IN 
   (
      SELECT
         h.computerid 
      FROM
         (
            SELECT
               PHF.computerid,
               kbid,
               title,
               PHF.operation,
               PHF.resultcode,
               PHF.actiondate 
            FROM
               (
                  SELECT
                     * 
                  FROM
                     patchhistory 
                  WHERE
                     actiondate > DATE_ADD(NOW(), INTERVAL - 45 DAY) 
               )
               AS PHF 
               JOIN
                  hotfixdata 
                  ON PHF.updateid = hotfixdata.hotfixid 
            WHERE
               PHF.`UpdateID` IN 
               (
                  SELECT
                     hotfixid 
                  FROM
                     hotfixdata 
                  WHERE
                     (
                        title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @LM MONTH), '%Y-%m'), '%'), '%Cumulative update for windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @LM MONTH), '%Y-%m'), '%'), '%Security Only Quality Update for Windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @LM MONTH), '%Y-%m'), '%'), '%security monthly quality rollup for windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @LM MONTH), '%Y-%m'), '%'), '%Preview of Monthly Quality Rollup for Windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @TM MONTH), '%Y-%m'), '%'), '%Cumulative update for windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @TM MONTH), '%Y-%m'), '%'), '%Security Only Quality Update for Windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @TM MONTH), '%Y-%m'), '%'), '%security monthly quality rollup for windows%') 
                        OR title LIKE CONCAT(CONCAT('%', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL - @TM MONTH), '%Y-%m'), '%'), '%Preview of Monthly Quality Rollup for Windows%') 
                     )
               )
               AND resultcode IN 
               (
                  1,
                  2
               )
            GROUP BY
               PHF.computerid 
         )
         AS h 
   )
   #You can ignore stuff below here for running in SQLyog.
   AND c.computerid NOT IN 
   (
      SELECT
         computerid 
      FROM
         agentignore 
      WHERE
         agentid = xxxxx 
   )

Now, you're going to want to modify the intervals, and run times to suit your needs. If you run this on the last day of the month, you'll get a lot fewer results than the first of the month (for obvious reasons). 
 

I have this set to "Default - Raise Alert" so I can see at-a-glance what devices need some love. You could easily make a report with this (if you're so inclined). Or you could make improvements, and let me know about them!

I'm new to MySQL, and have only really be using it off and on for a couple months, so I'm what you'd call a COMPLETE AND UTTER BEGINNER. I'm not claiming to be competition for Gavsto, or anyone else. I just built a thing that's a useful tool for my purposes and figured I'd share in case it's helpful to you too.

Future improvements are going to include (hopefully):

1.) Checking the current day of the month, and checking the months accordingly. Probably a good idea to use the previous two months if today's day of month is < ~10. I Know something needs to be done here, just not sure exactly the best approach. It'd be neat to have it account for "Patch Tuesday"  Figured this out with the help of someone. See below. LET ME KNOW if there's something wrong with this. Again, I'm a beginner and this is a spare-time project for me.

2.) Returning the last CU/Equivalent patch installed. Not sure exactly how to go about this, but it'd be handy info to have. The problem lies in the fact that we're using an "or" statement, so it's possible to grab last month's update when this month's has been installed. Again, I'm really new to MySQL so I have to figure out first what I want to accomplish, then figure out how to do it without too much of a database hit. I'm guessing I'll use something similar to the PHF (Patch History: Filtered, in case you were wondering) table again and look for the highest date matching the pattern, but I don't want to hit too many times with the compares. It might be worth filtering the data going into the PHF table using the text filters. Gonna take some playing around with the data, and I'm kind of out of time at the moment. Any recommendations are welcome. 

Edit: Added some checking in to compare to patch Tuesday. If today is earlier than the PT for this month, it checks for last month's CU, and the previous month. If today is greater than PT, it'll check this month and last month. 45 days is a pretty generous window for devices not being patched. For the last line, the "xxxxx" will need to be replaced with the agent ID of the monitor you create, which you can get by looking at the title bar of the thing. 

Edited by PaulTM
Updated with new information so the monitor can run daily instead of once a month.

Share this post


Link to post
Share on other sites

Asking for a Friend...

I've been looking at this and I know how to go in and create a monitor and select the fields, etc however I suspect that there is a much easier way to do this by actually using the SQL code above then trying to go through the dropdowns and select all the right options...

Share this post


Link to post
Share on other sites

Mark, 

You want to use the RAWSQL method for creating the monitor. I've got mine set to run daily, which I don't recommend doing if you're going to make tickets for this, as you'll get a giant swarm of tickets in the beginning of the month until I cope with the timing issue. Here's how you would set it up though:

image.thumb.png.3f761cb2fa203c84d5f19fcf4d5b196a.png

 

Note the last line with 428163. That agent ID has to match the actual ID of the monitor (Which you can see in the title bar of the monitor) so you'll need to create the monitor then put that value in. Also, I've got mine limited to an EDF that indicates the device's patching is managed by us, so there's a bit of a difference between what I'm using and what's in the above post. Don't worry though, it doesn't affect its usefulness. 

Share this post


Link to post
Share on other sites

thanks for that...I have just started getting into automate so I wasn't aware of the RAWSQL monitor (but I've seen lots of people mention the word) :)

Share this post


Link to post
Share on other sites

Hi Paul

 

Thanks for this. I've tried to use the code in your first post but it doesn't like it. I suspect the problem is around

 

 AND c.locationid NOT IN ( <Update With Pertinent Info For Your Environment> ) 
       AND c.computerid NOT IN (SELECT h.computerid 

but I'm not sure (I'm not much of an SQL expert either). Do you have a clean version if possible please that will work out as is? I can add stuff to it if I need it but I'm not sure if I'm removing the right bits as unsure what the pertinent info bit is referring to. I have updated the agentID but that didn't help. Unfortunately all I get is "Error in your SQL statement".

 

Share this post


Link to post
Share on other sites
Posted (edited)

The part in the angle brackets is intended to be a variable. Basically, if you have a "New Computers" location (let's say it's #1), you would put in the location ID there without the brackets. Say you also have a client who manages their own stuff with WSUS, so your reporting is bad in there, and their location number is 513. In this case, since you don't want to get either of those locations included in the check, you would have the line be:

AND c.locationid NOT IN ( '1', '513' ) 
Edited by PaulTM
The numbers didn't show up for some reason. NOT IN ( '1', '513')

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