I used this KB article to create a custom rule to email the technical group when a ticket is in the queue, https://support.software.dell.com/kb/111222.  Is there a way to add a value from a custom field to an email notification?  For example our users choose a building location.  When the notification email comes through to the technician, I want the location on that email.  Is that possible?  The field is Custom_field_value0 or Custom_1.
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
You need to add the custom field to the select query in order for it to be available as a variable in the email. In the select SQL find the line that begins with FROM. If you followed the KB article you listed then it should read FROM HD_TICKET. The line above that is something like 'helpdesk@mycompany.com' AS NEWTICKETEMAIL -- $newticketemail where you should have customized the email address. Add a comma to the end of that line and add this:
HD_TICKET.CUSTOM_FIELD_VALUE0 as BUILDING

You can now use $building in your email. You can add other custom fields by including them in the same way, just be sure to separate the fields with a comma.
Answered 04/27/2015 by: chucksteel
Red Belt

  • like this I'm assuming.

    SELECT
    -- ticket fields
    HD_TICKET.ID, -- $id
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_TICKET.TITLE, -- $title
    DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
    DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    -- change fields
    C.COMMENT, -- $comment
    C.DESCRIPTION, -- $description
    GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kaceassets.sboe.org/adminui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
    ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

    -- about the updater
    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 priority
    P.NAME AS PRIORITY, -- $priority

    -- about status
    S.NAME AS STATUS, -- $status

    -- about impact
    I.NAME AS IMPACT, -- $impact

    -- about category
    CAT.NAME AS CATEGORY, -- $category

    -- other fields

    -- -- example of static distribution list
    'xxx@abc.net' AS NEWTICKETEMAIL -- $newticketemail,

    HD_TICKET.CUSTOM_FIELD_VALUE0 as BUILDING

    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
    /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
    /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
    /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_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 LIKE 'TICKET CREATED%'
    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1


    I added that line but when I get the email, it only says Building: $building in the email.
    • The syntax is correct, I'm not sure why the value isn't getting populated. You might want to try checking the "Email results" box and having the results of the query sent to you. This helps troubleshoot sometimes.
      • Ok, I figured it out. There had to be an issue with the SQL statement itself. I just deleted my test queue and created a new one and everything worked perfectly....thank you.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity

Share