I have several rules to notify supervisors by email when a ticket has been in a state for a specified period of time. My first try ran daily, but would repeatedly email about the same tickets because the statement selected anything over X # of days. To solve this I tried changing it to ticket has been in status X = X # of days. So the time would have to be exactly equal to and send only one email for that ticket. I think it is requiring the tme to = 10days 0h 0m 0s. The rule runs daily so that almost never happens. So how do I get it to send one email per ticket once it is 10 days old, but make sure it catches the tickets.

I'm assuming it needs to be =>10 days and <11days, but I don't know if that's two separate statements or one. Again SQL newbie here.

I also noticed it executing the query, getting the rows but then saying 0 of 5 emails sent. Do I have the email addresses set wrong in the query?

Here's the select query:

Select 'lwalters@wacoisd.org' AS LISA,

'cfrey@wacoisd.org' as CHARLIE,

OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email

S.NAME AS STATUS, T.ID AS ID, T.TITLE AS ISSUE,

U.FULL_NAME AS SUBMITTER, O.FULL_NAME AS OWNER,

T.CREATED AS CREATED, T.MODIFIED AS STALLED

FROM HD_TICKET T

JOIN HD_STATUS S ON (S.ID = T.HD_STATUS_ID)

JOIN USER U ON (U.ID = T.SUBMITTER_ID)

JOIN USER O ON (O.ID = T.OWNER_ID)

LEFT JOIN USER OWNER ON OWNER.ID = T.OWNER_ID

WHERE (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'ajimenez')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'trott')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'mmccormick')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'sfelkner')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'ckluk')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'bgamboa')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'rdaniels')

OR (S.NAME = 'New' AND DATEDIFF(NOW(), T.MODIFIED) = 10 AND O.USER_NAME = 'lmland')

AND T.HD_QUEUE_ID = 1

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Found the answer here: http://www.itninja.com/question/multiple-email-address-formatting-in-rules. I used CONCAT to group multiple emails.

Answered 02/13/2013 by: lmland
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity