Systems Management : How do I create a ticket rule to email a particular technician when a user selects a location in a custom field?

How do I create a ticket rule to email a particular technician when a user selects a location in a custom field?

Our school currently has 8 buildings. I want to create a ticket rule to email a technician when a user selects a particular building from a custom category. One technician handles 4 buildings, so if a user selects one of his buildings, he will receive a notification that a ticket has been created. He would then go in and take ownership of it.

I want to do this instead of creating 8 separate queues and assigning each queue to a particular technician. I called Kace support and they told me we would get charged for having them rewrite any SQL query for the system.

I read these 2 articles, http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned and https://support.software.dell.com/k1000-systems-management-appliance/kb/111222 and while I can make a ticket rule for the whole queue, I don't know who to make a rule to generate an email for a custom category, in my case CUSTOM_1.

Any help would be great!!! Thank you.

0 Comments [ + ] Show comments

Answers (1)

Posted by: chucksteel 9 years ago

You should be able to use the rules wizard to create a rule that finds tickets with Location = "This Building". Leave the default settings for the update part of the wizard.

Once the rule is created uncheck the box for "Run update query"

Following the instructions on the KB article add a line to include the email address of the technician

Check the box to "Email each recipient in the query results"

Setup the email settings as outlined in the KB article.

Comments:

thank you for your response. I'm not really good at SQL so I'm a little in the dark. I created the rule using the wizard and the output is below. Now using the KB article, I see I need to add static distribution list and send an email, but I'm not sure how to combine what's on the KB article and my rule.

select HD_TICKET.*,
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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
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_TICKET.CUSTOM_FIELD_VALUE0 like '%Arleth%') and HD_TICKET.HD_QUEUE_ID = 40 ) - mjreccoppa 9 years ago
- A SQL query is a series of statements with the general syntax of:
  SELECT some data in a comma separated list
  FROM tables
  WHERE certain criteria are met
  
  In the case of the query above you are selecting columns from the tickets table (joins extend the query to include columns from other tables) where the custom field contains the word Arleth. We want to add some information to the selected data although in this case we are going to provide the data instead of selecting it from a table.
  
  Find this line:
  Q.NAME as QUEUE_NAME
  
  Place a comma at the end of the line (because MySQL uses commas to delineate fields) and insert a line like this:
  "arleth_tech@school.edu" AS TECHNICIAN_EMAIL
  
  You would want to use the email address that you want to be notified. Whatever you enter as TECHNICIAN_EMAIL is what you want to place in the Email Column field of the rule when configuring the "Email each recipient in the query results" section.
  
  Note that every piece of data that is selected in the query is available as a variable when configuring the email. You can see that most of the lines end in something like "as SUBMITTER_FULLNAME", this makes the field available as $submitter_fullname when creating the email template. - chucksteel 9 years ago
  - perfect...thank you...that seemed to work. Now I just want to set up the email that gets sent to the tech with some basic information..submitter name, category, priority and maybe a link to the ticket itself. I tried to combine with the KB article but I got errors. I'll continue to play around a bit. - mjreccoppa 9 years ago

Answer this question

Posted by:

View more:

Systems Management Questions

question