I'm looking for a field to search on in a report to show recently installed patches, ie past 7 days or entering a specific date in the SQL query. I'm wanting to pull up a report after patching that shows what patches were installed sorted by computer after we run a patch job. It's too time consuming to go to each computer in Inventory to pull up this information. Thanks.

Answer Summary:
SELECT DISTINCT M.NAME, IP, P.TITLE, PM.DEPLOY_STATUS_DT AS 'DEPLOY DATE', PM.STATUS FROM MACHINE M LEFT JOIN PATCHLINK_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PM.PATCHUID WHERE (DEPLOY_STATUS_DT > CURDATE() - INTERVAL 7 DAY)
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

5

Something like this will get you going. If you need it to include more detail let me know.

SELECT DISTINCT M.NAME, IP, P.TITLE, PM.DEPLOY_STATUS_DT AS 'DEPLOY DATE', PM.STATUS

FROM MACHINE M

LEFT JOIN PATCHLINK_MACHINE_STATUS PM ON M.ID=PM.MACHINE_ID

LEFT JOIN KBSYS.PATCHLINK_PATCH P ON P.UID=PM.PATCHUID

WHERE (DEPLOY_STATUS_DT > CURDATE() - INTERVAL 7 DAY)

*Break on M.NAME

Answered 10/29/2012 by: dugullett
Red Belt

  • This got me going in the right direction, thanks.
Please log in to comment