Best Practices : When creating Reports, is it possible to reference labels?

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)

Posted by: nshah 8 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 8 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 8 years ago

Answer this question

Posted by:

View more:

Best Practices Questions

question