Edit SQL report to look at Microsoft only

  • murbot
    Tenth Degree Black Belt
  • How helpful is this to you?
  • Average Rating 4
  • 370 views
  • 06/21/2016
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
Green 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