SQL Rule when the updater posts
I'm trying to create a service desk ticket rule that when:
- Owner is Unassigned
- Status is New
- Comment posted is not null (there has been a comment made other than original summary)
- Ticket is saved
What I want to happen:
- Also Notify (CC) is auto-updated with the email of the last user who posted with a comment on a ticket meeting criteria above but when the owner does not assign the ticket nor enters their email in the notify field, especially one of the "owners" of the queue
I am having trouble figuring out what that field is called (the user who makes a change update) and how to select it and then set it in an update query. What I have right now gets stuck selecting and never finishes updating rows. Prior to this, I kept getting syntax errors to do with the UPDATER field, unknown column errors, and incorrect parameters to native function concat. Is there a better approach to selecting the UPDATER user or another way to do this?
Last run log: 03/02/2023 11:11:16> Starting: 03/02/2023 11:11:16 03/02/2023 11:11:16> Executing Select Query... 03/02/2023 11:11:16>
I keep reworking the SQL, but this is what I am currently sitting with:
Code - Select:
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,
UPDATER.EMAIL as UPDATER_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(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(HD_TICKET.APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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 (((( HD_STATUS.NAME = 'New') AND ( exists (select 1 from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID and HD_TICKET_CHANGE.COMMENT is not null)) ) AND HD_TICKET.ID = '60') and HD_TICKET.HD_QUEUE_ID = 2 )The Update Code below has been tried with several versions of UPDATER_xxx and all sorts of related fields I have searched on ITNinja.
Code - Update:
update HD_TICKET
set HD_TICKET.CC_LIST = concat( UPDATER.USER_NAME,'@email.us,')
where
(HD_TICKET.ID in (<TICKET_IDS>))
Answers (2)
I dont know the answer to this but I would suggest you can figure it out if you use a SQL editor. I use mysql workbench. This way you can do queries and see what is inside each table.. Thus you can deep dive into each ticket. Comments are tricky to track down but with a little work and patience you will find what you need. Keep digging here for a script that looks at comments.
Also, I would highly suggest you build a dev server or you risk destroying your sql DB in production. I came close once, I created a script to add a comment (I think I was working on adding ITIL "resolved" status) and I ended up adding a comment to every single open ticket in ALL queues. When the rule ran for 15 minutes panic set in. :) Luckly it was a very benign comment :)
Speaking of which, its a shame KACE does not follow ITIL standards at all.. even very rudimentary ITIL would be a huge step forward.. but, I dont see that ever happening based on their releases over the last 2 yrs.
