I would like to notify support team only when new ticket is created.

can anyone share screen shot or rules for this?
Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • getting the below error

    rror Code: 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 'select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, U1.USER_NAME a' at line 42 0.250 sec
    • Did you make any changes to the code when you created the rule? If so can you post what you have?
Please log in to comment

Answer Chosen by the Author

0
Here is the query I use:
select HD_TICKET.ID, 
HD_TICKET.ID as TICKNUM, 
HD_TICKET.TITLE, 
U1.USER_NAME as OWNER_NAME, 
U3.USER_NAME as LASTINPUTNAME,  
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, 
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, 
HD_STATUS.NAME AS STATUS_NAME, 
HD_STATUS.ORDINAL as STATUS_ORDINAL, 
STATE, 
U1.FULL_NAME as OWNER_FULLNAME, 
U1.EMAIL as OWNER_EMAIL, 
U2.USER_NAME as SUBMITTER_NAME, 
U2.FULL_NAME as SUBMITTER_FULLNAME, 
U2.EMAIL as SUBMITTER_EMAIL, 
U3.EMAIL as UPDATEREMAIL, 
U3.FULL_NAME as UPDATER_NAME,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
HD_TICKET_CHANGE.COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.LOCATION AS SUBMITTER_LOCATION,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET, 
HD_PRIORITY, 
HD_STATUS, 
HD_IMPACT, 
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
 and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID 
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID 
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID  and 
HD_STATUS.ID = HD_STATUS_ID  and 
HD_IMPACT.ID = HD_IMPACT_ID  and 
HD_CATEGORY.ID = HD_CATEGORY_ID  and
HD_TICKET_CHANGE.DESCRIPTION LIKE '%Ticket Created%' 

Answered 04/07/2016 by: chucksteel
Red Belt

  • i had to change <CHANGE_ID> to '<CHANGE_ID>' , thanks for ur help, but where should I mention the email address that needs to receive alert when tickets are created?

    also when i run this query i dont see any output in mysql browser..my queue ID is 16 and no where in code i could see option to include queue no
    • You won't be able to run this query in a MySQL tool because it won't know what <CHANGE_ID> is. The appliance replaces that with the value of the change ID at run time. The appliance should also include the queue ID at run time but if you want to add it place the following line at the end of the query:
      and HD_TICKET.HD_QUEUE_ID = 16

      If you want to notify the ticket owner then the column containing email address would be OWNER_EMAIL. If you want to notify a specific address for every ticket then after HD_QUEUE.NAME AS QUEUE_NAME add this line:
      ,"email@company.com" AS NOTIFYEMAIL

      You would then use NOTIFYEMAIL as the column containing the email address.
Please log in to comment

Answers

Answer this question or Comment on this question for clarity