/build/static/layout/Breadcrumb_cap_w.png

KACE Service Desk Ticket Rule

I have a ticket rule I would like help with. I have the default ticket rule on that was built into kace "ReopenTicket" and what we would like it to do is to ignore running on tickets that the owner is "Unassigned". Can Someone help with doing that. I am not too skilled on SQL Query's. 


The select and update sql commands are below.

 

Select SQL

 

select distinct HD_TICKET.ID,

               HD_TICKET.OWNER_ID as OWNER_ID,

               HD_TICKET.ID as TICKNUM,

               HD_TICKET.TITLE,

               HD_STATUS.NAME AS STATUS_NAME,

               HD_STATUS.STATE as STATE,

               OWNER.USER_NAME as OWNER_NAME,

               OWNER.FULL_NAME as OWNER_FULLNAME,

               OWNER.EMAIL as OWNER_EMAIL,

               UPDATER.USER_NAME as UPDATERNAME,

               UPDATER.EMAIL as UPDATEREMAIL

          from (HD_TICKET, HD_STATUS)

     left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>

     left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID

     left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

     left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID

         where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID

           and HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID

           and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'

           and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'

           and HD_STATUS.STATE = 'closed'

           and (UPDATER.ID <> OWNER.ID or OWNER.ID is NULL)

           and UPDATER.ID > 0

 

update SQL

 

update HD_TICKET as T, HD_STATUS as STATUS set T.HD_STATUS_ID = STATUS.ID, T.RESOLUTION = CONCAT(T.RESOLUTION,'

Reopened'),

T.TIME_OPENED  = IF(STATUS.STATE = 'opened', NOW(), T.TIME_OPENED), T.TIME_CLOSED  = IF(STATUS.STATE = 'closed', NOW(), T.TIME_CLOSED), T.TIME_STALLED = IF(STATUS.STATE = 'stalled', NOW(), T.TIME_STALLED), T.SATISFACTION_RATING = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_RATING), T.SATISFACTION_COMMENT = IF(STATUS.STATE = 'closed', NULL, T.SATISFACTION_COMMENT) where STATUS.NAME = 'Reopened' and T.HD_QUEUE_ID = STATUS.HD_QUEUE_ID and  (T.ID in (<TICKET_IDS>))


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: h2opolo25 10 years ago
Red Belt
0

select distinct HD_TICKET.ID,

               HD_TICKET.OWNER_ID as OWNER_ID,

               HD_TICKET.ID as TICKNUM,

               HD_TICKET.TITLE,

               HD_STATUS.NAME AS STATUS_NAME,

               HD_STATUS.STATE as STATE,

               OWNER.USER_NAME as OWNER_NAME,

               OWNER.FULL_NAME as OWNER_FULLNAME,

               OWNER.EMAIL as OWNER_EMAIL,

               UPDATER.USER_NAME as UPDATERNAME,

               UPDATER.EMAIL as UPDATEREMAIL

          from (HD_TICKET, HD_STATUS)

     left join HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = <CHANGE_ID>

     left join HD_TICKET_CHANGE_FIELD on HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID = HD_TICKET_CHANGE.ID

     left join USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

     left join USER UPDATER on UPDATER.ID = HD_TICKET_CHANGE.USER_ID

         where HD_STATUS.ID = HD_TICKET.HD_STATUS_ID

           and HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID

           and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_RATING'

           and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED !='SATISFACTION_COMMENT'

           and HD_STATUS.STATE = 'closed'

           and  UPDATER.ID != OWNER.ID

           and UPDATER.ID > 0

           and OWNER.ID is not NULL

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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