/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hi

I have created (with help of other SQL query reports I found here on ITNINJA) this nifty report I have been using to report against a set of patches in a patch label along with a set of computers in a computer label:

select CASE WHEN machine_name is null then 'Total' Else machine_name end as COMPUTER_NAME,PATCHED_COUNT,NOTPATCHED_COUNT, ROUND(((PATCHED_COUNT/total_p)*100),2) as COMPLIANCY
  from
  (
    SELECT   M.NAME as machine_name,count(S.status) as total_p,
      ( SUM(if(S.STATUS='PATCHED',1,0)) ) AS PATCHED_COUNT,
      ( SUM(if(S.STATUS='NOTPATCHED',1,0)) ) AS NOTPATCHED_COUNT
FROM   ORG1.MACHINE_LABEL_JT MJ,
       ORG1.MACHINE M,
       ORG1.LABEL L,
      ORG1.PATCHLINK_MACHINE_STATUS S ,
       KBSYS.PATCHLINK_PATCH P
LEFT JOIN ORG1.PATCHLINK_PATCH_LABEL_JT PL ON  P.UID=PL.PATCHUID
LEFT JOIN ORG1.LABEL LL ON  PL.LABEL_ID=LL.ID
WHERE  M.ID = MJ.MACHINE_ID
        AND L.ID = MJ.LABEL_ID
       AND M.ID = S.MACHINE_ID
       AND S.PATCHUID = P.UID
       AND S.STATUS IN ( 'PATCHED' ,'NOTPATCHED')
       AND LL.NAME= 'Production Approved Patches'
           AND L.NAME LIKE 'PATCHING%'
        GROUP BY M.NAME
        ) T

This query will report patch count of PATCHED and NOT PATCHED like below:
# Computer Name Patched Count Notpatched Count Compliancy
1 computer1 370 0 100
2 computer2 342 20 94.48
3 computer3 356 0 100

However, the problem arises as the numbers above show when the computer is in multiple labels matching PATCHING%.  If I run it directly against a specific label where I know it matches exactly once on the computer, then I would get the correct data:

# Computer Name Patched Count Notpatched Count Compliancy
1 computer1 185 0 100
2 computer2 171 10 94.48
3 computer3 178 0 100

I am pretty sure the reason why is because it is counting the patch count again since it matched the computer on another label.  

I hope this query is helpful for others too, however one thing I am hoping to fix now is to get it such that I am able to match the label with like, but if the label matches multiple times, I would like to make sure it only counts it once instead as in the above list, it counted twice because it found the computer names had 2 labels that matched PATCHING%.

Please let me know how I can update it correctly.  Thank you very much!  I hope this query is helpful to you too.
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

There are no answers at this time
Answer this question or Comment on this question for clarity

Answers