/build/static/layout/Breadcrumb_cap_w.png

K1000 Reports - Listing Machines Not in a Patch Schedule

K1000 Reports - Listing Machines Not in a Patch Schedule
_________________________________________________________

I wrote this SQL query to help with a recent question and thought it might be useful for others who may not have seen it:

http://www.itninja.com/question/how-to-tell-if-a-machine-isn-t-in-a-patch-task

This SQL Report will list any machines that are not members of a machine patch label (i.e. not in a patch schedule - working under the assumption that patch schedules are targeting machine patch labels), by using the REGEX statement to target the machine patch label names.  As a point of reference, my machine patch labels all use a naming convention of "patch (location - OS)" (ex: patch (roaming - XPsp3), patch (stationary - 7sp1x64), etc), so you'll want to tweak the target in the next to the last line from "patch" to whatever you are using.

For a full run-down of my patching setup, please see this article:

www.itninja.com/blog/view/k1000-patching-setup-tips-things-i-have-learned-ldap-smart-labels-sql-reports
_________________________________________________________

*Title*
Machines Not in a Patch Schedule

*Category*
Patching (Custom)

*Description*
Lists all machines not assigned to a patch schedule.

*SQL Select Statement*
SELECT M.NAME
FROM MACHINE M
WHERE M.NAME NOT IN
(SELECT M.NAME
FROM MACHINE M
JOIN MACHINE_LABEL_JT ML ON (ML.MACHINE_ID = M.ID)
JOIN LABEL L ON (L.ID = ML.LABEL_ID)
WHERE L.NAME rlike 'patch')
ORDER BY M.NAME
_________________________________________________________

Hope that helps!

John


Comments

  • Not bad, it's very similar to what I do but the report would be more useful if it didn't show machines that hadn't sync in awhile, like 30 days. That way machines that are dead don't show up as not in a patch group because they didn't sync in the appliance. - ms01ak 11 years ago
  • Not a problem, here ya go. Just change the number from 30 to whatever suits your needs.

    John
    _________________________

    SELECT M.NAME
    FROM MACHINE M
    WHERE M.NAME NOT IN
    (SELECT M.NAME
    FROM MACHINE M
    JOIN MACHINE_LABEL_JT ML ON (ML.MACHINE_ID = M.ID)
    JOIN LABEL L ON (L.ID = ML.LABEL_ID)
    WHERE L.NAME rlike 'patch')
    AND DATEDIFF (NOW(), M.LAST_SYNC) < 30
    ORDER BY M.NAME - jverbosk 11 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ