Anyone know how to create a custom ticket rule that automatically sets a user's manager as a CC on a support ticket?
Goal: When a user opens a ticket, their manager gets copied on the email generated.
Issue: I don't know how to achieve this goal, as I am unfamiliar with how to create custom ticket rules. Quest support directed me to create a custom ticket rule to achieve this goal.
Any assistance is much appreciated.
Answers (1)
Ok so I've taken a quick look at your question, to understand how this should work you need to know the following information about the KACE database
HD_TICKET contains the submitter ID in the SUBMITTER_ID Column, that ID then is used in the USER table and in that table the manager ID of the submitter is recorded in the MANAGER_ID Column.
Once we have the MANAGER_ID we can then lookup that ID in the USER table to get the email address that is then used in the CC_LIST function of the HD_TICKET Table. This also only runs if the CC_LIST field is empty.
So here is an example SELECT Statement for your rule
SELECT HD_TICKET.*, USER.EMAIL AS manager_email
FROM HD_TICKET
JOIN USER ON HD_TICKET.SUBMITTER_ID = USER.ID
JOIN USER AS manager_user ON USER.MANAGER_ID = manager_user.ID
WHERE HD_TICKET.SUBMITTER_ID IS NOT NULL
AND HD_TICKET.HD_QUEUE_ID = 16
AND HD_TICKET.HD_STATUS_ID = 115;
Note : The Queue ID is included to only look at tickets within the specific queue and I have chosen to run the rule only when the ticket is in a new status.
Here is an example UPDATE Statement for your rule
UPDATE HD_TICKET
JOIN USER submitter_user ON HD_TICKET.SUBMITTER_ID = submitter_user.ID
JOIN USER manager_user ON submitter_user.MANAGER_ID = manager_user.ID
SET HD_TICKET.CC_LIST = manager_user.EMAIL
WHERE HD_TICKET.SUBMITTER_ID IS NOT NULL
AND HD_TICKET.HD_QUEUE_ID = 16
AND HD_TICKET.HD_STATUS_ID = 115;
Note : As with the select statement the where statement in the update statement also limits the rule to a queue and status.
Run this rule "On Ticket Save"
If this has been helpful, please comment and let people know, partner KACE Consultants are a dying breed thanks to Quest, so a few days work consultancy helps to keep that knowledge within forums like ITNinja to provide value just like this for all. #WeAreQuest
Comments:
-
Thank you for taking time to assist me with this, Hobbsy. Much appreciated.
I copied and pasted the SELECT and UPDATE statements you wrote up for me, enabled the custom ticket rule, set it to run "On Ticket Save", saved it, then tested it. It did not work.
Is there a delay from when a rule is set like this to when it starts working?
Also, is there a way to double check the SQL database to make sure the statements are using the correct references?
Thank you! - bmcmillan 2 months ago-
Life is never that simple, so you will need to adjust some data in the queries to work for you. You will need to find out the queue ID that you need this to work on and also the status ID for the New status. If you want to jump on a quick call and fix it, just reach out via email. - Hobbsy 2 months ago
