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

Comments

Please log in to comment

Community Chosen Answer

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



Answered 11/23/2017 by: svmay
Red Belt

  • 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]
  • 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.
  • Can you post your code here?
Please log in to comment

Answers

0

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



Answered 11/22/2017 by: Druis
Third Degree Blue Belt

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