SQL for custom Ticket Rule
Here is the situation;
We have multiple ticket Queues, however we only have 1 main queue that ALL employees submit ticket into..
The IT department then moves any tickets that need to go into our secondary Queue..
The owners of the secondary Queue never get any E-mail when the IT department moves the ticket to their Queue..
The custom ticket rule we are trying to use is from https://www.itninja.com/question/kace-service-desk-custom-ticket-rule-to-notify-when-ticket-enters-a-queue
however no matter what I try, no e-mail is getting sent out when a ticket is moved form our main Queue (ID 1) to this Queue (ID 6)
And of course Kace support will not assist, except to say to search IT Ninja.. but when you have no idea what these term even mean in the SQL script.. or what all needs to be set in order for e-mail to be sent for the Queue,
kinda hard to figure out what needs to be tweaked..
Any assistance is much appreciated!
Jason
Answers (2)
Maybe try something like this for the custom ticket rule:
select
HD_TICKET.ID,
HD_TICKET.ID ticket_number, -- $ticket_number
HD_TICKET.TITLE ticket_title, -- $ticket_title
SUBMITTER.FULL_NAME ticket_submitter_name, -- $ticket_submitter_name
SUBMITTER.EMAIL ticket_submitter_email, -- $ticket_submitter_email
CAT.NAME ticket_category, -- $ticket_category
IMPACT.NAME ticket_impact, -- $ticket_impact
PRIORITY.NAME ticket_priority, -- $ticket_priority
STATUS.NAME ticket_status, -- $ticket_status
HD_TICKET.HTML_SUMMARY, -- $html_summary
group_concat(OWNERS.EMAIL) as EMAILCOLUMN
from
HD_TICKET
join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID
and C.ID = <CHANGE_ID>
left join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID
left join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID
left join HD_IMPACT IMPACT on HD_TICKET.HD_IMPACT_ID = IMPACT.ID
left join HD_PRIORITY PRIORITY on HD_TICKET.HD_PRIORITY_ID = PRIORITY.ID
left join HD_STATUS STATUS on HD_TICKET.HD_STATUS_ID = STATUS.ID
join HD_QUEUE_OWNER_LABEL_JT HDQOLJT on HD_TICKET.HD_QUEUE_ID = HDQOLJT.HD_QUEUE_ID
join USER_LABEL_JT ULJT on HDQOLJT.LABEL_ID = ULJT.LABEL_ID
join USER OWNERS on ULJT.USER_ID = OWNERS.ID
where
C.DESCRIPTION like '%Ticket Created%'
Then have it email each recipient. You will also need to use "EMAILCOLUMN" as the "column containing email address".
Comments:
-
This will only trigger new tickets, not tickets moved from one queue to another. - chucksteel 4 years ago
The change description that the source rule is looking for isn't catching the correct tickets. Find this line:
C.DESCRIPTION LIKE 'Changed Ticket Queue%'
And change it to:
C.DESCRIPTION LIKE '%ticket Queue from%'
Also, make sure that the rule is in the destination queue.
