KACE Product Support

This is a base ticket rule template I use, you can replace the WHERE filters as needed.

Select Query:

 SELECT
          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,
          S.NAME AS STATUS, 
          I.NAME AS IMPACT, 
          CAT.NAME AS CATEGORY 
        FROM HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.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 ((S.NAME = 'Waiting for Ticket Owner'))
                       and DATE_SUB(NOW(), INTERVAL 1 DAY) > DATE(HD_TICKET.MODIFIED) 
                       and HD_TICKET.HD_QUEUE_ID = 1
and TIME(NOW())<'18:00:00' and TIME(NOW())>'05:00:00'
and DAYOFWEEK(NOW()) in (2,3,4,5,6)
        GROUP BY HD_TICKET.ID

Update Query:

 update HD_TICKET
Set HD_TICKET.MODIFIED = NOW()
WHERE (HD_TICKET.ID in (<TICKET_IDS>))

This is how the email is setup:

Subject: [TICK:$ticknum]$title

Email Column: OWNER_EMAIL  

NOTE::  Email column is hard coded with a $, if you want to put in an actual email, then you have to add it and alias it within the SELECT QUERY.

Email:Hello $owner_fname,

You are receiving this email because a ticket submitted to the Service Desk is currently waiting for your response.  If you can, please take the time to visit your ticket at:

http://YOURURL/adminui/ticket.php?ID=$ticknum

and update the status of your ticket.

Thank You,
KACE Auto-Reminder System ||||

THIS EXAMPLE ALSO GIVES YOU BUSINESS HOURS TO WORK WITHIN.  Play with it as you need.

Also:  I have it set to run every 15 minutes.  If you do ticket save, someone must open and update the ticket in order to run the query.  The update query updates the modified field so the rule timer resets.  

If you have any questions, throw them out


The biggest thing to know is the EMAIL COLUMN is hardcoded with a $ which is aliased in the select query.  this took a ticket to KACE to find out.

If you want to Alias an email and add it to the query, you can do the following:

 '[email=user@companyname.com]EXAMPLE@EMAIL.COM[/email], EXAMPLE@EMAIL.COM' as EXAMPLEEMAIL

I add this line in right before the FROM statement (don't forget a comma on the line before)