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,
                        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,')
        (HD_TICKET.ID in (<TICKET_IDS>))

0 Comments   [ + ] Show comments

Answers (2)

Posted by: barchetta 1 year ago
4th Degree Black Belt

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.

Posted by: walkerl 1 year ago
Yellow Belt
@barchetta My version of limiting damage is this piece "AND HD_TICKET.ID = '60')" so it only affects one ticket but I suppose I could still kill something in the db regardless, thanks for that tip. I never figured this sql out and have abandoned the effort for now. The ROI digging into this problem is too low at this point after an hour or two.
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