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

Comments

Please log in to comment

Answers

0
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.

Answered 03/30/2015 by: chucksteel
Red Belt

  • 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 )
    • 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.
      • 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.
Please log in to comment
Answer this question or Comment on this question for clarity