/build/static/layout/Breadcrumb_cap_w.png

Ticket Rule: Notify on New Ticket

If you find you cant get a notify rule to work in 5.4 try the following.

To implement a new rule Go to 

Service Desk --> Configuration --> Queues --> Click on your queue name Scroll to the bottom to custom rules and click [Customize]

Select Choose action -> Add new

Simply click next and save through the wizard to get to the input screen and then change your setup as follows.

Give it a name

Run on Ticket Save

***************************SELECT QUERY**********************************

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://kbox/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
'helpdesk@mycompany.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 'TICKET CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

***************************END SELECT**********************************

*************************EMAIL CONTENT*********************************

$submitter_fname,
Your ticket '$title'
Was recieved at $created
TICKET DETAILS
********************
$history
Thanks for your business,
The Help desk Team

**************************END EMAIL************************************

 

The below pic is the setup of your email fields.

 

*NOTE

If you wish to use this as a distribution list then change the line in the SQL select query that reads 

'helpdesk@mycompany.com' AS NEWTICKETEMAIL -- $newticketemail

You will want to resplace with your email address to send to

Then replace the Email Column pictured above from SUBMITTER_EMAIL to NEWTICKETEMAIL

 


Comments

  • Hey JDornan,

    Im having some issues when trying to use the select query. Im getting an error in the SQL around line 41 that im having trouble finding. Can you help? - WhitzEnd 11 years ago
  • That is normally due to not having the Frequency set to On Ticket Save. Change that then hit run now on the rule to test the SQL. - offduty 11 years ago
  • I am having the same problem as Whit,

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=<change_ID>
    /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = H' at line 41]

    I tried your solution, but kept getting the same message. Any ideas? - Kevino2010 11 years ago
    • The article at http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222 has been corrected to have the correct SQL query. - brian_alsobrook 11 years ago
  • It is complaining about AND C.ID=<CHANGE_ID> Specifically KACEs use of <>
    It usually means you are trying to run it outside of KACE or without a change ID being generated. - jdornan 11 years ago
  • Thanks for the help fellas, I was able to get it to work with some tinkering. - Kevino2010 11 years ago
  • Any idea why support would have deleted SOL111222? I went to the site today and it was gone... - reneed33 10 years ago
This post is locked
 
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