/bundles/itninjaweb/img/Breadcrumb_cap_w.png
Hey everybody,

Basically I am trying to figure out how to narrow down the amount of information you get when you have a report tell you what was patched on the machine. Currently I am using this:

SELECT CONCAT(M.NAME, "\\", M.SYSTEM_DESCRIPTION) AS MACHINE_NAME,
P.IDENTIFIER AS KB_ARTICLE,
P.TITLE AS DISPLAY_NAME
FROM MACHINE_LABEL_JT MJ,
MACHINE M,
LABEL L,
PATCHLINK_MACHINE_STATUS S,
KBSYS.PATCHLINK_PATCH P
WHERE M.ID=MJ.MACHINE_ID
AND L.ID=MJ.LABEL_ID
AND M.ID = S.MACHINE_ID
AND S.PATCHUID = P.UID
AND S.STATUS = 'PATCHED'
AND (( 1 IN (SELECT 1
FROM PATCHLINK_PATCH_STATUS
WHERE P.UID = PATCHLINK_PATCH_STATUS.PATCHUID
AND PATCHLINK_PATCH_STATUS.STATUS IN ( 0 )) ))
AND L.NAME RLIKE 'Client Services|Marketing'
ORDER BY MACHINE_NAME,
P.TITLE

The problem is though it gives you all the patches with "PATCHED" (which on some machines can be as much as 225) and I only need the patches for the past week or the night that it actually updated. I know it has something to do with Deploy Date I just don't know the code. Please let me know if you have any questions or I need to explain it better.

Thanks.
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity

Answers

0
Add something like this to your where statement.

AND S.DEPLOY_STATUS_DT > DATE_SUB(NOW(),INTERVAL 7 DAY)
Answered 08/09/2011 by: dchristian
Red Belt

Please log in to comment
0
Perfect it now tells me what it patched from the night before.
Answered 08/09/2011 by: jburke
Senior Yellow Belt

Please log in to comment