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