/build/static/layout/Breadcrumb_cap_w.png

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


0 Comments   [ + ] Show comments

Answers (2)

Posted by: rruhl 5 years ago
Orange Senior Belt
1

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 5 years ago
Posted by: chucksteel 5 years ago
Red Belt
0

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.



Don't be a Stranger!

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

Sign up! or login

View more:

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