Adding Columns to Report
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
Answers (2)
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.
Comments:
-
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. - SMal.tmcc 5 years ago
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.