KACE Product Support

Make another category named "Classroom Down - Email Sent". Have the rule that fires off the email change the category to the email sent one.

I created the category can you help me get started with the query to change the category as soon as the email is sent?

Can you post the rule you're using to send the email for the Classroom Down category?

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>))

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.

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?

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.

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

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

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