We have a category of Classroom Down and as soon as the Ticket Saves a message is sent to a designated group of technicians so someone can respond right away.

Right now the way it works if the technician doesn't change the category of Classroom Down to something else everytime there is an update to the ticket the email is sent to all the technicians.

Is there anyway to have this ticket rule run on save but only once per ticket so that everytime an update is made to the ticket the emails do not get sent out?
Answer Summary:
Make the following OTS rule: Select Query: select T.ID as ID from HD_TICKET T join HD_CATEGORY C on (T.HD_CATEGORY_ID = C.ID) join HD_STATUS S on (T.HD_STATUS_ID = S.ID) join USER U on (T.OWNER_ID = U.ID) where C.NAME = 'Classroom Down' and U.FULL_NAME not like '%_%' and S.NAME = 'Opened' and T.HD_QUEUE_ID = 1 Update Query: update HD_TICKET T join HD_STATUS S on (T.HD_STATUS_ID = S.ID) set T.HD_STATUS_ID = S.ID where (T.ID in ()) and S.NAME = 'Classroom Down - Email Sent' Check the box that says 'Send query results to someone' and then put the comma separated email address list in this box.
Cancel
0 Comments   [ + ] Show Comments

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.

Answers

0
Make another category named "Classroom Down - Email Sent". Have the rule that fires off the email change the category to the email sent one.
Answered 02/01/2012 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I created the category can you help me get started with the query to change the category as soon as the email is sent?
Answered 02/01/2012 by: scarpent
Second Degree Black Belt

Please log in to comment
0
Can you post the rule you're using to send the email for the Classroom Down category?
Answered 02/01/2012 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', ')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (((( HD_CATEGORY.NAME = 'Classroom Down') AND (1 not in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.FULL_NAME like '%_%')) ) AND HD_STATUS.NAME = 'Opened') and HD_TICKET.HD_QUEUE_ID = 1 )


update HD_TICKET as T
set T.CC_LIST = '4196513301@vtext.com,4195652479@vtext.com,3306974545@vtext.com,4195647136@vtext.com,4195611360@vtext.com,4196514999@vtext.com,4192028119@vtext.com,4196511792@vtext.com'
where
(T.ID in (<TICKET_IDS>))
Answered 02/01/2012 by: scarpent
Second Degree Black Belt

Please log in to comment
0
You're adding them as cc's - there is no rule sending the updates. You'd have to remove them from the CC field. You can do that manually once a technician picks up the ticket, or use a rule by changing the category (or some other field).

Seems to me the easiest solution is to ask the technician who picks up the ticket to clear out the CC list.
Answered 02/01/2012 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I tried several other ways to send text messages to the cell phones and it would not work.

Do you know of a better way of doing this with a rule?
Answered 02/01/2012 by: scarpent
Second Degree Black Belt

Please log in to comment
0
Make the following OTS rule:

Select Query:
select T.ID as ID from HD_TICKET T
join HD_CATEGORY C on (T.HD_CATEGORY_ID = C.ID)
join HD_STATUS S on (T.HD_STATUS_ID = S.ID)
join USER U on (T.OWNER_ID = U.ID)
where C.NAME = 'Classroom Down'
and U.FULL_NAME not like '%_%'
and S.NAME = 'Opened'
and T.HD_QUEUE_ID = 1

Update Query:
update HD_TICKET T
join HD_STATUS S on (T.HD_STATUS_ID = S.ID)
set T.HD_STATUS_ID = S.ID
where
(T.ID in (<TICKET_IDS>)) and S.NAME = 'Classroom Down - Email Sent'


Check the box that says 'Send query results to someone' and then put the comma separated email address list in this box.
Answered 02/01/2012 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
I will try this to see how it works. I tried several different ways including just using the CC filed in the category list but if the Kbox was busy we wouldn't get the text messages sometimes until 1/2 hour after the ticket was put in. I will test and let you know if this works. Thanks
Answered 02/01/2012 by: scarpent
Second Degree Black Belt

Please log in to comment
0
This seems to work but I need to send the ticket number, submitter and problem subject to the email addresses. When it sends it only lists my titles in the email. example: ticketid submitter it doesn't actually give me any information
Answered 02/10/2012 by: scarpent
Second Degree Black Belt

Please log in to comment
0
As designed, it's only going to send a list of ticket IDs - because that is the query result. If you want to add the submitter and ticket title, you would need to modify the Select Query to this:
select T.ID as ID, T.TITLE as 'Title', U2.FULL_NAME as 'Submitter' from HD_TICKET T
join HD_CATEGORY C on (T.HD_CATEGORY_ID = C.ID)
join HD_STATUS S on (T.HD_STATUS_ID = S.ID)
join USER U on (T.OWNER_ID = U.ID)
join USER U2 on (T.SUBMITTER_ID = U2.ID)
where C.NAME = 'Classroom Down'
and U.FULL_NAME not like '%_%'
and S.NAME = 'Opened'
and T.HD_QUEUE_ID = 1
Answered 02/12/2012 by: airwolf
Tenth Degree Black Belt

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