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
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

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.

Answered 12/13/2012 by: chucksteel
Red Belt

  • 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.
  • 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?
    • 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-
        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?
Please log in to comment

Answers

0

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.

Answered 12/13/2012 by: gcarpenter
Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity