Miscellaneous

Thanks for taking the time to document and share this, good stuff!!!

John

You might need to adjust the order that rules run in. Make sure that your rule that populates the cc_list field has a lower order number than the rule that sends the email. 

Custom ticket rule 1 for finding the manager's e-mail, remember we don't have the e-mail address in AD, only the DNs so this is super roundabout (user's DN is imported into CUSTOM_2, user's manager's DN is imported into CUSTOM_3):

Name: Set Ticket Custom Field 1 to Manager E-Mail

Order: 75

Frequency: Ticket Save

Select Query: SELECT HD_TICKET.* FROM HD_TICKET WHERE HD_TICKET.HD_QUEUE_ID = 1 

Update Query: Update HD_TICKET as T

SET T.CUSTOM_FIELD_VALUE1 = (SELECT M.EMAIL FROM USER U INNER JOIN USER M ON U.CUSTOM_2 = M.CUSTOM_3 WHERE (((U.ID)=T.SUBMITTER_ID))) WHERE T.ID in (<TICKET_IDS>) 

Custom ticket rule 2 for adding Ticket Custom Field 1 to CC List:

Name: Add Ticket Custom Field 1 to CC List w/ Rules

Order: 100

Frequency: Ticket Save

Select Query: SELECT HD_TICKET.* FROM HD_TICKET WHERE HD_TICKET.HD_QUEUE_ID = 1 

Update Query: update HD_TICKET as T

SET T.CC_LIST = TRIM(',' FROM CONCAT(T.CC_LIST,',',T.CUSTOM_FIELD_VALUE1)) WHERE LOCATE(T.CUSTOM_FIELD_VALUE1,T.CC_LIST)=0 AND T.ID in (<TICKET_IDS>) 

Custom ticket rule 3 for e-mailing Ticket Custom Field 1:

Name: Email Supervisor on Creation

Order: 125

Frequency: Ticket Save

Select Query:

SELECT

      HD_TICKET.ID,

      HD_TICKET.ID AS TICKNUM,

      HD_TICKET.TITLE,

      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,

      C.COMMENT,

      C.DESCRIPTION,

      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY,

      UPDATER.USER_NAME AS UPDATER_UNAME,

      UPDATER.FULL_NAME AS UPDATER_FNAME,

      UPDATER.EMAIL AS UPDATER_EMAIL,

      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL,

      OWNER.USER_NAME AS OWNER_UNAME,

      OWNER.FULL_NAME AS OWNER_FNAME,

      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER,

      SUBMITTER.USER_NAME AS SUBMITTER_UNAME,

      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME,

      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,

      P.NAME AS PRIORITY,

      S.NAME AS STATUS,

      I.NAME AS IMPACT,

      CAT.NAME AS CATEGORY,

      HD_TICKET.CUSTOM_FIELD_VALUE1 AS NEWTICKETEMAIL

    FROM HD_TICKET

     JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

                            AND C.ID=<CHANGE_ID>

     JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

     JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID

     JOIN HD_STATUS S ON S.ID=HD_STATUS_ID

     JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID

     JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID

     LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID

     LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

     LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

    WHERE

     C.DESCRIPTION LIKE 'TICKET CREATED%'

    GROUP BY HD_TICKET.ID

    HAVING 1=1

Checked box for Send email for each result row and entered the following:

Subject: [TICK:$ticknum] NEW TICKET: $title

Email Column: NEWTICKETEMAIL

Email Body:

$submitter_fname has opened a ticket.  Please click here to reply to this email 

<mailto:servicedeskname@k1000.companystuff.com?subject=[TICK:$ticknum]> or review it online at 

http://kbox.whateveryourcompanyis.com/userui/ticket?ID=$ticknum

The submission was:

Ticket: $ticknum

From: $submitter_fname ($submitter_email)

Category: $category

Priority: $priority

Status: $status

Severity: $impact

Opening Comment: $comment

**You have received this message because you are listed as the supervisor of $submitter_fname.  If you have received this message as an error or no longer wish to receive issues relating to $submitter_fname please contact the Service Desk Administrator.

I finally pieced together something that worked, my issues were mostly just my inability to really understand the process, as I started taking it apart piece by piece I found a solution.  I will post my solution later on should anyone be interested.