K1000 server vertion 5.4.76848

We have all tickets start at the Helpdesk queue and if it is something they can't resolve, they then move it to the appropriate support queue and then that team gets a notification automatically via custom ticket rules. Currently there are two notification rules applied to all queues; one for tickets with an HD_STATUS.NAME of "New" and another for a queue move where we use HD_TICKET_CHANGE.DESCRIPTION LIKE '%que_name%' (que_name = the name of whatever queue the rule is used in). Everything works great except when a ticket has a status of New and is moved to a queue, both ticket rules run and two emails get sent. What I would like to do is have a line in the queue move notification script that looks for the HD_STATUS.NAME to not be equal to "New" in order to be able to execute. This way, since one requires the Status to be "New" and the other to not have a value of "New" it will be impossible for both scripts to excecute at the same time.

I tried adding and HD_STATUS.NAME NOT LIKE '%New%' at the end and the script would just break. Below is the functioning script for queue move notification. What do I need to do to make it not execute if the HD_STATUS.NAME is New?

select HD_TICKET.*,
HD_TICKET.ID as TICKETNUM, -- $ticketnum
HD_TICKET.TITLE as TITLE, -- $title
HD_STATUS.NAME as STATUS_NAME, -- $status_name
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
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, -- $submitter_name
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL, -- $submitter_email
U3.EMAIL as UPDATEREMAIL,
'email@mycompany.com' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
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 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 '%Helpdesk%'

2 Comments   [ + ] Show Comments

Comments

  • what do you mean by 'break' ? Does it say there is an error in the syntax or is it just not returning any rows now?

    I took your SQL and threw it MYSQL and ran it, ran fine (Which is surprising because you have '<CHANGE_ID>' in quotes, which normally breaks my own tickets when put into a ticket rule form.

    I added ' and HD_STATUS.NAME <> 'New' ' to the end of the query and it still ran just fine (although I get no returns, which is expected).


    I'd say try:
    and HD_STATUS.NAME <> 'New'

    give it a shot?
  • and HD_STATUS.NAME <> 'New'
    That worked perfect. Thank you very much!
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity