Best Practices

I create a custom rule via the wizard then customize as needed.

For instance (probably could be cleaned up)...

This selects tickets and notifies a technician for a special case we have... in our system all tickets are routed automatically based on the predefined categories in KACE, but if a user is of the Commercial Operations/Dispatch department and the ticket is New-Not Started it will be directed to a user called XXXXX (didn't want to put their name here).

This occurs ON TICKET SAVED

Select statement:

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(NOW()) - UNIX_TIMESTAMP(HD_TICKET.TIME_OPENED)
ELSE UNIX_TIMESTAMP(NOW()) - 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(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
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

*************************************This is where I came in an fiddled with the code*********************
AND (((((HD_STATUS.NAME NOT LIKE '%Closed%')
AND HD_PRIORITY.NAME LIKE '%Normal%')
AND (HD_TICKET.CUSTOM_FIELD_VALUE1 LIKE '%Commercial Operations%'
OR HD_TICKET.CUSTOM_FIELD_VALUE1 LIKE '%Dispatch%'))
AND HD_STATUS.NAME LIKE '%New - Not Started%')
AND HD_TICKET.HD_QUEUE_ID = 5)
***********************************************************************************************************************




Update statement:

update HD_TICKET, USER as T5, HD_STATUS as T6
set HD_TICKET.OWNER_ID = T5.ID,
HD_TICKET.HD_STATUS_ID = T6.ID,
HD_TICKET.TIME_OPENED = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T6.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
*******************Another edit******************************************************
where T5.USER_NAME = 'XXXXX' and
T6.NAME = 'New - Owner Assigned' and
*****************************************************************************************
HD_TICKET.HD_QUEUE_ID = T6.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>)) - dsykes 7 years ago

I think I actually figured this out. The checklist was being created as a child ticket. However, I've done away with the checklist as it wasn't being used and I found an easy way to basically get the same SQL query as what dsykes posted. Unfortunately, the e-mail that goes to the intended party comes up kinda ugly, the formatting is off. - adrianK 7 years ago