Auto-populate CC_LIST
In our environment, I want all tickets to have someone monitoring them to ensure they're completed. The solution was to auto-populate the CC_LIST field with the ticket owner's manager (or whomever is responsible for monitoring the ticket queue). This way owners don't have to remember to select a CC each time a ticket is created. I also ensured escalation emails are sent to the CC_LIST (which they are by default, I think).
Here's my solution, using a Custom Ticket Rule, that hopefully will be helpful to someone else.
1. Enter the manager's email address in the 'Custom 1' field for each owner ('Help Desk', 'Users', Select the user to edit)
2. Set the Frequency of the Ticket Rule to 'on Ticket Save'
3. Create a new Ticket Rule with these queries:
Select Query:
SELECT HD_TICKET.* FROM HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 1
Update Query:
update HD_TICKET as T
SET T.CC_LIST = (SELECT U.CUSTOM_1 FROM USER AS U WHERE U.ID = T.OWNER_ID)
WHERE T.CC_LIST = ' AND T.ID in (<TICKET_IDS>)
This rule will populate the CC_LIST field with whatever email address is in the 'CUSTOM_1' field of a user. Obviously, use whatever custom field is available for your environment. The CC_LIST field will only be updated if it's empty, thereby allowing owners to select a CC if they want to.
Let me know if you have any improvements to the idea.
Here's my solution, using a Custom Ticket Rule, that hopefully will be helpful to someone else.
1. Enter the manager's email address in the 'Custom 1' field for each owner ('Help Desk', 'Users', Select the user to edit)
2. Set the Frequency of the Ticket Rule to 'on Ticket Save'
3. Create a new Ticket Rule with these queries:
Select Query:
SELECT HD_TICKET.* FROM HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 1
Update Query:
update HD_TICKET as T
SET T.CC_LIST = (SELECT U.CUSTOM_1 FROM USER AS U WHERE U.ID = T.OWNER_ID)
WHERE T.CC_LIST = ' AND T.ID in (<TICKET_IDS>)
This rule will populate the CC_LIST field with whatever email address is in the 'CUSTOM_1' field of a user. Obviously, use whatever custom field is available for your environment. The CC_LIST field will only be updated if it's empty, thereby allowing owners to select a CC if they want to.
Let me know if you have any improvements to the idea.
1 Comment
[ + ] Show comment
Answers (5)
Please log in to answer
Posted by:
shandy4473
9 years ago
This doesnt seem to work on Kace K1000 version 6.2. The CC field doesn't seem to populate as easy.
Comments:
-
I've the same problem on 6.3, is there a solution or another SQL request ?
Ty ! - Crash73 8 years ago
Posted by:
airwolf
13 years ago
That's exactly what I would've done. Great job! I can't think of a more efficient way to do this. You could use categories, but that would either leave you with very generic categories or a very large list of several duplicate categories under each owner.
Posted by:
GillySpy
13 years ago
This is great. I've seen someone use a user asset that is tied to the user and set the manager there. It's intuitive to update cuz the field is called "manager" but the query becomes hairy.
One slight mod for this one to keep any existing cc in place
One slight mod for this one to keep any existing cc in place
update HD_TICKET as T JOIN USER U ON OWNER_ID=U.ID and CUSTOM_1<>'
SET T.CC_LIST = TRIM(',' FROM CONCAT(CC_LIST,','(U.CUSTOM_1) ) )
WHERE LOCATE(U.CUSTOM_1,CC_LIST)=0 /*if the email isn't already there*/
AND T.ID in (<TICKET_IDS>)
Comments:
-
Hello GillySpy - I am getting the following error in KACE when trying to execute the update query:
mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' FROM CONCAT(CC_LIST,','(U.CUSTOM_1) ) ) WHERE LOCATE(U.CUSTOM_1,CC_LIST)=0 /' at line 1] in EXECUTE("update HD_TICKET as T JOIN USER U ON OWNER_ID=U.ID and CUSTOM_1<>' SET T.CC_LIST = TRIM(',' FROM CONCAT(CC_LIST,','(U.CUSTOM_1) ) ) WHERE LOCATE(U.CUSTOM_1,CC_LIST)=0 /*if the email isn't already there*/ AND T.ID in ()
I am not a DBA of any sort, and not very well versed in mySQL. Any ideas as to why? - Diablo51 12 years ago
Posted by:
airwolf
13 years ago
One slight mod for this one to keep any existing cc in place
Good catch, Gerald. I didn't even think about that, but I'm sure my managers would've if I'd have implemented this by replacing the field instead of adding to it. [;)] I'd be tempted to use this, but we're using our users' custom fields for other stuff. It would be nice to be able to attach CCs to aspects aside from Categories - such as Owners in this case.
Posted by:
GillySpy
13 years ago
In our production helpdesk we allow customers to have an autocc, where a rule, tacks that to the cc list based on what your records have. Simlar end-result but I understand what your saying.
It could be possibly be a built-in feature. Shoot an email to us and we'll log it.
It could be possibly be a built-in feature. Shoot an email to us and we'll log it.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.
Ty ! - Crash73 8 years ago