/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Edit SQL report to look at Microsoft only

06/21/2016 845 views
How can this report be edited to only look at Microsoft\Windows patches?
It's an old report that can only be edited via SQL commands.
Thanks!

SELECT PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION, IP, MAC,
REPLACE(M.USER_LOGGED,'\\','\\\\') as USER_LOGGED, 
CS_DOMAIN
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN MACHINE M ON M.ID = MS.MACHINE_ID
WHERE MS.STATUS = 'NOTPATCHED'
ORDER BY PP.TITLE
0 Comments   [ + ] Show comments

Comments


All Answers

1
You can try that :
SELECT
PP.TITLE AS DISPLAY_NAME,
M.NAME AS ComputerName,
SYSTEM_DESCRIPTION,
IP,
MAC,
REPLACE(M.USER_LOGGED,'\\','\\\\') AS USER_LOGGED,
CS_DOMAIN
FROM
PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP
ON
PP.UID = MS.PATCHUID
JOIN MACHINE M
ON
M.ID = MS.MACHINE_ID
WHERE
MS.STATUS = 'NOTPATCHED'
AND VENDOR LIKE '%microsoft%'
ORDER BY
PP.TITLE

Answered 06/23/2016 by: gwir
Brown Belt

1
Thanks Gwir. This is looking good, but I got a new item to add. We need to look at patches deployed from 10/1/15 - 4/30/16, but just a date column I can sort in Excel would probably suffice. 

I've tried a few different things, but I've got little SQL experience and each failed. 

As it is, it does return a nicely formatted, easy to read doc. Though I'm not sure why some patches show up repeatedly and others hardly at all. I don't think that's a SQL issue, but I'm not sure what else it could be.

Any ideas on a date column?
Answered 06/23/2016 by: murbot
10th Degree Black Belt

  • The "detect date" is in column PATCHLINK_MACHINE_STATUS.STATUS_DT
    so in the query above you would add "MS.STATUS_DT" to your 'select' clause

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