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

Comments

Please log in to comment

Answers

1
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 
Answered 06/30/2016 by: nshah
Red Belt

  • 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?
    • 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.
Please log in to comment
Answer this question or Comment on this question for clarity