I have written this report I want to count the number of computers getting patched but there seems to bee a miscalculation the total number of computers vs the total # of patched computers is way out. And when you see the % patch compliance it does not make sense, can anyone please have a look and advise.

Select '<ORGANIZATION_NAME>' AS Operating_Platform, P.NAME as Business_Name, C.NAME as Client, KBSYS.PATCHLINK_PATCH.IMPACTID,
       (Select count(id) from <ORGANIZATION_DB>.MACHINE where OS_NAME NOT LIKE '%Server%') AS `Total # of Computers`,
       COUNT(DISTINCT MACHINE.ID) AS `# of  Patched Computers`,
       ROUND((SUM(PATCHLINK_MACHINE_STATUS.STATUS = 'PATCHED')/(SUM(PATCHLINK_MACHINE_STATUS.STATUS = 'PATCHED') + SUM(PATCHLINK_MACHINE_STATUS.STATUS = 'NOTPATCHED')))
          * 100,0) AS `Patch Compliance %`
FROM <ORGANIZATION_DB>.MACHINE
LEFT JOIN <ORGANIZATION_DB>.MACHINE_LABEL_JT MLJT ON (MACHINE.ID = MLJT.MACHINE_ID)
LEFT JOIN <ORGANIZATION_DB>.LABEL C ON (C.ID = MLJT.LABEL_ID)
LEFT JOIN <ORGANIZATION_DB>.LABEL_LABEL_JT LLJT ON (C.ID = LLJT.CHILD_LABEL_ID)
LEFT JOIN <ORGANIZATION_DB>.LABEL P ON (P.ID = LLJT.LABEL_ID)
       JOIN <ORGANIZATION_DB>.MACHINE_LABEL_JT MACHINE_LABEL_JT
       JOIN <ORGANIZATION_DB>.LABEL
       JOIN <ORGANIZATION_DB>.PATCHLINK_SCHEDULE_LABEL_JT
       JOIN <ORGANIZATION_DB>.PATCHLINK_MACHINE_STATUS
       JOIN KBSYS.PATCHLINK_LST,
       KBSYS.PATCHLINK_LST_PATCH_JT,
       KBSYS.PATCHLINK_PATCH
 WHERE   (MACHINE.ID = MACHINE_LABEL_JT.MACHINE_ID)
       AND (MACHINE_LABEL_JT.LABEL_ID = LABEL.ID)
       AND (LABEL.ID = PATCHLINK_SCHEDULE_LABEL_JT.LABEL_ID)
       AND (MACHINE.ID = PATCHLINK_MACHINE_STATUS.MACHINE_ID)
       AND (C.TYPE !='hidden')
       AND (MACHINE.OS_NAME NOT LIKE '%Server%')
       AND (PATCHLINK_MACHINE_STATUS.PATCHUID = KBSYS.PATCHLINK_PATCH.UID)
       AND KBSYS.PATCHLINK_PATCH.UID = KBSYS.PATCHLINK_LST_PATCH_JT.PATCHUID
       AND KBSYS.PATCHLINK_LST_PATCH_JT.LST_ID = KBSYS.PATCHLINK_LST.ID
       AND KBSYS.PATCHLINK_LST.ID IN (3,17,23,36,38,33,32)
GROUP BY Operating_Platform, KBSYS.PATCHLINK_PATCH.IMPACTID

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

I've written several reports to do this and have been meaning to write up as a blog, but just haven't had time.  I'll try to write/post later today/tonight and advise once it's up.  If any of them don't cover what you need, let me know here.

John

Answered 10/31/2012 by: jverbosk
Red Belt

  • It's up - see if any of these help:

    http://www.itninja.com/blog/view/k1000-reports-patching-reports-for-completion-by-patch-machine-vendor-using-labels

    John
  • Thanks this helps.
Please log in to comment
Answer this question or Comment on this question for clarity