Is there a custom rule that will allow the service desk to notify a submitter that their ticket has been inactive for 1 day?
We are wanting to create a rule that notifies a submitter that their ticket has been inactive, not because it has not been worked, but more so that the owner of the ticket has not heard back from the submitter.
I have read a few articles on here and I have found an SQL script, but I can't get it to work properly. I have changed everything that I can see, although I'm not very good with SQL.
1 Comment
[ + ] Show comment
Answers (1)
Please log in to answer
Posted by:
JasonEgg
7 years ago
There's an open paren on the second to last line in your query you posted in comments:
and (HD_TICKET.HD_QUEUE_ID = 5 It does not require "(" so delete it
A few questions for clarification:
1. Are you only applying this to one queue?
2. Are you sure the only "inactive" status is 'Waiting for User'? (in our environment we have "Need More Info", "New, and "Waiting on Customer")
3. Do you have any idea why the table "ASSET_DATA_20" is included in this query?
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://k1000.plainscommerce.local/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, ASSET_DATA_20)
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 User'))
and DATE_SUB(NOW(), INTERVAL 1 DAY) > DATE(HD_TICKET.MODIFIED)
and (HD_TICKET.HD_QUEUE_ID = 5
GROUP BY HD_TICKET.ID - abratton 7 years ago