Looking for a custom ticket rule to set the owner to the person/changer of a ticket when they try to change the owner to an account that is restricted.
When a ticket analyst tries to change the owner or CC analyst of a ticket to an account that is restricted (e.g. company_admin), I want KACE to set the owner to the person attempting to make the change on ticket save. Any help is appreciated.
Here's what I have so far but it's not reverting the owner to the changer. It does set the owner to "unassigned' however:
#Collect information from the ticket:
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp() - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp() - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(HD_TICKET.APPROVAL='rejected', 'Rejected',
if(HD_TICKET.APPROVAL='info', 'More Info Needed',
if(HD_TICKET.APPROVAL='approved', 'Approved',
if(HD_TICKET.APPROVER_ID>0, 'Pending', '')
)
)
) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
Inner Join HD_TICKET_CHANGE On HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
Inner Join USER On HD_TICKET.OWNER_ID = USER.ID
Inner Join USER USER1 On HD_TICKET_CHANGE.USER_ID = USER1.ID
Inner Join USER USER2 On HD_TICKET.SUBMITTER_ID = USER2.ID
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( ( exists (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.FULL_NAME = 'company_admin')) ) and HD_TICKET.HD_QUEUE_ID = 2 )
#Change ticket owner to changer user id:
UPDATE HD_TICKET
Inner Join HD_TICKET_CHANGE On HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
Inner Join USER On HD_TICKET.OWNER_ID = USER.ID
Inner Join USER USER1 On HD_TICKET_CHANGE.USER_ID = USER1.ID
Inner Join USER USER2 On HD_TICKET.SUBMITTER_ID = USER2.ID
SET OWNER_ID = USER2.FULL_NAME
WHERE (HD_TICKET.ID in (<TICKET_IDS>))
Answers (2)
Top Answer
In the SQL Update, you will want to change this SET statement.
FROM
SET OWNER_ID = USER2.FULL_NAME
TO
SET OWNER_ID = USER2.ID
ID is a integer, FULL_NAME is a varchar
I have not tested your SQL query or update, so there maybe other issues too.
User the Ticket Change table to drive the rule rather than the HD Ticket table, you can then search for specific text that indicates the assignment and you will also get the ID of the tech making the change so you can set it as the owner
Comments:
-
I appreciate the reply Hobbsy. It looks like I just needed to change this line:
SET OWNER_ID = USER2.FULL_NAME
#TO#
SET OWNER_ID = USER1.ID
The FULL_NAME is not recognized as an ID number and could not convert to the user who made the change. I had to match the ID.
Again, thanks for the help. - jarrett 1 year ago
