/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Adding Columns to Report

06/20/2019 181 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
Senior Purple 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