Kace Patch Deploy status - Executive level
I am attempting to modify a SQL report that I found to make it a bit more "executive-friendly" and I am running into problems. The report currently lists each patch schedule, along with each machine included within the patch schedule and a count of patched, notpatched, and failed. We have hundreds of systems, so this is a pretty lengthy report
I'd like to modify this script to display the sum of each label's computers patched vs. nonpatched vs. failed, rather than listing this data for each individual computer.
To give an example, the report currently prints out each patch schedule like this (the last 3 columns are patched, notpatched, and deploy unsuccessful):
|4 of Schedule: Java Patches (Last Run: 11-28-2012 04:00:10 PM)|
|2||Workstation1||Microsoft Windows 7 Professional||completed||1||0||0|
|3||Workstation2||Microsoft Windows 7 Professional||completed||1||0||0|
|4||Workstation3||Microsoft Windows XP Professional||completed||2||0||0|
|5||Workstation4||Microsoft Windows 7 Professional||completed||2||0||0|
I'd like to modify the SQL code to sum up all of this information. It should display each patch schedule name and last run date, but should display the sums of patched, notpatched, and deploy unsuccessful rather than displaying these numbers for each individual workstation.
I'd appreciate any help with this.
SELECT CONCAT(S.DESCRIPTION, ' (Last Run: ',DATE_FORMAT(S.LAST_RUN,'%m-%d-%Y %r'),')') AS SCHEDULE,
M.NAME AS MACHINE_NAME,
M.OS_NAME AS OS_NAME,
K.PHASE AS TASK_STATUS,
SUM(PMS.STATUS='PATCHED') AS PATCHED,
SUM(PMS.STATUS='NOTPATCHED') AS NOTPATCHED,
SUM(PMS.STATUS='NOTPATCHED' AND PMS.DEPLOY_ATTEMPT_COUNT>0) AS DEPLOY_UNSUCCESSFUL
FROM PATCHLINK_SCHEDULE S
JOIN KBSYS.KONDUCTOR_TASK K ON K.TYPE=S.KONDUCTOR_TASK_TYPE
JOIN MACHINE M ON M.KUID=K.KUID
JOIN PATCHLINK_MACHINE_STATUS PMS ON PMS.MACHINE_ID=M.ID
JOIN PATCHLINK_PATCH_LABEL_JT J ON PMS.PATCHUID=J.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PS ON PS.PATCHUID=PMS.PATCHUID AND PS.STATUS=0
JOIN PATCHLINK_SCHEDULE_DEPLOY_LABEL_JT SJ ON SJ.PATCHLINK_SCHEDULE_ID=S.ID AND SJ.LABEL_ID=J.LABEL_ID
GROUP BY S.ID,K.ID,M.ID
ORDER BY S.ID,NOTPATCHED DESC,PATCHED ASC,PHASE,M.NAME
Community Chosen Answer
If you set your GROUP BY to only S.ID it will group everything by just the patch schedule and will display just one line per schedule and totals for everything in that schedule. When I ran this I received what I think you're looking for. Remember that there will be very large numbers for each schedule, however, since it will now include the total number of patches deployed to all computers, so if one patch is deployed to 100 computer, this report will report 100 in the Patched column.