I've got a quick question. I'd like to be able to automatically have an email sent to our ticket owners if tickets they are servicing have had no updates in 7 days. Is this possible?
0 Comments   [ - ] Hide Comments


Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.
Answer this question or Comment on this question for clarity


You could setup a ticket rule to do this. It would email only those who require it containing only their information. In a rule you would have a select query and you could choose one of the email actions. To send it to a specific own you would need to use a GROUP_CONCAT function and the "send an email for each result row" feature. So technically you are compressing each owners results into one row and expanding them in the email.

If that sounds daunting then you could schedule a single report that goes to all your ticket owners by email (a distro list?). use a date function based on the HD_TICKET.MODIFIED column.

Or create one report per owner similar to the single report
Answered 08/18/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
We achieved a similar goal by sending emails to everyone involved in the ticket after 14 days of inactivity. You may not want this, though, if you don't want to notify your submitters. We typically wait on info from submitters more often than have an owner let a ticket stagnate, so I've written a rule that does the following:
  1. [SELECT Query] Query all tickets that have not been modified in 14 days or more.
    select T.ID as ID from HD_TICKET T
    where DATEDIFF(NOW(),T.MODIFIED) > 14

  2. [Check Results are tickets, add a comment to each one] Send a notification by way of making a comment.
    Our notification says, "Automated Notification: FYI, this ticket has not been updated or modified in 2 weeks."
  3. [UPDATE Query] Change the modified date to now (the time when the rule is run and the comment is added).
    update HD_TICKET as T
    set T.MODIFIED = NOW()
    where (T.ID in (<TICKET_IDS>))
You can modify that to work for 7 days easily. If this won't work for you and you want to 'silently' notify owners (without changing the ticket at all), let me know and I'll show you how to do that. Basically, in step 2 above we'd check the "Send an email for each result row" box and we'd have to change the Select query a bit to add an EMAIL column by joining to the USER table on the OWNER_ID.

Also, you want the rule to run on a schedule - not OTS (On Ticket Save). Every 15 minutes is fine, hourly, daily, whatever. Just don't rely on OTS for something like this, or it will only run when someone modifies a ticket - which would mean it would run constantly on a busy KBOX.
Answered 08/19/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment