Jump to content

PaulTM

Members
  • Content Count

    4
  • Joined

  • Last visited

Community Reputation

0 Neutral

My Information

  • Location
    Bothell, WA
  • Agent Count
    > 6000 Agents
  1. 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' )
  2. 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: 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.
  3. 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.
  4. PaulTM

    Bitlocker Enabled

    Just go to the Dashboard and navigate to Config>Configurations>Role Definintions Now click on the Bitllocker Enabled one, and go to the "Detection" field. Add " C:" after "-status" such that it is now "-status c:" You'll now see the results from the C drives only, if that's what you're interested in. Or not. Maybe I'm wrong.
×