KACE Product Support

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!

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. 

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!