/build/static/layout/Breadcrumb_cap_w.png

[SOLVED] K1000 Service Desk Auto-Populate (and e-mail) CC_List

So I have been working to try to duplicate the idea found in this thread:  http://www.itninja.com/question/auto-populate-cc-list

 

I have configured it so that the ticket submitter's manager is autopopulated into the CC_List on the ticket save, however it seems to be doing so at the very end of the entire process, so on the ticket creation, the manager is not being e-mailed.  I have checked the CC_List e-mail settings and have it set to notify the CC_List on any change, but still no dice on the ticket creation.  I have no experience whatsoever with SQL and the custom ticket rules all look like heiroglyphics to me, so my troubleshooting in this endeavor is extremely limited.  Does anyone know if there is an easy fix to make the ticket rule that populates the CC_List on ticket save also ensure that an e-mail is sent on ticket save?  I was thinking if I could come up with that, then I could just clear the e-mail boxes for CC_List and the problem would be solved?


1 Comment   [ + ] Show comment
  • Hi All,

    How can i pull AD/Windows login name

    in ticketing field before or after ticket creation? - rahimpal 8 years ago

Answers (6)

Answer Summary:
Okay, so this cannot possibly be the most efficient method but it was the only way I could get everything to work exactly how I wanted it. To accomplish this, I created 3 custom ticket rules. We do not have manager e-mail addresses stored in our AD but we do have the user's manager's distinguished name, so during user import I imported the user's distinguished name into user custom field 1 and their manager's distinguished name into custom field 2. For my first ticket rule I ran a query to find a match of custom field 2 for the user to another user's custom field 1. It then grabbed the e-mail field of the matching row and placed that in the tickets custom field 1. This rule was modeled after terabyte's rule in his original quesiton here: http://www.itninja.com/question/auto-populate-cc-list The second rule places custom field 1 of the ticket into the ticket CC field for future ticket updates through the CC checkbox options in the ticket settings. It followed the logic of GillySpy's rule on the same article from above, making sure that the e-mail is not already in the CC list and if there is another e-mail address in the CC list then the manager e-mail should be added in addition to the existing CC list. However since this was happening on ticket save it wasn't sending out the initial email on ticket creation. Thus I wrote a 3rd ticket rule. The third rule sends a customized e-mail and is an exact duplicate of the rule found in this article: http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk?action=artikel&cat=8&id=613&artlang=en except that instead of hardcoding an e-mail address on the line 'helpdesk@mycompany.com' AS NEWTICKETEMAIL I used HD_TICKET.CUSTOM_FIELD_VALUE1 AS NEWTICKETEMAIL. The reason for doing the initial e-mail using the custom field value was because if I were to use the CC_FIELD some people could end up getting e-mailed twice, once for the default CC_FIELD behavior and then again because of the custom ticket rule. This way people that the user specified in the CC_FIELD receive the normal e-mail and the email for the supervisor gets sent out by the rule. On future changes they all get the same e-mail. Sorry if this is incredibly ridiculous sounding, I just had very specific instructions and not much to work with so I just combined a bunch of different elements. If you want to see the actual custom rules I will post them below in the comments. Thanks, Andrew
Posted by: jverbosk 11 years ago
Red Belt
1

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

John

Posted by: chucksteel 11 years ago
Red Belt
1

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. 


Comments:
  • That's the problem, I'm such a novice at SQL I can't even really get a functional rule to send the e-mail after the cc_list is populated. I've been trying to pick and pull from http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk?action=artikel&cat=8&id=613&artlang=en but haven't had any luck. - ais4ocho 11 years ago
Posted by: ais4ocho 11 years ago
Senior Yellow Belt
1

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>) 

Posted by: ais4ocho 11 years ago
Senior Yellow Belt
1

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>) 

Posted by: ais4ocho 11 years ago
Senior Yellow Belt
1

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.

Posted by: ais4ocho 11 years ago
Senior Yellow Belt
0

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.


Comments:
  • Yes please do post your solution! - bkelly 11 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ