/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Trigger ticket rules based on change user label

01/10/2020 221 views

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?

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0

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>))

Answered 02/07/2020 by: JesseJM
White Belt

All Answers

0

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.

Answered 01/10/2020 by: Hobbsy
Red Belt

  • 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.
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ