KACE Product Support

OK, I think I see where you're going with this now.  Try these - one lists each machine with relevant patches, the other lists the patches themselves.  Change the L.NAME in line 18 (machines report) and line 20 (patches report) to match the labels in your own environment and see if these will work (or at least get you closer to what you need).

Hope that helps!

John

______________________________________________

Machines report

*SQL Query*
SELECT M.NAME AS MACHINE_NAME,
OS_NAME AS WINDOWS_VERSION,
PP.TITLE AS PATCH_NAME,
PP.IMPACTID as IMPACT,
(CASE PPS.STATUS
  WHEN 0 THEN 'Active'
  WHEN 1 THEN 'Inactive'
  WHEN 4 THEN 'Disabled'
  ELSE 'Unknown' END)
AS PATCH_STATUS,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'server'
AND PP.IMPACTID = 'Critical'
AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
AND PP.VENDOR rlike 'adobe|microsoft'
AND PPS.STATUS in (0)
GROUP BY M.NAME, PATCH_NAME
ORDER BY M.NAME, PP.RELEASEDATE, PP.TITLE

*Break on Columns*
MACHINE_NAME

______________________________________________

Patches report

*SQL Query*
SELECT PP.TITLE AS PATCH_NAME,
PP.IMPACTID as IMPACT,
(CASE PPS.STATUS
  WHEN 0 THEN 'Active'
  WHEN 1 THEN 'Inactive'
  WHEN 4 THEN 'Disabled'
  ELSE 'Unknown' END)
AS PATCH_STATUS,
SUM(MS.STATUS='PATCHED') AS PATCHED,
SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')
  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
DATE_FORMAT(PP.RELEASEDATE, '%Y-%m-%d') AS RELEASED
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)
JOIN LABEL L ON (ML.LABEL_ID = L.ID)
WHERE L.NAME = 'server'
AND PP.IMPACTID = 'Critical'
AND RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY)
AND PP.VENDOR rlike 'adobe|microsoft'
AND PPS.STATUS in (0)
GROUP BY PATCH_NAME
ORDER BY PP.RELEASEDATE desc, PP.TITLE

Has anyone mentioined this blog?

http://www.itninja.com/blog/view/report-of-critical-paths-deployed-by-vendor

First and foremost, cheers to the both of you for the assist!!! I have a majority of what I need but I am having one more issue. I have been told that the audit department just wants the percentage of servers and workstations. I created a smart label named "All Servers" and "All Workstations" but I just cant figure out where to place it. Here is an example of my script:

SELECT RELEASEDATE,VENDOR,
GROUP_CONCAT(DISTINCT LABEL.NAME SEPARATOR '\n') AS LABEL_NAME_GROUPED,
IMPACTID,
(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS,
SUM(P.STATUS='PATCHED') AS PATCHED,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(P.STATUS='PATCHED')/(SUM(P.STATUS='PATCHED')+SUM(P.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
IDENTIFIER,TITLE
FROM KBSYS.PATCHLINK_PATCH 
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_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID
LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE (RELEASEDATE > DATE_SUB(NOW(),INTERVAL 60 DAY))
AND ((VENDOR = 'Adobe Systems Inc.') OR (VENDOR = 'Microsoft Corp.'))
GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY LABEL_NAME_GROUPED desc,PATCH_STATUS desc

I read in one of the threads on here that it needed to be placed above the "group by" entry but when I do that I get nothing in return......

this might help

http://www.itninja.com/question/run-a-report-against-a-smart-label

or this

http://www.itninja.com/question/kace-1000-patch-reports

a variety of reporting results

http://www.itninja.com/search?q=report+for+critical+patches&i.x=0&i.y=0&cx=014503921158165719423%3A3hy89iql-j8&t=search-filter-by-keyword#search-filter-by-keyword

Here are a couple more articles that might help, in addition to what nshah suggested (the second to help with the SQL if you aren't too familiar with it):

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

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

John