/build/static/layout/Breadcrumb_cap_w.png

Best Practices Question


When creating Reports, is it possible to reference labels?

06/30/2016 809 views
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


All 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.

Don't be a Stranger!

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

Sign up! or login

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