When creating Reports, is it possible to reference labels?
Is it possible to reference labels in reports?
For example, I have a label for list of patches. Can I create a report showing the compliance level of all the machines for that label of patches in a Report?
Also, I have a list of computers in a label. Can I create a report like above but against just the label of computers?
How can I reference label of computer names or label of patches to use in a SQL query for the report?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
nshah
7 years ago
Yes you can. Here is one that I have for finding missing patches based on a label. Just change 'server' in this line "AND L.NAME RLIKE 'server' to whatever you want.
SELECT CONCAT(M.NAME, "\\", M.SYSTEM_DESCRIPTION) AS MACHINE_NAME,
P.IDENTIFIER AS KB_ARTICLE,
P.TITLE AS DISPLAY_NAME
FROM MACHINE_LABEL_JT MJ,
MACHINE M,
LABEL L,
PATCHLINK_MACHINE_STATUS S,
KBSYS.PATCHLINK_PATCH P
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 = 'NOTPATCHED'
AND (( 1 IN (SELECT 1
FROM PATCHLINK_PATCH_STATUS
WHERE P.UID = PATCHLINK_PATCH_STATUS.PATCHUID
AND PATCHLINK_PATCH_STATUS.STATUS IN ( 0 )) ))
AND L.NAME RLIKE 'server'
ORDER BY MACHINE_NAME,
P.TITLE
Comments:
-
Hi
Thank you for this. I did found another query similar to this to query against a label of machines, but yours give some pretty good data. The other part of my question is how can I just list against a label of patches that I want to report against.
I found the following SQL query which allows me to look at a label of patches:
SELECT RELEASEDATE,VENDOR,
LABEL.NAME,
IMPACTID,
(CASE PATCHLINK_PATCH_STATUS.STATUS WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 4 THEN 'Disabled' ELSE 'Unknown' END) AS PATCH_STATUS,
SUM(P.STATUS='PATCHED') AS PATCHED,SUM(P.STATUS='NOTPATCHED') AS NOTPATCHED,
ROUND((SUM(P.STATUS='PATCHED')/(SUM(P.STATUS='PATCHED')+SUM(P.STATUS='NOTPATCHED')))*100,0) AS PERCENT,
IDENTIFIER,TITLE
FROM KBSYS.PATCHLINK_PATCH
LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = KBSYS.PATCHLINK_PATCH.UID)
LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_ID)
LEFT JOIN PATCHLINK_PATCH_STATUS ON PATCHLINK_PATCH_STATUS .PATCHUID = PATCHLINK_PATCH.UID
LEFT JOIN PATCHLINK_MACHINE_STATUS P ON P.PATCHUID = KBSYS.PATCHLINK_PATCH.UID
WHERE LABEL.NAME = 'Pilot Approved Patches'
GROUP BY KBSYS.PATCHLINK_PATCH.ID ORDER BY LABEL.NAME desc,PATCH_STATUS desc
I like the simplicity of your report headers, and I would just want to have a report against the Patch Label in my case about Pilot Approved Patches. How can both be combined to give me this data? - tuyen 7 years ago-
Actually, I was able to combine the two with the following query:
SELECT M.NAME AS MACHINE_NAME,
P.IDENTIFIER AS KB_ARTICLE,
P.TITLE AS DISPLAY_NAME ,
REPLACE(P.RELEASEDATE,'00:00:00','') AS RELEASE_DATE
FROM MACHINE_LABEL_JT MJ,
MACHINE M,
LABEL L,
PATCHLINK_MACHINE_STATUS S,
KBSYS.PATCHLINK_PATCH P
LEFT JOIN PATCHLINK_PATCH_LABEL_JT ON (PATCHLINK_PATCH_LABEL_JT.PATCHUID = P.UID)
LEFT JOIN LABEL ON (LABEL.ID = PATCHLINK_PATCH_LABEL_JT.LABEL_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 = 'NOTPATCHED'
AND (( 1 IN (SELECT 1
FROM PATCHLINK_PATCH_STATUS
WHERE P.UID = PATCHLINK_PATCH_STATUS.PATCHUID
AND PATCHLINK_PATCH_STATUS.STATUS IN ( 0 )) ))
AND L.NAME='machine_label'
AND LABEL.NAME = 'Pilot Approved Patches'
ORDER BY MACHINE_NAME,
P.RELEASEDATE
I'm not sure how efficient the query is, but it seems to work well. - tuyen 7 years ago
