We recently moved to Dell Kace within the last few months. We are now to the point of implementing the service desk, and I need help with one aspect. We are setting the medium priority items to where they can be worked on during times that the higher priority items are closed out. Basically, medium priority will be small projects, as such I would like a way to set up email reminders to the owners of those tickets to ensure that they are updating them periodically. I have tried creating a new email rule, but it is not working so far. Does anyone know of a tutorial online that shows how to do this so I can figure out the step I am missing? Thanks.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

 

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.

 

Answered 08/05/2013 by: Wildwolfay
Red Belt

  • I just wanted to chime in and say this works great. I set mine to 15 days instead of 1, and we are using it in our environment.
Please log in to comment
1

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)

Answered 08/05/2013 by: Wildwolfay
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity