/build/static/layout/Breadcrumb_cap_w.png
10/31/2019 148 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.