Trigger ticket rules based on change user label
I'd like to make a ticket rule that prevents users with a certain label from setting the ticket status to closed and I'm having trouble. Can anyone help?
Answers (2)
This is sort of possible, you have to use the update log for the ticket that tracks the changes and whilst you cannot prevent the user setting the data value within the ticket, you can undo the change and let someone know that the change was made "illegally". So the logic is the ticket rule says if this data value is changed and the person who updated it is x, then set the value back to previous.
Comments:
-
Right, we'd let the rule overwrite their status change, so back to "Open" or whatever.
It's more that I'm having trouble with the sql.
There is a ticket change table, a change user, a change user label, etc. Should be possible, but the wizard puts in a bunch of garbage sql along with the important bits, and I've been unable to get things to work, both when I've written the sql from scratch and when I've tried to modify the wizard generated sql. - JesseJM 4 years ago
Top Answer
So I figured this out. I ended up using a role rather than a label but you couple probably alter easily enough. I just didn't want to deal with a table that was going to return a bunch of values when role was just as good for us. You'd probably handle that similarly to the Change table (see "C.ID=<CHANGE_ID>"); that part was important otherwise there is a result returned for every change ever associated with the record, which grows more or less exponentially for each ticket. Anyway, for posterity, here's the SQL:
----------------Select sql-------------------
SELECT
HD_TICKET.ID, -- $id
UPDATER.ROLE_ID -- $updater_roleid
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_ID>
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
/* change field ******/ JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = C.ID
WHERE
UPDATER.ROLE_ID = 6 /*This is the technician's role*/
AND CF.FIELD_CHANGED="HD_STATUS_ID"
AND CF.AFTER_VALUE=118 /*This is our"closed" status*/
/* this is necessary because otherwise the change table returns a million things */
GROUP BY HD_TICKET.ID
-------------Update SQL-------------
update HD_TICKET
set HD_TICKET.HD_STATUS_ID = 128 /*ID for the status I want to change to"*/
where (HD_TICKET.ID in (<TICKET_IDS>))