Email Notification for Unassigned Tickets
I'm trying to setup a rule that will notify our IT group when there is a ticket that is new and unassigned with a certain priority level after so many minutes. When I try to setup the rule using the wizard, the second step only allows me to change a value. Is there a way to do this? I'm not very good with SQL.
I have when the Priority is Critical and Owner Full Name is Unassigned as the tickets to be affected, but when I go to the next step, I only get the option to change the value to something else. Is there a way to add a custom field for custom rules?
Thanks!
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
Druis
7 years ago
Try this:-
Enter into Select SQL box:
SELECT 'ITgroup@email.com' as MAILGROUP,
HD_STATUS.NAME AS STATUS,
HD_TICKET.ID AS ID,
HD_TICKET.TITLE AS ISSUE,
HD_PRIORITY.NAME AS Priority,
HD_TICKET.CREATED AS CREATED
FROM HD_TICKET
LEFT JOIN USER ON (USER.ID = HD_TICKET.OWNER_ID)
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
WHERE (HD_STATUS.NAME like 'New%' AND HD_PRIORITY.NAME Like 'Medium')
Tick into 'Email each recipient in query results'
Make sure you put MAILGROUP into 'Colum containing email addresses'
Set your frequency to every 15 minutes
Posted by:
svmay
7 years ago
I use this ticketrule for that case:
Create a new custom ticket rule in your queue:
SQL:
SELECT
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
HD_TICKET.SUMMARY, -- $summary
DATE_FORMAT(HD_TICKET.DUE_DATE,'%d.%m.%Y, %k:%i Uhr') AS DUE_DATE, -- $due_date
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://your-kbox.int/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
'yourmail@yourdomain.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 '%CREATED%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Send an e-mail to each recipient in query results
Subject: Column with e-mail addresses:
New Ticket NEWTICKETMAIL
Notification:
$ submitter_fname has created a ticket.
Ticket information:
Ticket ID: [$ ticknum] $ title
$ summary
Category $ category
Priority - $ priority
Impact - $ impact
Ticket completion no later than: $ due_date
Link to the ticket:
http://your-kbox.int/adminui/ticket.php?ID=$ticknum
To Plan
Frequency:
When saving ticket
cheers
Comments:
-
I am working on something similar but am getting syntax errors on each of these statements:
/* 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
It appears that this should be pretty standard for all users. Why would I be getting the following syntax error:
There were syntax errors in your query.
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID' at line 41] - jessburd 7 years ago -
I don't know what you did, but this custom ticketrule has been working for me for a year and a half without any problems. Please check your SQL rule to make sure that nothing has been misspelled or a special character has been inserted incorrectly. - svmay 7 years ago
-
Can you post your code here? - svmay 7 years ago
