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.
1 Comment   [ + ] Show Comment

Comments

  • I've the same problem on 6.3, is there a solution or another SQL request ?
    Ty !
  • This content is currently hidden from public view.
    Reason: Determined to be spam through moderation
    For more information, visit our FAQ's.
Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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.
Answered 06/11/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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

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>)
Answered 06/11/2010 by: GillySpy
Seventh Degree Black Belt

  • 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?
Please log in to comment
0
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.
Answered 06/11/2010 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.
Answered 06/11/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
This doesnt seem to work on Kace K1000 version 6.2. The CC field doesn't seem to populate as easy.
Answered 03/24/2015 by: shandy4473
Yellow Belt

  • I've the same problem on 6.3, is there a solution or another SQL request ?
    Ty !
Please log in to comment
Answer this question or Comment on this question for clarity