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
WHERE S.PATCH_ACTION=2
GROUP BY S.ID,K.ID,M.ID
ORDER BY S.ID,NOTPATCHED DESC,PATCHED ASC,PHASE,M.NAME
Answers (2)
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.
Comments:
-
Grabbed a coffee, stared at the screen, and put some info in to test this, come back, and Chuck already answered it the right way. Good answer. - gcarpenter 11 years ago
-
chucksteel- This report looks like exactly what I need but it's just returning 0 rows when I run it (no errors). Can you help a newbie get this going? What do I need to adjust here? Has anything changed in the DB in the past 3 years that would impact this report? - nytechguy 9 years ago
-
What are your patching schedules setup like? This report will only show patching schedules that are set to Detect and Deploy. To change that you can remove the WHERE S.PATCH_ACTION = 2 line and it should return all patching schedules. Note that if you maintain separate detection and patching schedules and you only want to return on those that perform patching you would choose WHERE S.PATCH_ACTION = 3. - chucksteel 9 years ago
-
chucksteel-
My jobs are mostly all detect and deploy except for one nightly detect job- see screenshot linked below. Regardless if I comment out the line you suggest or set it to anything else (1 2 or 3) I get zero rows returned.
Here is my PATCHLINK_SCHEDULE table: http://imgur.com/JLQs6iD
What am I missing? - nytechguy 9 years ago
I ran this and didn't get results (probably because I'm lazy and haven't set this up). But I think instead of SUM, you should try COUNT and then sum the counts. Let me know. I'll try to throw some stuff together so I get output so I can see the difference between SUM and COUNT in your report.
