/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Ticket Rules Question

02/03/2020 143 views

I have a question about ticket rules. I have made a few custom fields in my queue and now I want to send an email to the tech every time a ticket is created but I want some of those values from the custom fields to be send in the email.

I have tried to play with it and asked for help from support but they tell me its custom and I would need to pay extra to get help (which I do not agree with but okay)

All I need to know is how to get these fields to populate in the message.

This is my select statement:

 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


HD_TICKET.CUSTOM_FIELD_VALUE0 AS BackgroundCheckCleared,

HD_TICKET.CUSTOM_FIELD_VALUE1 AS DateofHire,

HD_TICKET.CUSTOM_FIELD_VALUE2 AS FirstName,

HD_TICKET.CUSTOM_FIELD_VALUE3 AS Building,

HD_TICKET.CUSTOM_FIELD_VALUE4 AS Department,

HD_TICKET.CUSTOM_FIELD_VALUE5 AS Title,

HD_TICKET.CUSTOM_FIELD_VALUE6 AS DoorAccess,

HD_TICKET.CUSTOM_FIELD_VALUE7 AS Picture,

HD_TICKET.CUSTOM_FIELD_VALUE8 AS AdditionalInformation,

HD_TICKET.CUSTOM_FIELD_VALUE9 AS LastName,


      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://kace.com/userui/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


      'email@email.com' AS NEWTICKETEMAIL -- $newticketemail


    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 '%CREATED%'


      /* this is necessary when using group by functions */


    GROUP BY HD_TICKET.ID


    HAVING 1=1


And this is my message:

Ticket: [TICK:$ticknum]


Badge First Name: $FirstName

Badge Last Name: $LastName

Badge Building: $Building

Badge Department: $Department

Badge Title: $Title

Door Access: $DoorAccess

Date of Hire: $DateofHire

Picture Location: $Picture

Additional information: $AdditionalInformation


It's not working, can anyone explain to me why?

1 Comment   [ + ] Show comment

Comments

  • Thak you sooo much for this. I never once thought about the lower case being a problem, you both made me very happy!!

All Answers

0

First I would try creating a basic rule using the wizard, let KACE put in all the lines to the query it needs, then I suggest you add in your fields and copy the format of the wizard select statement. Just as an idea also change the names of the custom fields in the select statement so there is no confusion with the default field titles in the database (eg Title). 

Try this with just one or two of your fields and then insert them into the template, with the $variables all in lower case.

if that works, keep adding in select lines and $variables until you have what you need.

If it doesn’t work, let me know what version you are as it may yet still be a bug ;o)

Answered 02/03/2020 by: Hobbsy
Red Belt

0

The variables need to be lower case.

Badge First Name: $firstname

Badge Last Name: $lastname

Badge Building: $building

Badge Department: $department

etc.

Answered 02/04/2020 by: chucksteel
Red Belt

 
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