Jump to content
PaulTM

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

Recommended Posts

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:

 

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  c.lastcontact > DATE_ADD(NOW(), INTERVAL - 45 DAY) 
       AND c.locationid NOT IN ( <Update With Pertinent Info For Your Environment> ) 
       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 - 1 MONTH),'%Y-%m'),'%'),'%Cumulative update for windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 1 MONTH),'%Y-%m'),'%'),'%Security Only Quality Update for Windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 1 MONTH),'%Y-%m'),'%'),'%security monthly quality rollup for windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 1 MONTH),'%Y-%m'),'%'),'%Preview of Monthly Quality Rollup for Windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 0 MONTH),'%Y-%m'),'%'),'%Cumulative update for windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 0 MONTH),'%Y-%m'),'%'),'%Security Only Quality Update for Windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 0 MONTH),'%Y-%m'),'%'),'%security monthly quality rollup for windows%')
                OR title LIKE CONCAT(CONCAT('%',DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL - 0 MONTH),'%Y-%m'),'%'),'%Preview of Monthly Quality Rollup for Windows%')
            )
        ) 
            AND resultcode IN ( 1, 2 ) 
            GROUP  BY PHF.computerid
    ) AS h 
) 
AND c.computerid NOT IN (SELECT computerid 
FROM   agentignore 
WHERE  agentid = <Update With Your Monitor ID And Remove Angle Brackets>) 

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"

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. 

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×