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 ! :)

 

 

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • I try my best, and configure it like this :
    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 :)
Please log in to comment

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
Answered 07/01/2013 by: dugullett
Red Belt

  • Oh, thank you very much ! Help me a lot ! :)
  • 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
    • 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
  • Everything's ok ! Thanks :)
  • 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?
    • 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.
Please log in to comment
Answer this question or Comment on this question for clarity