/build/static/layout/Breadcrumb_cap_w.png

Security Question


Help with report to filter against patch label and computer group label

10/22/2016 806 views
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   [ + ] Show comments

Comments


Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ