/build/static/layout/Breadcrumb_cap_w.png

Ticket Rule to run only once per ticket

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?

0 Comments   [ + ] Show comments

Answers (10)

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.
Posted by: airwolf 12 years ago
Red Belt
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.
Posted by: scarpent 12 years ago
6th Degree Black Belt
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?
Posted by: airwolf 12 years ago
Red Belt
0
Can you post the rule you're using to send the email for the Classroom Down category?
Posted by: scarpent 12 years ago
6th Degree Black Belt
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>))
Posted by: airwolf 12 years ago
Red Belt
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.
Posted by: scarpent 12 years ago
6th Degree Black Belt
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?
Posted by: airwolf 12 years ago
Red Belt
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.
Posted by: scarpent 12 years ago
6th Degree Black Belt
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
Posted by: scarpent 12 years ago
6th Degree Black Belt
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
Posted by: airwolf 12 years ago
Red Belt
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
Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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