K1000 Service Desk not emailing technician when custom ticket rule is used.
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,
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 = 'School') and HD_TICKET.HD_QUEUE_ID = 28 )
6 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
Druis
9 years ago
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.
Comments:
-
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? - mjreccoppa 9 years ago
-
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 :-
https://support.software.dell.com/k1000-systems-management-appliance/kb/111222
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? - Druis 9 years ago-
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. - mjreccoppa 9 years ago
-
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. - mjreccoppa 9 years ago
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? - Druis 9 years ago