/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


KACE SMA - Send email to all owners in a queue when a ticket is sitting unassigned

09/24/2020 909 views

Anyone have a rule to setup on a scheduled basis to all email addresses within the owners label(s) of a queue?  I found one that does on ticket save but cant seem to make it work when I try to schedule it.  I hobbled one rule which sends out alerts on urgent tickets unassigned to a  DL email but I dont want to create a DL for each of our queues and maintain them all.  

So ideally, it would run every 2 hrs, look for unassigned tickets in a queue and then email all members of the labels assigned as owners for that queue.  

Would appreciate any assistance... 

3 Comments   [ + ] Show comments

Comments

  • Please post the rule that you have that works on ticket save.
  • I figured it out by luck. Im a SQL HACK big time. Dangerous in fact. What I did is created a report with search criteria needed. Then I found a script someone wrote to pull in label emails. Pasted in and got lucky. I then limited the update to the queue only. Can someone tell me if Ive done something dangerous? Like maybe for some reason it runs on all queues or something and sends 1458 emails?

    SELECT

    HD_TICKET.CREATED,
    HD_PRIORITY.NAME AS PRIORITY,
    HD_TICKET.TITLE,
    HD_TICKET.ID AS TICKNUM, -- $ticknum
    HD_CATEGORY.NAME AS CATEGORY,
    S.FULL_NAME AS SUBMITTER_NAME,
    Q.NAME AS QUEUE_NAME, O.FULL_NAME AS OWNER_NAME,
    HD_TICKET.DUE_DATE, HD_TICKET.MODIFIED, MACHINE.NAME AS SYSTEM_NAME,
    HD_TICKET.ID as TOPIC_ID , CONCAT('TICK: ', HD_TICKET.ID) AS Number,
    OLIST.EMAIL AS NEWTICKETEMAIL, -- $newticketemail
    CAT.NAME AS CATEGORY, -- $category
    HD_STATUS.NAME AS STATUS


    FROM

    HD_TICKET
    LEFT JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
    LEFT JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
    JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
    LEFT JOIN MACHINE ON (MACHINE.ID = HD_TICKET.MACHINE_ID)
    LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS
    and HD_TICKET.HD_SERVICE_STATUS_ID
    and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID
    LEFT JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
    JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_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

    (HD_TICKET.HD_QUEUE_ID = '5') AND (((HD_CATEGORY.NAME = 'Employee Offboard') OR HD_CATEGORY.NAME = 'Employee Onboard')
    AND (IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT,
    HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) = 'Opened')
    OR (IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT,
    HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) = 'New')
    AND ((TIMESTAMP(HD_TICKET.MODIFIED) > NOW()
    OR TIMESTAMP(HD_TICKET.MODIFIED) <= DATE_SUB(NOW(),INTERVAL 5 HOUR))))


    Email config is:
    Subject: [TICK:$ticknum] Reminder: $title
    Column: NEWTICKETEMAIL
  • By the way, what is cool about this script for those that send rule based Q notifications is it eliminates my need to maintain distribution lists for all queues. I can always be sure all owners of a Q are going to get the notification.

All Answers

0

Here is a rule I have setup to email anyone that is labeled - "HelpDesk - Ticket Owners".  Mine is also set up as On Save, but it should work on an interval as well.  One thing you should check is that your Where condition is still TRUE when the timer runs.  For example, on my rule I have it setup so the status has to be "New" and I have the rule setup as one of my last to fire.  The issue with your rule not firing could be a status or something in the Where condition is not true because another rule or something changed a condition.


Select SQL

============================

Select

  HD_TICKET.*,

  HD_STATUS.NAME As STATUS_NAME,

  HD_IMPACT.NAME As IMPACT_NAME,

  HD_CATEGORY.NAME As CATEGORY_NAME,

  HD_PRIORITY.NAME As PRIORITY_NAME,

  HD_STATUS.STATE,

  U2.FULL_NAME As SUBMITTER_FULLNAME,

  U2.EMAIL As SUBMITTER_EMAIL,

  Q.NAME As QUEUE_NAME,

  (Select

    Group_Concat(USER.EMAIL Separator ', ') As ADDRESSLIST

  From

    LABEL Inner Join

    USER_LABEL_JT On USER_LABEL_JT.LABEL_ID = LABEL.ID Inner Join

    USER On USER.ID = USER_LABEL_JT.USER_ID

  Where

    LABEL.NAME = 'HelpDesk - Ticket Owners') As GROUPMAIL

From

  HD_TICKET 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.APPROVER_ID Left Join

  HD_QUEUE Q On Q.ID = HD_TICKET.HD_QUEUE_ID Left Join

  MACHINE M1 On M1.ID = HD_TICKET.MACHINE_ID,

  HD_PRIORITY,

  HD_STATUS,

  HD_IMPACT,

  HD_CATEGORY

Where

  HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And

  HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And

  HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And

  HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And

  HD_TICKET.OWNER_ID = 0 And

  HD_TICKET.HD_QUEUE_ID = 1 And

  HD_STATUS.NAME = 'New'

=============================================

Email each recipient in query results = enabled
Subject = [TICK:$id] Unassigned Ticket: $title
Column containing email addresses = GROUPMAIL

Message:

-+-+- Please reply above this line to add a comment -+-+-

A new ticket was unable to be assigned to a Ticket Owner. For complete details, see: https://myhelpdesk.com/adminui/ticket?ID=$id


Submitter: $submitter_fullname

Email: $submitter_email

Category: $category_name

Impact: $impact_name

Priority: $priority_name


Summary:

$summary

Answered 09/26/2020 by: JordanNolan
9th Degree Black Belt

-1

If you check out the HD_TICKET table you will see HD_TICKET.HD_OWNER_ID which is where the owner I’d is stored. Admin ID is always 10, but if the ticket is unassigned this column will not contain a number. You could build a rule using the WHERE statement is if Owner_id <> 0 or 10 and that will also work in triggering an email. To make life easier store email addresses as default values in hidden custom fields, so you can easily load them as a variable in the ticket rule for the target email.

Answered 09/27/2020 by: Hobbsy
Red Belt

Don't be a Stranger!

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

Sign up! or login

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