report on a specific patch label
Hi guys,
I'm trying to generate a machine report for specific machines that are missing a patch, in this case java for Mac OS X. I want to show which machines have certain Java patches, I've added the machines to a report but I really want to show each individual machine instead of grand totals.
The report would look like:
Machine, user, patch
Here's my sql so far:
SELECT RELEASEDATE,DESCR,IDENTIFIER,IS_APP,GROUP_CONCAT(DISTINCT O.DESCRIPTION SEPARATOR '\n') AS OS_NAMES,IMPACTID,TITLE,VENDOR,(IF(V.ATTRVALUE <> '', V.ATTRVALUE, 'Not Available')) AS VENDOR_RATING,GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,SUM((P.DEPLOY_ATTEMPT_COUNT >= 3 and P.STATUS != 'PATCHED') or P.STATUS = 'FAIL' or P.DEPLOY_STATUS = 'FAIL') AS ERROR,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,SUM(P.STATUS='PATCHED') AS PATCHED FROM KBSYS.PATCHLINK_PATCH LEFT JOIN KBSYS.PATCHLINK_LST_PATCH_JT ON KBSYS.PATCHLINK_LST_PATCH_JT.PATCHUID=KBSYS.PATCHLINK_PATCH.UID LEFT JOIN KBSYS.PATCHLINK_LST O ON O.ID=KBSYS.PATCHLINK_LST_PATCH_JT.LST_ID LEFT JOIN KBSYS.PATCHLINK_VENDORATTRIBUTE V ON V.PATCHUID=PATCHLINK_PATCH.UID AND V.ATTR = 'MaximumSeverityRating' LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID) LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID) LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID WHERE (LABEL.NAME = 'java critical patches april 5 2012 mac osx ') GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY RELEASEDATE
I'm trying to generate a machine report for specific machines that are missing a patch, in this case java for Mac OS X. I want to show which machines have certain Java patches, I've added the machines to a report but I really want to show each individual machine instead of grand totals.
The report would look like:
Machine, user, patch
Here's my sql so far:
SELECT RELEASEDATE,DESCR,IDENTIFIER,IS_APP,GROUP_CONCAT(DISTINCT O.DESCRIPTION SEPARATOR '\n') AS OS_NAMES,IMPACTID,TITLE,VENDOR,(IF(V.ATTRVALUE <> '', V.ATTRVALUE, 'Not Available')) AS VENDOR_RATING,GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,SUM((P.DEPLOY_ATTEMPT_COUNT >= 3 and P.STATUS != 'PATCHED') or P.STATUS = 'FAIL' or P.DEPLOY_STATUS = 'FAIL') AS ERROR,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,SUM(P.STATUS='PATCHED') AS PATCHED FROM KBSYS.PATCHLINK_PATCH LEFT JOIN KBSYS.PATCHLINK_LST_PATCH_JT ON KBSYS.PATCHLINK_LST_PATCH_JT.PATCHUID=KBSYS.PATCHLINK_PATCH.UID LEFT JOIN KBSYS.PATCHLINK_LST O ON O.ID=KBSYS.PATCHLINK_LST_PATCH_JT.LST_ID LEFT JOIN KBSYS.PATCHLINK_VENDORATTRIBUTE V ON V.PATCHUID=PATCHLINK_PATCH.UID AND V.ATTR = 'MaximumSeverityRating' LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID) LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID) LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID WHERE (LABEL.NAME = 'java critical patches april 5 2012 mac osx ') GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY RELEASEDATE
0 Comments
[ + ] Show comments
Answers (3)
Answer Summary:
SELECT DISTINCT CASE WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE. NAME WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN Concat(MACHINE. NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) END AS MACHINE_NAME, P.TITLE AS DISPLAY_NAME, P.IDENTIFIER AS KB_ARTICLE FROM PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P, PATCHLINK_PATCH_LABEL_JT PL, LABEL L WHERE MACHINE.ID = S.MACHINE_ID AND S.PATCHUID = P.UID AND S. STATUS != 'PATCHED' AND PL.PATCHUID = P.UID AND PL.LABEL_ID = L.ID AND L.NAME = 'MICROSOFT PATCHES' -- CHANGE YOUR PATCH LABEL HERE ORDER BY MACHINE_NAME, P.TITLE
SELECT DISTINCT CASE WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE. NAME WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN Concat(MACHINE. NAME, "\\", MACHINE.SYSTEM_DESCRIPTION) END AS MACHINE_NAME, P.TITLE AS DISPLAY_NAME, P.IDENTIFIER AS KB_ARTICLE FROM PATCHLINK_MACHINE_STATUS S, MACHINE, KBSYS.PATCHLINK_PATCH P, PATCHLINK_PATCH_LABEL_JT PL, LABEL L WHERE MACHINE.ID = S.MACHINE_ID AND S.PATCHUID = P.UID AND S. STATUS != 'PATCHED' AND PL.PATCHUID = P.UID AND PL.LABEL_ID = L.ID AND L.NAME = 'MICROSOFT PATCHES' -- CHANGE YOUR PATCH LABEL HERE ORDER BY MACHINE_NAME, P.TITLE
Please log in to answer
Posted by:
dchristian
12 years ago
Here's a report that you can use as a template. Change to match your patch label:
SELECT DISTINCT CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE. NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN Concat(MACHINE. NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME,
P.IDENTIFIER AS KB_ARTICLE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL L
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND S. STATUS != 'PATCHED'
AND PL.PATCHUID = P.UID
AND PL.LABEL_ID = L.ID
AND L.NAME = 'MICROSOFT PATCHES' -- CHANGE YOUR PATCH LABEL HERE
ORDER BY MACHINE_NAME,
P.TITLE
SELECT DISTINCT CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE. NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN Concat(MACHINE. NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME,
P.IDENTIFIER AS KB_ARTICLE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P,
PATCHLINK_PATCH_LABEL_JT PL,
LABEL L
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND S. STATUS != 'PATCHED'
AND PL.PATCHUID = P.UID
AND PL.LABEL_ID = L.ID
AND L.NAME = 'MICROSOFT PATCHES' -- CHANGE YOUR PATCH LABEL HERE
ORDER BY MACHINE_NAME,
P.TITLE
Posted by:
jverbosk
12 years ago
I'm reporting on patches via labels, examples toward the bottom of this post:
http://www.itninja.com/question/ldap-patching-sql-reports-using-all-three-for-efficient-managed-patching-and-other-cool-tric
Hope that helps!
John
http://www.itninja.com/question/ldap-patching-sql-reports-using-all-three-for-efficient-managed-patching-and-other-cool-tric
Hope that helps!
John
Posted by:
nshah
12 years ago
have you checked out the following link yet?
http://www.kace.com/support/resources/kb/article/Patch-Report-to-find-machines-that-are-missing
This patch report will look for what machines are missing a particular Software Patch.
The SQL for the report is:
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(M.USER_LOGGED,'\\','\\\\') as USER_LOGGED,
CS_DOMAIN
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
AND PP.TITLE like '%MS%'
ORDER BY PP.TITLE
Please NOTE: to replace the %MS% with a specific patch name in between the quotes.
For example: '%KB978207%'
Attached is a zip file that can be imported into the K1000 appliance.
http://www.kace.com/support/resources/kb/article/Patch-Report-to-find-machines-that-are-missing
This patch report will look for what machines are missing a particular Software Patch.
The SQL for the report is:
SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(M.USER_LOGGED,'\\','\\\\') as USER_LOGGED,
CS_DOMAIN
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
AND PP.TITLE like '%MS%'
ORDER BY PP.TITLE
Please NOTE: to replace the %MS% with a specific patch name in between the quotes.
For example: '%KB978207%'
Attached is a zip file that can be imported into the K1000 appliance.
