I'm working on a custom ticket rule that will go out when a ticket is set to a closed status and is saved.  The problem is that for each comment or ticket change completed and saved before the status gets changed to closed, there are that many number of duplicates in the email.  So for example:

" Ticket Number: 4168

  Ticket Title: testing ticket rules v8

  Ticket Status: Closed

  Summary: The following summary gets repeated 12 times

Your ticket has been closed by a technician for one of the
following reasons:
1. Your ticket was in a wating for response status for seven days
without comment and has automatically closed
2. You have declared that the issue has been resolved
3. A technician has completed the request
If you believe the issue has not been resolved please see your ticket at
the above link and add a comment to reopen it. If you agree that the issue
is resolved, please help us provide a higher level of service by providing
your feedback and rating of the service provided for this issue by
completing the User Satisfaction Survey that is now present in your ticket.

History: Each of the following status changes and comments is repeated 4 times in the email

-----Change by *** at 12-10-10 16:22:56 -----

Changed ticket status from "New" to Work in Progress"

Testing ticket rule to see if this comment works

 

-----Change by *** at 2012-10-10 16:21:34 -----

Testing sending comment from user to ticket while status is set to work in progress still

 

-----Change by *** at 2012-10-10 16:21:34 -----

Changed ticket Status from "Work in Progress" to "Closed".

Closing out ticket to see if rule puts comments into proper spot in email. 

 

Here is my SQL coding, thanks for the help:

SELECT
    -- Ticket Fields
    HD_TICKET.ID, -- $id
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
 
    -- Change Fields
    C.DESCRIPTION, -- $description
    S.NAME AS STATUS,
    C.COMMENT, -- $comment
 
    CASE WHEN S.NAME = 'Closed' THEN
       
        GROUP_CONCAT(DISTINCT CONCAT('----- Change by ', UPDATER.FULL_NAME, 'at ',H.TIMESTAMP,' -----\n',
            C.DESCRIPTION,'\n',C.COMMENT,'\n',
            '\n') SEPARATOR '\n') END HISTORY, -- $history

   
    -- About the Udater
    UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
    UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
    UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
    IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
 
    -- about the owner
    OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
    OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
    OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
    IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
 
    -- About the Submitter
    SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
    SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
    SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
 
    -- About Status
    S.NAME AS STATUS,   -- $status     -- About Category
    CAT.NAME AS CATEGORY -- $category
 
  
FROM HD_TICKET
    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE.ID>
    /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
    /* status **********/ JOIN HD_STATUS S ON S.ID = HD_TICKET.HD_STATUS_ID
    /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID = HD_CATEGORY_ID
    /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
    /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID WHERE C.DESCRIPTION NOT LIKE 'TICKET CREATED%'
    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

The first thing that I notice is that your two joins to the HD_TICKET_CHANGE table are identical. The latest change should be this:

 JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID 
 and C.ID=<CHANGE_ID>

The CHANGE_ID variable is sent to the query for the most recent change on the ticket.

Have you turned on the checkbox to have the query results emailed to you? It might help you determine what's going on. I would also run the query in MySQL WorkBench (hard coding a ticket ID and CHANGE_ID). 

Personally I wouldn't have the summary returned in the SQL and I would instead have all of that in the email portion of the ticket rule using the appropriate columns as variables where necessary.

Answered 10/11/2012 by: chucksteel
Red Belt

  • Changed the JOIN statement and pulled the summary from the SELECT statement but I'm still getting multiple copies of the comments in the email.
  • I've also edited the coding in my question to reflect the changes I've made.
  • I can add the $comment directly to the email template, but I am looking to get a comment history in the email instead of just most recent comment
  • Personally I would strip it down to the point where it works as intended, maybe just the ticket information then the most recent comment. Once that is working as intended I would add the functionality to get all comments and see what breaks it (e.g. add the join, make sure it works, add a select statement, etc.).
  • Kind of my thought process as well. Will give that a shot on Monday.
Please log in to comment
Answer this question or Comment on this question for clarity