Greetings all,

A few weeks ago with everyone's help I was able to configure email notifications for newly created tickets so that only technical resources for a specific problem category would be notified. This works well, and has the potential to drasticly reduce helpdesk "spam" for many of our techs and programmers (who, with our old helpdesk system got an email about every single ticket, regardless of content).

Some of our resource groups have gotten into the habit of... lets say... forgetting that tickets have been submitted that require their attention. The old excuse about tickets getting lost in a barrage of emails isn't valid anymore, but what I would like to do is have a "reminder" email notification if tickets sit around with the owner set to "Unassigned" for more than a day. 

 

Here is my current "new ticket" notification rule:

   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://vk1000.****.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, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
-- -- static distribution list
'email1@****.com,email2@****.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%'
AND
CAT.NAME LIKE 'Software%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

 

I'm reasonably sure that this can be modified to meet my needs, I'm just not quite sure how to go about doing so.

from playing around with the rule creation wizard (not exceedingly helpful) I'm thinking I need to add something like this:

  (((  (1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.FULL_NAME = 'Unassigned')) ) AND HD_TICKET.TIME_OPENED > '1d') and HD_TICKET.HD_QUEUE_ID = 1 )

and then just modify the email that's sent to reflect an unassigned ticket as opposed to a new ticket.

 

Any ideas? Thanks in advance!

Answer Summary:
John's ticket rule worked fantastic, with a few tweaks for my organization. Thanks John!
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

Here's a tweak on what I'm using.

Hope that helps!

John

___________________

Create this ticket rule in Queue 1.

Title:
Unassigned Tickets

Order:
11

Notes:
Sends an email when a ticket has sat in queue longer than specified period (1 hour).

Frequency:
15 minutes

Select Query:
Select 'it@company.com' as SUPPORT, T.ID as ID, T.TITLE as Issue,
S.FULL_NAME as Submitter, T.CREATED as Created
FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)
AND T.OWNER_ID = 0
ORDER BY T.ID ASC
______________________________

X Send an email for each result row

Subject:
ATTENTION - Unassigned Ticket in Queue

Email Column:
SUPPORT

Email Body:
Unassiged ticket in one of the queues, please review.

Created:    $created
Ticket ID:    $id
Issue:        $issue
Submitter:    $submitter

Answered 01/30/2013 by: jverbosk
Red Belt

  • Thanks John, I've got a couple test tickets running right now, I'll let you know how it works out.
  • eyy Jverbosk!! i have one doubt and problem. my rule sends the notification every 1:30h if nobody has done anything yet (i have it OTS), but i just want ONE notification. how can i do it?? or is it because of OTS??? Thanks!!!
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Member has been banned from the site
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity

Share