KACE Product Support

below is our rules and the ticket rules that Knowledge artical 794 want added does anyon know how to merge these together

New select query needing added


select HD_TICKET_CHANGE.ID from
HD_TICKET JOIN HD_TICKET_CHANGE ON HD_TICKET.ID=HD_TICKET_ID and HD_TICKET_CHANGE.ID=<CHANGE_ID>
JOIN HD_STATUS ON HD_STATUS_ID=HD_STATUS.ID
WHERE
(DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR DESCRIPTION LIKE '%Changed Ticket Submitter from "%" to Unassigned%'
OR OWNERS_ONLY_DESCRIPTION LIKE '%Changed Ticket Machine from "%" to %Unassigned%'
OR OWNERS_ONLY_DESCRIPTION LIKE '%Changed Ticket Submitter from "%" to Unassigned%')
and ( COMMENT LIKE '%Machine % was deleted%' OR COMMENT LIKE 'User % was deleted%')
and HD_STATUS.STATE = 'Closed'


existing select query


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



new update query needing added

update HD_TICKET_CHANGE
set
HD_TICKET_CHANGE.NOTIFY_USERS=', /*remove all users from notification list */
OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH '\n' FROM(REPLACE (CONCAT(TRIM(BOTH '\n' FROM
OWNERS_ONLY_DESCRIPTION), '\n',
'Ticket Rule: Suppressing email notification for user/machine deletion'
,'\n') ,'\n\n','\n')))
where
HD_TICKET_CHANGE.ID in (<TICKET_IDS>)





Existing update query



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