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! 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

If you look at your Queue>Service Desk Configuration> Email on Events: [Customize Emails] > Ticket Closed on Submitter is it checked? 

Answered 04/23/2012 by: Aaron Cool
Red Belt

  • 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.
Please log in to comment
1

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. 

 

Answered 04/24/2012 by: chucksteel
Red Belt

  • 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?
  • 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.
Please log in to comment
1

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?

Answered 04/24/2012 by: horstj
Fourth Degree Green Belt

Please log in to comment
1

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?

Answered 04/24/2012 by: horstj
Fourth Degree Green Belt

  • 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.
  • This content is currently hidden from public view.
    Reason: Removed by user request
    For more information, visit our FAQ's.
  • 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?
  • 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.
  • 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?
  • 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.
  • 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?
  • I think your comment got truncated, it ends at an equal sign.
  • This content is currently hidden from public view.
    Reason: Removed by user request
    For more information, visit our FAQ's.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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?
  • 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.
  • Got it. Thanks for all the help!
Please log in to comment
Answer this question or Comment on this question for clarity