/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: Druis 6 years ago
Third Degree Green Belt
1

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 6 years ago
Red Belt
1
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 6 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 6 years ago
  • Can you post your code here? - svmay 6 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ