KACE Product Support : WatingOverdue/OverdueClose Ticket Rules

WatingOverdue/OverdueClose Ticket Rules

Hi,

We have these 2 inbuilt rules enabled on our KACE system but they do not work. It always says there are 0 rows selected.

This is the code for the rules:

WaitingOverdue

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((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 and ((HD_STATUS.NAME = 'Waiting on end user'))
                       and DATE_SUB(NOW(), INTERVAL 2 DAY) > DATE(HD_TICKET.MODIFIED)
                       and HD_TICKET.HD_QUEUE_ID = 1

update HD_TICKET as T, HD_STATUS as T5
set T.HD_STATUS_ID = T5.ID,
T.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
where T5.NAME = 'Waiting on end user (2 Days)' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in ())

OverdueClose

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((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 and ((HD_STATUS.NAME = 'Waiting on end user (2 Days)'))
         and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)
            and HD_TICKET.HD_QUEUE_ID = 1

update HD_TICKET as T, HD_STATUS as T5
set T.HD_STATUS_ID = T5.ID,
T.RESOLUTION = CONCAT(T.RESOLUTION,'
Closed Email Sent'),
T.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), T.TIME_OPENED),
T.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), T.TIME_CLOSED),
T.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), T.TIME_STALLED),
T.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_RATING),
T.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, T.SATISFACTION_COMMENT)
where T5.NAME = 'Closed' and
T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in ())

They should change the status and comment the ticket after 2 days (waitingoverdue) and then close the ticket and comment again after 3 days (overdueclose)

Hoping someone can point me in the right direction.

Many thanks.

0 Comments [ + ] Show comments

Answers (2)

Posted by: svmay 7 years ago

Hello @ms34,

I know, it's a little bit difficult with date and time querys. Maybe one of these "questions" can help you.

http://www.itninja.com/question/dell-k1000-service-desk-ticketrule-send-mail-on-duedate

http://www.itninja.com/question/dell-k1000-service-desk-ticketrule-send-mail-on-ticket-close

http://www.itninja.com/question/dell-k1000-service-desk-add-comment-to-a-ticket-per-custom-rule

Let me know if you have any question, maybe we can find a way that your rules will work.

cheers

Comments:

Many thanks for these. I've already got the due date email function working so maybe just have use this as a template and go from there.

The code above, when I run it, always comes back as zero and we have got a ticket at the moment that hasn't been responded to in this time period - not sure if it seeing that there has been a response (has still been more than 2 days) and not doing it.

Thanks. - ms34 7 years ago
I had also problems with the time query - my query doesn't consider the duedate field, but a custom ticket field. Otherwise it wouldn't work and the compromise with a custom ticket field was fine for me. - svmay 7 years ago
- Many thanks. I think just going to go with including the required information when a ticket is closed - easiest option I think. Many thanks again for the links you sent through. - ms34 7 years ago

Posted by: RichB 7 years ago

I compared your query with mine that is working and the difference [beside the different status names "Waiting on end user" and "Waiting on end user (2 Days)"] is you have some text differences:

The line in the Waiting Overdue Select SQL statement that works for me:

if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,

and what you have:

if(APPROVAL='approved', 'Approved', if(HD_TICKET.APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,

And then the Update SQL that works for me:

T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in (<TICKET_IDS>))

and what you have:

T.HD_QUEUE_ID = T5.HD_QUEUE_ID and (T.ID in ())

The same differences also exist in the Overdue Close rules statements. We use these a lot on our help desk queues.

This based on version 6.4 SQL so may have differences if running 7.2.

Comments:

Many thanks for this. Yes we are running 7.2.

I will make these changes and let you know if they work.

Thanks. - ms34 7 years ago

Answer this question

Posted by:

View more:

KACE Product Support Questions

question