/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Adding Columns to Report

06/20/2019 216 views

I have this report created and it produces a nice simple report on multiple device labels, but how would I add a column to report the # of  security and non security patch count? How do I know what options are available for stuff like this? 


SELECT M.NAME AS MACHINE_NAME, M.ID,

OS_NAME AS WINDOWS_VERSION,

SUM(MS.STATUS='PATCHED') AS PATCHED,

SUM(MS.STATUS='NOTPATCHED') AS NOTPATCHED,

ROUND((SUM(MS.STATUS='PATCHED')/(SUM(MS.STATUS='PATCHED')

  +SUM(MS.STATUS='NOTPATCHED')))*100,0) AS PERCENT_PATCHED

FROM MACHINE M

LEFT JOIN PATCHLINK_MACHINE_STATUS MS ON (M.ID = MS.MACHINE_ID)

LEFT JOIN KBSYS.PATCHLINK_PATCH PP ON MS.PATCHUID = PP.UID AND PP.IS_SUPERCEDED = 0

LEFT JOIN PATCHLINK_PATCH_STATUS PPS ON (PP.UID = PPS.PATCHUID AND PPS.STATUS = 0)

JOIN MACHINE_LABEL_JT ML ON (M.ID = ML.MACHINE_ID)

JOIN LABEL L ON (ML.LABEL_ID = L.ID)

WHERE L.NAME IN 

(

  'DL - Austin - Group_A',

'DL - Austin - Group_B',

'DL - Austin - Group_C',

'DL - Austin - Group_D',

'DL - Austin - Group_E'

)

GROUP BY M.NAME

ORDER BY PERCENT_PATCHED, M.NAME 

0 Comments   [ + ] Show comments

Comments


All Answers

0

You can enable database access (externally) and use TOAD or a similar product to look at the tables and columns to see what is available.

Answered 06/20/2019 by: SMal.tmcc
Red Belt

  • see this article that chucksteel posted on his site

    http://chucksteel.blogspot.com/2017/09/linking-power-bi-to-quest-k1000-sma.html

    I think this will give you the tool to see how the tables are in relation to the others and help you find the fields you want.
0

If you add PP.* or MS.* to a line in the above query, you can see all the columns for those tables. I dont know what else I can search. 

Answered 06/21/2019 by: lama01
Blue Belt

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