Edit SQL report to look at Microsoft only
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
Answers (2)
Please log in to answer
Posted by:
gwir
8 years ago
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
Posted by:
murbot
8 years ago
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?
Comments:
-
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 - JasonEgg 8 years ago