Help with report to filter against patch label and computer group label
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
Answers (0)
Please log in to answer
Be the first to answer this question
