Need help creating a custom report that displays all patches applied on a certain date.
The report will need to be: sorted by computer listing showing all patches applied to that particular computer on a certain date.
Example:
Report - Patch Completed on 3/28/2012
WORKSTATION 1
- MSQ 0156-7894 Microsoft Word 2007 Vulernability
- KB 0168135 Microsoft 2012 EOL Patch
SERVER 1
- KB 68435 All your base are belong to us
Any help would be greatly appreciated.
UPDATE: Is there a way to consolidate the computer names if sorted by deploy date? Also is there a way to input = 30 day interval from the currdate?
Example:
Report - Patch Completed on 3/28/2012
WORKSTATION 1
- MSQ 0156-7894 Microsoft Word 2007 Vulernability
- KB 0168135 Microsoft 2012 EOL Patch
SERVER 1
- KB 68435 All your base are belong to us
Any help would be greatly appreciated.
UPDATE: Is there a way to consolidate the computer names if sorted by deploy date? Also is there a way to input = 30 day interval from the currdate?
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
dchristian
12 years ago
Here's a modified version of the for each machine report. I think it'll give you the info your looking for.
SELECT CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE.NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN Concat(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME,
P.IDENTIFIER AS KB_ARTICLE,
IF(S.DEPLOY_STATUS_DT LIKE '000%','Not Installed by Kace',DATE_FORMAT(S.DEPLOY_STATUS_DT,'%Y-%m-%d')) as DEPLOY_DATE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND S.STATUS = 'PATCHED'
AND P.IS_APP = 0
ORDER BY DEPLOY_DATE, MACHINE_NAME,
P.TITLE
SELECT CASE
WHEN MACHINE.SYSTEM_DESCRIPTION = '' THEN MACHINE.NAME
WHEN MACHINE.SYSTEM_DESCRIPTION != '' THEN Concat(MACHINE.NAME, "\\", MACHINE.SYSTEM_DESCRIPTION)
END AS MACHINE_NAME,
P.TITLE AS DISPLAY_NAME,
P.IDENTIFIER AS KB_ARTICLE,
IF(S.DEPLOY_STATUS_DT LIKE '000%','Not Installed by Kace',DATE_FORMAT(S.DEPLOY_STATUS_DT,'%Y-%m-%d')) as DEPLOY_DATE
FROM PATCHLINK_MACHINE_STATUS S,
MACHINE,
KBSYS.PATCHLINK_PATCH P
WHERE MACHINE.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND S.STATUS = 'PATCHED'
AND P.IS_APP = 0
ORDER BY DEPLOY_DATE, MACHINE_NAME,
P.TITLE
Comments:
-
Great report. Thanks for posting. - ohiosoundguy 12 years ago
-
Great! Thanks much. I'll give it a try. - RealityHook 12 years ago
-
Is there a way to consolidate the computer names if sorted by deploy date? Also is there a way to input >= 30 day interval from the currdate? Everything I try says it's wrong and wont run. - RealityHook 12 years ago
Posted by:
RealityHook
12 years ago
Is there a way to consolidate the computer names if sorted by deploy date? Also is there a way to input >= 30 day interval from the currdate? Everything I try says it's wrong and wont run. <- Lack of experience here.