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

Please log in to comment

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
Third Degree Blue Belt

Please log in to comment
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
Tenth 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
Please log in to comment
Answer this question or Comment on this question for clarity