System Queue Rule: Email on Close
Hi All, I am trying to set up a system queue rule to send an email to the submitter of a ticket the moment that ticket is closed. The purpose of this is for the user to be able to see the resolution in the email to verify that the resolution is what they required. Does anyone have a working example of this? I am not well versed in sql/mysql so I have been working on a rule, but cannot get it to work. The log just keeps saying
06:40> Starting: Mon, 23 Apr 2012 10:06:40 -0400
06:40> Executing Select Query...
06:40>
and the last line never shows anything else. Any help troubleshooting why it might be doing that or a working rule would be very much appreciated!
Answers (4)
If you look at your Queue>Service Desk Configuration> Email on Events: [Customize Emails] > Ticket Closed on Submitter is it checked?
Comments:
-
No, I unchecked that so that there would not be 2 emails being sent to the submitter. I know that that preconfigured Ticket Closed email is basically what I am trying to do, however there is no functionality to include the resolution in the email. Basically I would like to clone that, but add the ability to use $resolution. - horstj 12 years ago
Here is the select query for our rule that notifies the user their ticket has been closed:
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,
DATE_FORMAT(HD_TICKET.TIME_CLOSED,'%b %d %Y %I:%i:%s %p') as CLOSED,
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)
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='STATUS_NAME'
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_CHANGE_FIELD.AFTER_VALUE = 'Closed') and HD_TICKET.HD_QUEUE_ID = 2)
Note that this only fires when the ticket status changes to Closed. The queue ID is also 2 for this particular service desk, so you'll need to update that accordingly.
Comments:
-
Thanks for the comment. However, I do not think that this solves my problem of needing to include the resolution in the email. What you have here is very similar to what I have been working with except I added the line "HD_TICKET.RESOLUTION as RESOLUTION, -- $resolution" into my select statement. Does anyone know why the third line of my log (shown in my first post) never resolves to anything? - horstj 12 years ago
-
This query includes HD_TICKET.* so the resolution is included in that. The $resolution variable is used in our message that gets sent to the user. - chucksteel 12 years ago
I did some further checking using the MySQL Workbench and it seems that the line "STATE," is giving me problems. I get an error that says "Error Code: 1052. Column 'STATE' in field list is ambiguous". Can anyone tell me what that line refers to and/or how to fix it?
I figured out that the STATE is a column in HD_STATUS. I changed that and now I get the email and almost everything looks good. The problem is that I am now getting the email for any change made to the ticket (resolution update, status change, etc.). Here is my query right now:
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,
F.AFTER_VALUE AS AFTER_VALUE,
S.NAME AS STATUS, -- $status
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
C.DESCRIPTION, -- $description
HD_TICKET.RESOLUTION as RESOLUTION, -- $resolution
S.NAME AS STATUS_NAME,
S.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
S.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(S.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, -- $owner_name
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, -- $submitter_name
U2.EMAIL as SUBMITTER_EMAIL, -- $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)
/*********** incorporate change tables begin ****************/
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID
LEFT JOIN HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID=C.ID
/*********** incorporate change tables end ****************/
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
JOIN HD_STATUS S ON S.ID=HD_STATUS_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
/********************** detect moment of change begin ***********/
and ((F.FIELD_CHANGED='STATUS_NAME' and F.AFTER_VALUE='Closed') and HD_TICKET.HD_QUEUE_ID = 1)
/********************** detect moment of change end ***********/
How can I change this to only email me when the ticket is closed?
Comments:
-
The major difference I see between your query and mine is that your join for the change is only based on the change_id, which may include other changes. I have the join specify a change type of FIELD_CHANGE='STATUS_NAME', whereas you're doing that in the WHERE part of the query. - chucksteel 12 years ago
-
Thanks, I see what you're saying. I have seen something similar to HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=< CHANGE_ID > (without the spaces, but for some reason it wouldn't let me post it like that) before, but have no clue what it is doing. I changed my code to match yours in that area and KACE does nothing while the MySQL Workbench gives me a syntax error at the = sign on that line. Could you please help me understand what this line is doing? - horstj 12 years ago
-
The table HD_TICKET_CHANGE_FIELD contains before and after values for field changes. If you look at it in MySQLWorkBench you can see a list of all the changes made to ticket fields. The join looks for the change just made based on the CHANGE_ID variable passed to the SQL query in the rule. You can't use that SQL query in MySQLWorkBench because it doesn't know what CHANGE_ID is. If you only make the join based on the CHANGE_ID, then it may match multiple rows in the table, because more than one field may have changed, but if you also add the FIELD_CHANGE='STATUS_NAME' to the join, it will then only find the entry where the status field changed. - chucksteel 12 years ago
-
Thanks for the info! That makes much more sense now. The problem I am now having is that with my rule (even if I copy exactly what you provided above and change the cue id), the rule log says that it selected 0 rows. I know that it is running on save because the log shows that it ran the select query at the exact time that I changed the ticket status to closed and saved it. Is there anything else that I am missing that would cause this? - horstj 12 years ago
-
If the rule is setup and the only thing in the where clause is to match the field changed to Closed and queue is the right number, then it should be working. It's hard to say without looking at it myself why it isn't matching correctly. - chucksteel 12 years ago
-
I finally got this working how we want it! The problem now is that I would like to return a column in the select statement using a subquery based on the described above. Here is my subquery:
(SELECT C1.COMMENT from HD_TICKET_CHANGE C1 where C1.DESCRIPTION='Ticket Created' and C1.HD_TICKET_ID= to '1' it returns what I would expect to see (and do see in the workbench). Is there a reason that the variable is not checking in correctly? - horstj 12 years ago -
I think your comment got truncated, it ends at an equal sign. - chucksteel 12 years ago
-
Sorry, not sure what happened there! Here is my subquery in the select statement:
(SELECT C1.COMMENT from HD_TICKET_CHANGE C1 where C1.DESCRIPTION='Ticket Created' and C1.HD_TICKET_ID=(SELECT C2.HD_TICKET_ID from HD_TICKET_CHANGE C2 where C2.ID=[CHANGE_ID])) as DESCRIPTION,
I know that this should work because when I change the [CHANGE_ID] to any string ('1','9','200',etc) it returns the opening comment of the ticket as desired. Is there some reason that the [CHANGE_ID] variable is not working in this case? All ][ are actually >< but every time I tried putting that in it formatted them and the containing text out of the comment. - horstj 12 years ago -
Is your rule running on save? I'm pretty sure it is, but I'm asking just to be sure. I think that the CHANGE_ID variable is only viable when the rule runs on save.
Also, what exactly are you trying to include in the email? I don't quite understand what you're trying to select. - chucksteel 12 years ago -
Yes, it is running on ticket save. Basically I would like to include the comment that the user put into the ticket when they first created it. This has to be done (I think. If there is a better way please let me know) through a set of nested select statements. The lowest embedded select returns the ticket id (not ticket change id) of the ticket being saved. Then I was using that to select the first comment for the ticket in question. Pretty convoluted, but it should work. - horstj 12 years ago
-
I get the initial comment with the following join:
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
This is the same as the join for the current change except it is based on the lowest change_id for this ticket. I then have the following in my select statements:
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
Then my email includes $initial_comment as the original comment. I also perform a join to the user table using INITIAL_CHANGE.USER_ID to get the name of the first commenter so I can include that in the email, too. - chucksteel 12 years ago -
Thanks so much, that worked perfectly!!! I appreciate all of the help on this and I think everything is working how we want it to... for now :-) This may be a dumb question, but just for clarification, how does that join know which ticket is the current one in question? - horstj 12 years ago
-
HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID. I believe when rules are run on save KACE also adds a where statement so that it matches the currently saved ticket. - chucksteel 12 years ago
-
Got it. Thanks for all the help! - horstj 12 years ago