/build/static/layout/Breadcrumb_cap_w.png

How to automatically update month and year when a report is run depending on what month and year it is.

I am making a report which will be run monthly. It is to show Security Only patches for that month and year. 

I want the month and year to be automatically updated when I run the report (depending on what current month and year it is). 


 left join

      PATCH_STATUS 

      on PATCH_STATUS.PATCH_ID = KBSYS.PATCH.ID 

WHERE

   (

(KBSYS.PATCH.TITLE like '%2020-06 Security Only %') 

      OR 

      (

         KBSYS.PATCH.TITLE like '%2020-06 Security Monthly %'

      )

   )


Here, there is "2020-06 Security Only". I want the report to automatically update the "2020-06 Security Only" to the month and year the report is being run. For example: I decide to run the report in August 2020 then it should be updated to "2020-08 Security Only" 

I am trying something like - KBSYS.PATCH.TITLE like '%(CONCAT(SELECT YEAR(CURDATE()),'-',SELECT MONTH(NOW()),' ','Security',' ','Only'))%'         This is not working. Seems like KACE does not allow to declare variables. 

Thank you. 


0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 3 years ago
Red Belt
1

You don't need to use SELECT for the functions and they need to be outside of the single quotes:

KBSYS.PATCH.TITLE like concat(YEAR(NOW()), '-',lpad(MONTH(NOW()), 2, '0'), ' Security Only%') 

Because the month function doesn't return zero padded values for single digit months you need to do that, too. Also, I removed the initial wildcard because I'm fairly certain the patches you want to return all start with that string and it is more efficient to only include the wildcard at the end. If that isn't the case, then you will need to add it back.



Comments:
  • Thanks a lot. This work perfectly. - AleinsExist9 3 years ago
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ