We have multiple queues set up for our various groups (help desk, networking, desktop, applications, etc) with each queue having a different list of owners.  Most of our tickets are funnelled through our Help Desk and then distributed to the other queues. Assignment of specific owners within those queues is left to the owners'.  Queue owners do not currently get a notification when a ticket is added to their queue.  We would like to enable this.

The closest thing I have found is at:

http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk

However, we would have to add all of the owners to the category cc of each category in each group.  A much cleaner and less manual approach would be to use the rule to email members of the queue's owner labels.  I have tried tweaking the SQL, but cannot get the results I seek.  Effectively I want to replace the NEWTICKETEMAIL value with the result of this query:

SELECT USER.EMAIL
FROM HD_TICKET G
INNER JOIN HD_QUEUE_OWNER_LABEL_JT
ON G.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
INNER JOIN USER_LABEL_JT
ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
INNER JOIN USER
ON USER_LABEL_JT.USER_ID = USER.ID
WHERE G.ID=TICKNUM

I have tried using the CONCAT, CONCAT_WS, and GROUP_CONCAT functions to no avail.  Does anyone have any suggestions???

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1

After much hair-pulling, I finally figured it out.  I have the queue owners labels populating the email list.  I also pull the queue name so I can copy the rule to all of our queues without modifying the query.

It emails the queue owners when an unassigned ticket is created in the queue or moved to the queue.  We needed to add this because most of our tickets are created in the Help Desk queue by student aides and then transferred to other queues by staff members.

This rule is run on ticket save.

Select query:

 SELECT
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      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://kbox/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
      -- name of the queue
      HD_QUEUE.NAME AS QUEUENAME, -- $queuename
      -- -- example of static distribution list
      OLIST.EMAIL 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
     /* group email     */
JOIN HD_QUEUE_OWNER_LABEL_JT ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE_OWNER_LABEL_JT.HD_QUEUE_ID
JOIN USER_LABEL_JT ON HD_QUEUE_OWNER_LABEL_JT.LABEL_ID = USER_LABEL_JT.LABEL_ID
JOIN USER OLIST ON USER_LABEL_JT.USER_ID = OLIST.ID
/* queue */
JOIN HD_QUEUE ON HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID

    WHERE
     (C.DESCRIPTION LIKE 'TICKET CREATED%'
or C.DESCRIPTION LIKE '%Changed ticket Queue from%')
and OWNER.USER_NAME is null

     
      /* this is necessary when using group by functions */
    GROUP BY OLIST.EMAIL
    HAVING 1=1

 

Email Subject: 

 [TICK:$ticknum] Assigned to $queuename: $title

Email Column:  NEWTICKETEMAIL

Email Body:

$submitter_fname has opened a ticket.  Please review it online at

http://kbox/adminui/ticket?ID=$ticknum

The submission was:
  Title:  $title
  Ticket:  $ticknum
  Client:  $submitter_fname ($submitter_email)
  Category:  $category
  Priority:  $priority
  Status:  $status
  Severity:  $impact
  Created:  $created
  Modified:  $modified
 

I hope this info helps someone else out there!

Answered 07/03/2012 by: grayematter
Fourth Degree Black Belt

  • Despite having 300 rules in our system ( i love rules) We use the non-rule method in http://www.kace.com/support/resources/kb/article/How-To-Notify-Any-User-or-Group-by-Email-When-a-New-Help-Desk because no matter how much we would like to we simply cannot trust the category the user has chosen. Since we have to review the category 100% of the time (even if to check it's right) we simply don't allow the category to be set and use a the distribution list method on a generic category.

    Can you actually trust the category that user's set?
  • We are using the queue owner labels and NOT categories to identify recipients.

    On creation or move of an unassigned ticket to any queue with this rule, all potential owners for that queue receive notification of ticket creation/movement regardless of ticket category. This allows users (and entire groups, like our programming group) to be notified when an issue has been forwarded to their group. The group can then determine who the ticket owner should be.
  • I would like to try this out, as it appears to be what I am looking for.

    Can you highlight in your script, the places where I would need to modify it to match my Queue Label? I am going to keep looking, but I am not a SQL guy.

    Thanks for the post.
    • I have this exact same script in most queues. It pulls the notification list from the potential owners list, so everyone in all of the Ticket Owners By Label groups would receive the notification. If you want to restrict the recipient list to certain members of the Ticket Owners labels, add to the WHERE clause:
      and OLIST.EMAIL in ('tech1@email.com', 'tech2@email.com')
      where the email addresses are the specific techs you want to be notified. You could also manage this with labels, but that would require a bit more modification of the query.
      • Hello,

        We have this script running, currently, and it it working correctly. We are looking to have a second script run in addition to this script that would send an email to a second email, but only when a ticket is assigned to a specific category. This script would need to work not just at the ticket creation, but also if the category of the ticket is changed after creation.

        Thank you
      • Apeebles: you would need to change the WHERE condition to check for the category change. Look at some examples in your database (specifically HD_TICKET_CHANGE.DESCRIPTION) to figure out exactly what and how you need to filter.
Please log in to comment

Community Chosen Answer

2

As an easier method we setup distribution lists for each category, so we just have to add that distribution list to the category_cc field. I like your idea of querying for the owners label, however. 

Answered 06/12/2012 by: chucksteel
Red Belt

  • I understand that thinking. However, I like to be lazy.....er.....efficient! I don't want to have disparity between a distribution list and the owners labels. The whole point is setting the membership in ONE place.
Please log in to comment

Answers

1

We made this a ticket rule using the SQL below:

 

#Get Ticket ID, Title, Submitter Email, Submitter Full Name
SELECT T.ID AS TICKNUM, T.TITLE AS TITLE, U.EMAIL AS SUBMITTER_EMAIL, U.FULL_NAME AS SUBMITTER_FULLNAME, EMAIL_IDS.EMAIL AS EMAIL FROM HD_TICKET T
#JOIN the HD_STATUS Table
JOIN HD_STATUS S ON (T.HD_STATUS_ID = S.ID)
#Join the USER tabel
JOIN USER U ON (T.SUBMITTER_ID = U.ID)
#Join a sub-select where we gather user email address based on a label (in our "kace" label 464 which is our ticket owners label)
JOIN (SELECT EMAIL FROM USER_LABEL_JT
JOIN USER ON USER_LABEL_JT.USER_ID= USER.ID
WHERE LABEL_ID =  464) AS EMAIL_IDS #Save it all as the EMAIL_IDS alias name
#In this where we check he time to make sure it is M-F 8:00am-4:30pm our standard working hours. If it isn't in that timeframe, don't email us!
WHERE  HOUR(NOW()) > 8 AND HOUR(NOW()) < 16.5 AND DAYOFWEEK(CURDATE()) != 7 AND DAYOFWEEK(CURDATE()) != 1
#In this line we make sure that the ticket itself was created during our standard hours. We have different SLAs on tickets created out of standard hours
AND T.HD_QUEUE_ID = 1 AND HOUR(T.CREATED) >= 8 AND HOUR(T.CREATED) <= 16.5 AND T.OWNER_ID = 0 AND T.HD_STATUS_ID = 4 AND S.STATE != 'closed'
#Finally here we check to see if the ticket was created within the last hour OWNERID 0 is "Unassigned" and the ticket can't be closed.
AND TIME_TO_SEC(TIMEDIFF(NOW(),T.CREATED)) >= 3600 AND TIME_TO_SEC(TIMEDIFF(NOW(),T.CREATED)) <= 7200 AND T.OWNER_ID = 0 AND S.STATE != 'closed'

I'm sure you can fiddle with it from here to meet your needs.  As for the rest of the ticket rule screen.

Frequency:  Hourly

Results are tickets add a comment to each one.  We add a comment denoting which ticket rule fired off as an owner's only comment.  This helps with ticket rule looping.

Send an email for each result row

Subject:  <Subject line you want here>

Email Column:  EMAIL (It has to be the word EMAIL as written here as that is the column we gathered in that sub-select based on the user label)

Email Body:  <Email body you wish to use>

 

Hope this helps!

Answered 06/12/2012 by: lind.fedora@gmail.com
Senior Yellow Belt

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

Share