Reporting for new patchs
Hi everyone.
I need to create two reports, they will contain all the new downloaded patchs (status = active), during a certain period of time :
- One report for all new active downloaded patchs (type = SECURITY), for the last 24h hours.
- One report for all new active downloaded patchs (type = NON-SECURITY), for the last week.
I tried several tips (like the one with smart label..) and it not figure to work.
The patchs are not labelised.
The machines are (name of label = XX)
What i try to do is to make a report at the morning to see if some patchs have to be disabled (manually) before deployement.
I'm not very good at SQL, if someone have made it before, thanks for help ! :)
Answers (1)
Try this for security. Change the security line to '0' for non-security.
SELECT DISTINCT TITLE, HYPERLINK, RELEASEDATE FROM KBSYS.PATCHLINK_PATCH PP LEFT JOIN PATCHLINK_PATCH_LABEL_JT PPJT ON PP.UID=PPJT.PATCHUID LEFT JOIN PATCHLINK_PATCH_STATUS P ON P.PATCHUID = PP.UID WHERE RELEASEDATE > DATE_SUB(NOW(),INTERVAL 7 DAY) AND PP.IS_SECURITY = '1' AND P.STATUS ='0' ORDER BY TITLE
One thing I do is run something like this. All my patches come in as inactive. I then have a report scheduled nightly to show new patches that have shown in my Kbox that would fall into one of my labels. I have it set to run only if results are present. This way I can still verify each one before it is set to active. All of my patch labels begin with patch_. So this will weed out all the ones I do not care about.
SELECT TITLE, HYPERLINK, RELEASEDATE, L.NAME as 'Label Name' FROM KBSYS.PATCHLINK_PATCH PP LEFT JOIN PATCHLINK_PATCH_LABEL_JT PPJT ON PP.UID=PPJT.PATCHUID LEFT JOIN LABEL L ON L.ID=PPJT.LABEL_ID WHERE RELEASEDATE > DATE_SUB(NOW(),INTERVAL 1 DAY) AND L.NAME LIKE 'PATCH_%' ORDER BY TITLE
Comments:
-
Oh, thank you very much ! Help me a lot ! :) - lipo 10 years ago
-
Just one more thing !
I'd like to make a column who contain if the patch is of type SECURITY or NON-SECURITY.
How to get the text corresponding to the value PP.IS_SECURITY=0 AND PP.IS_SECURITY =1 ?
thanks a lot - lipo 10 years ago-
I believe this is what you're asking for? Of course since you are filtering by Security already everything should show as Security. So be sure to take that line out.
SELECT DISTINCT TITLE, HYPERLINK, RELEASEDATE,
CASE WHEN PP.IS_SECURITY = '1' THEN 'SECURITY'
WHEN PP.IS_SECURITY = '0' THEN 'NON-SECURITY'
END
FROM KBSYS.PATCHLINK_PATCH PP
LEFT JOIN PATCHLINK_PATCH_LABEL_JT PPJT ON PP.UID=PPJT.PATCHUID
LEFT JOIN PATCHLINK_PATCH_STATUS P ON P.PATCHUID = PP.UID
WHERE RELEASEDATE > DATE_SUB(NOW(),INTERVAL 7 DAY)
AND PP.IS_SECURITY = '1'
AND P.STATUS ='0'
ORDER BY TITLE - dugullett 10 years ago
-
Everything's ok ! Thanks :) - lipo 10 years ago
-
Hey Dugullett. Thanks for the report sql it's worked a charm! I'm wondering though if you know of a way to make the hyperlink in your report actually show up as a hyperlink in the html report so you can just click the link to go to the page? - mgomez 10 years ago
-
Unfortunately not. I know you can create reports using the GUI and the links will show. I tried this, and hyperlink is not an option using the GUI. Maybe we'll see it in a few more revs? Support might be better able to answer that. - dugullett 10 years ago
SELECT PP.TITLE AS 'Patch Name',
RELEASEDATE AS 'Date'
FROM PATCHLINK_MACHINE_STATUS MS
JOIN KBSYS.PATCHLINK_PATCH PP ON PP.UID = MS.PATCHUID
JOIN PATCHLINK_PATCH_STATUS PPS ON PPS.PATCHUID = PP.UID
AND RELEASEDATE > DATE_SUB(now(), INTERVAL 7 DAY)
AND PPS.STATUS in (0)
AND IS_SECURITY=0
GROUP BY PP.TITLE
ORDER BY PP.RELEASEDATE, PP.TITLE
it's correct ?
Thanks :) - lipo 10 years ago