/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Ticket Rule: Last business day of every month

10/31/2019 266 views
Is it possible to create a ticket rule that only is triggered on the last business day of every month?  I setup a custom ticket rule to create a recurring ticket.  I set it to run monthly with the next run date for October 31st (Thursday) and to run monthly.  Unfortunately, the next time it will run after that is November 30th, which is a Saturday.  November only has 30 days, so the staff won't notice that ticket until December 1st.  
1 Comment   [ + ] Show comment

Comments

  • I'm a noob in SQL, but I do now there's a LAST_DAY and a DAYOFWEEK function....

    The KACE SMA uses a MySQL like DB, I'm sure something like

    DAYOFWEEK( LAST_DAY( NOW( ) )

    Should be possible... but the it has to be a good SQL statement to achieve that... You can always contact KACE Professional Services Team... but they charge for stuff like this.

    Or maybe someone from our Forum.

All Answers

2

I'm assuming that you didn't see my comment on https://www.itninja.com/question/sql-statement-help yesterday (remember that you have to Follow questions to get notified of comments, etc.). 

Here is the query that I came up with:

SELECT 
COUNT(ID), DAYOFMONTH(CREATED)
FROM HD_TICKET
WHERE YEAR(CREATED) = YEAR(NOW())
AND MONTH(CREATED) - MONTH(NOW())
AND DATE(
CASE DAYOFWEEK(LAST_DAY(NOW()))
WHEN 1 THEN DATE_SUB(NOW(), INTERVAL 2 DAY)
WHEN 7 THEN DATE_SUB(NOW(), INTERVAL 1 DAY)
ELSE LAST_DAY(NOW())
END ) = DATE(NOW())
GROUP BY dayofmonth(CREATED)

The important part is this:

AND DATE(
CASE DAYOFWEEK(LAST_DAY(NOW()))
WHEN 1 THEN DATE_SUB(NOW(), INTERVAL 2 DAY)
WHEN 7 THEN DATE_SUB(NOW(), INTERVAL 1 DAY)
ELSE LAST_DAY(NOW())
END ) = DATE(NOW())

My logic is that

  1. If the last day of the month is a Sunday (1), then the last business day was two days ago.
  2. If the last day of the month is a Saturday (7) then the last business day was one day ago.
  3. Otherwise the last day of the month is a weekday. 
  4. If today is the last day of the month, then the entire statement is true and the query returns results, otherwise the query does not return results.

When I ran my query yesterday I got results, when I ran it today I didn't, which I took as a good sign. I have the rule set to run daily but it should only generate an email on the last day of the month. Of course, I won't know that for certain until November 29th. :)

You should be able to add the important part that is posted second to the where clause of your current rule. 

Answered 11/01/2019 by: chucksteel
Red Belt

  • Thanks a ton for the reply here. I think I have something off in my Select SQL statement. When I click Run Now to test the syntax, I get the following error:

    "11/07/2019 10:31:13> Starting: 11/07/2019 10:31:13 11/07/2019 10:31:13> Executing Select Query... 11/07/2019 10:31:13> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COUNT(ID), DAYOFMONTH(CREATED) FROM HD_TICKET WHERE YEAR(CREATED) = YEAR(NOW()) ' at line 3] in EXECUTE("select "kbox_contact" as EMAILCOLUMN COUNT(ID), DAYOFMONTH(CREATED) FROM HD_TICKET WHERE YEAR(CREATED) = YEAR(NOW()) AND MONTH(CREATED) - MONTH(NOW()) AND DATE( CASE DAYOFWEEK(LAST_DAY(NOW())) WHEN 1 THEN DATE_SUB(NOW(), INTERVAL 2 DAY) WHEN 7 THEN DATE_SUB(NOW(), INTERVAL 1 DAY) ELSE LAST_DAY(NOW()) END ) = DATE(NOW()) GROUP BY dayofmonth(CREATED)")

    Here's my Select SQL:

    select
    "kbox_contact" as EMAILCOLUMN
    COUNT(ID), DAYOFMONTH(CREATED)
    FROM HD_TICKET
    WHERE YEAR(CREATED) = YEAR(NOW())
    AND MONTH(CREATED) - MONTH(NOW())
    AND DATE(
    CASE DAYOFWEEK(LAST_DAY(NOW()))
    WHEN 1 THEN DATE_SUB(NOW(), INTERVAL 2 DAY)
    WHEN 7 THEN DATE_SUB(NOW(), INTERVAL 1 DAY)
    ELSE LAST_DAY(NOW())
    END ) = DATE(NOW())
    GROUP BY dayofmonth(CREATED)
    • I wonder if it'd be easier to uncheck "Email each recipient in query results" and remove "kbox_contact" as EMAILCOLUMN and simply put a check mark in "Email results" and manually type the address in there?
      • If you use that option you lose the ability to format the output of the message. Depending on the output of the query, however, that might be a good thing. The Email Results option will give you more of a report layout, which in this case is a good thing.
    • You're missing a comma after EMAILCOLUMN.
      • lol...that's it? Not so bad then.
      • btw, thanks again!
  • Well shoot, this ticket rule didn't generate a ticket during the last business day of the month for November. Did yours?
    • Nope. Although I did find a typo. This line:
      AND MONTH(CREATED) - MONTH(NOW())
      should be
      AND MONTH(CREATED) = MONTH(NOW())
      I'm not sure why that didn't generate an error, or if it would have stopped the rule from returning any results, but it's possible.
      • Thank you! I'll see how this goes at the end of this month.
      • Hi Chuck, Thanks for all your help thus far my friend. I hope this additional post of mine makes it to your alerts so you're able to swing back. The ticket for sure opened on the last business day of the month, but unfortunately it created about 9 copies or so of the ticket. Any ideas on why that might have happened?
  • Hi chucksteel,

    So the ticket rule worked! It fired off a ticket today. The only problem is that it generated 9 copies of the ticket. Any idea on why that may have happened?

    Thanks!
    • I'm guessing you are using the "Email each recipient in query results" and there are nine rows returned in the query. Use the "Email results" option instead.
      • Thanks for the reply! I made the change and will see how it goes in a couple weeks.
 
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