Is there a way to automatically add the submitters Manager to the CC field of tickets?
We want manager to be aware of tickets that their employees are entering. Is there a way that if the managers are already set for the submitter, that when the ticket is opened the manager is automatically added to the CC list.
Answers (2)
Just a question, since your query is a bit ... confused ...
...
You want the following:
For every ticket with the SD the manager of the employee is cc'ed, so the managers see the full going of the ticket during the run and not only informed that a ticket is opened? Or do you want the managers of the engineers who work on the ticket being able to work with the tickets directly too?
For the later one it is just a question of groups, that the managers can also read and work on the tickets.
For the first one:
- Enable "Allow managers to view and comment on their employee's tickets" in the queue settings in query.
- create a Custom Ticket Rule, which sets a new user to cc if not yet set, and collect this user from the manager field of the user who is submitter.
Comments:
-
Can you review my Query and let me know where the issue is?
Select statement
SELECT HD_TICKET.ID
FROM HD_TICKET
WHERE HD_TICKET.HD_QUEUE_ID = 13;
Update
UPDATE HD_TICKET
SET CC_LIST = CONCAT(COALESCE(CC_LIST, ''), ',', (
SELECT MANAGER.EMAIL
FROM USER SUBMITTER
JOIN USER MANAGER ON SUBMITTER.MANAGER_ID = MANAGER.ID
WHERE SUBMITTER.ID = (SELECT SUBMITTER_ID FROM HD_TICKET WHERE ID = HD_TICKET.ID)
))
WHERE ID IN HD_TICKET; - Ricky.Collins 1 year ago-
I think there is an extra ) in the update section. I have tried with and without it and I get this message"
08/26/2024 16:10:27> Starting: 08/26/2024 16:10:27 08/26/2024 16:10:27> Executing Select Query... 08/26/2024 16:10:27> selected 2905 rows 08/26/2024 16:10:27> Executing Update Query... 08/26/2024 16:10:27> mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE ID IN HD_TICKET.ID' at line 8] in EXECUTE("UPDATE HD_TICKET SET CC_LIST = CONCAT(COALESCE(CC_LIST, ''), ',', ( SELECT MANAGER.EMAIL FROM USER SUBMITTER JOIN USER MANAGER ON SUBMITTER.MANAGER_ID = MANAGER.ID WHERE SUBMITTER.ID = (SELECT SUBMITTER_ID FROM HD_TICKET WHERE ID = HD_TICKET.ID) ) WHERE ID IN HD_TICKET.ID;") 08/26/2024 16:10:27> Ending: 08/26/2024 16:10:27 - marshalisms 3 months ago
-
I am trying to accomplish this via a ticket rule as well. I usually can navigate kace pretty good. I struggle writing SQL queries but I am sometimes successful modifying an example query.
I am at the new ticket rule wizard and I decided the best approach would be to target tickets that are not in closed status and have an empty CC field. It is possible to change the CC List value to the submitter's manager? Does this have to be a custom SQL query?
I would greatly appreciate any help! - marshalisms 3 months ago
Documenting this as I now have it working! This automatically adds the ticket submitter's manager to the CC list. It will only apply if the CC list is empty. If there is a value already in the field the ticket rule will not be applied.
Select SQL:
SELECT HD_TICKET.ID,
USER_MANAGER.EMAIL AS MANAGER_EMAIL
FROM HD_TICKET
JOIN USER SUBMITTER ON HD_TICKET.SUBMITTER_ID = SUBMITTER.ID
JOIN USER USER_MANAGER ON SUBMITTER.MANAGER_ID = USER_MANAGER.ID
WHERE HD_TICKET.HD_QUEUE_ID = 1 AND CC_LIST = '';
Update SQL:
UPDATE HD_TICKET
SET CC_LIST = TRIM(BOTH ',' FROM CONCAT(COALESCE(CC_LIST, ''), ',', (SELECT USER_MANAGER.EMAIL
FROM USER SUBMITTER
JOIN USER USER_MANAGER ON SUBMITTER.MANAGER_ID = USER_MANAGER.ID
WHERE SUBMITTER.ID = HD_TICKET.SUBMITTER_ID)))
WHERE ID IN (<TICKET_IDS>);