We have 8 schools in our district and I created one queue with all 8 locations.  The user simply has to select the building.  I created a custom ticket rule that if a user selects a building, (High School), the technician in charge of the High School will become the owner of the ticket.

My question is isn't that technician supposed to receive an email stating they have a ticket in the queue?  If I choose a category and manually put that technician's name, they'll receive an email.  Did I do something wrong or miss a step?  Keep in mind, SQL is not my best friend :)

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,
                        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 = 'School') and HD_TICKET.HD_QUEUE_ID = 28 )
6 Comments   [ + ] Show Comments


  • Just to clarify, are you assigning a specific technician email dependent on category?
  • It's Dependent on location. I created a custom field and added all 8 schools. So when the user selects a particular school, the technician in charge of that building becomes the owner of the ticket. That part works fine. It's just the technician isn't receiving any email stating he has a ticket in the queue. He won't know until he goes into the system.
  • The way to go with this maybe with a second rule to run directly after the rule that specifies the tech as the owner.

    In the above script you have specified the field for the 'OWNER_EMAIL'. What actions are selected on the ticket rule? Also if you do a 'Run Now' on the ticket rule do you get any errors?
  • Sorry, a bit of an edit there I meant to ask were you adding the 'OWNER_EMAIL' to the field 'Column containing email addresses'?
  • I think that may be the issue. How would I go about adding my email address to that SQL query? Where is that particular column? Sorry for my ignorance with SQL
  • Ok....got off the phone with Dell/Kace support. Apparently the problem is in the SQL code itself. Our user has to click save twice before the technician gets an email saying there is a ticket in his queue. It's because they click save once, the owner gets changed...click save twice, the email goes through. The only thing I could do is either pay to have them redo box's SQL code or try a work around which is basically add an additional custom view to remind them to hit save twice.
Please log in to comment


You are 99% the way there. As long as you are not getting any errors when you Test the query then you should be able to select the 'Email each recipient in query results' option. The add 'OWNER_EMAIL' into the 'Column containing email addresses' field. We use a similar rule for our techs. See picture.
Answered 01/29/2015 by: Druis
Blue Belt

  • Ok thanks.....I got it to email me (the owner), but only when I click run now, even though I set it to run on ticket save. When you create your rule, do you use the wizard or SQL?
    • Normally I use the SQL end, but most of my scripts I got from postings on here. There are two articles you should read one by Dell :-

      The other by a member of this group Jverbosk:- http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned.

      One more question. If you create a test ticket then go back into the rule does the time when it last ran match the time and date when you created the test ticket?
      • I read the first article and I did find that pretty helpful. I'll check out the second article when I put out this new fire over here. I did find out about the email thing. Apparently the user has to hit save twice. Once when they are done filling out the ticket and then they have to hit save again. THEN the email comes through. That doesn't seem right but at least I'm getting there.
      • Ok, after some digging, this is what I found after the user clicks save once.

        02/02/2015 10:24:27> Starting: 02/02/2015 10:24:27 02/02/2015 10:24:27> Executing Select Query... 02/02/2015 10:24:27> selected 1 rows 02/02/2015 10:24:27> Sending ticket notifications... 02/02/2015 10:24:27> sent mail to 0 of 1 02/02/2015 10:24:27> Executing Update Query... 02/02/2015 10:24:27> updated 1 rows 02/02/2015 10:24:27> Ending: 02/02/2015 10:24:27.

        After the user clicks save twice, this comes up.

        02/02/2015 10:44:35> Starting: 02/02/2015 10:44:35 02/02/2015 10:44:35> Executing Select Query... 02/02/2015 10:44:35> selected 1 rows 02/02/2015 10:44:35> Sending ticket notifications... 02/02/2015 10:44:35> sent mail to 1 of 1 02/02/2015 10:44:35> Executing Update Query... 02/02/2015 10:44:35> updated 0 rows 02/02/2015 10:44:35> Ending: 02/02/2015 10:44:35

        I noticed this only happens on the custom ticket rule when the user selects a building. I have another custom ticket rule that redirects the ticket to another technician based on a category. That one only needs to click save once.
Please log in to comment
Answer this question or Comment on this question for clarity