KACE SMA - Send email to all owners in a queue when a ticket is sitting unassigned
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...
Answers (2)
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
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.
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 - barchetta 4 years ago