k1000 Ticket Rule help


I have ticket rules set in each Queue to auto assign a Due Date based on priority level...this works correctly. However, we have noticed that if we manually adjust the due date (as a technician/admin) it does not save and changes back to whatever the due date was when the priority was set. We made a new priority that had no rules and we are now allowed to manually adjust due dates...was wondering if there was something we could do to make it where we can manually change the due date on a priority that has a rule set?

This is what is checked on in the rule:


Run Update Query


Frequency = on Ticket Save

Here is the 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,

                        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_PRIORITY.NAME = 'Low (1 Week)') and HD_TICKET.HD_QUEUE_ID = 1 )

Here is the update sgl query

update HD_TICKET as T



        (T.ID in (<TICKET_IDS>))

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 7 months ago
Red Belt

You can add this line to the select statement to only run on tickets that don't have a date set:

AND HD_TICKET.DUE_DATE != "0000-00-00 00:00:00"

  • Thank you chucksteel, I tried adding that to the select statement and now the due date does not auto calculate unless we manually enter one. As soon as we manually enter it...the due date then shows what the priority default time would originally be set to and does not allow it to be changed manually - trankin 7 months ago
    • That because I led you astray. You want DUE_DATE = '0000-00-00 00:00:00'. - chucksteel 7 months ago
      • Thank you...I added that to the rule and now we are able to manually set the date...however what we would like to happen is for it to auto create the due date based on priority level...then IF we wanted to update it manually we could without it reverting back to the default priority calculation.

        With and (( HD_TICKET.DUE_DATE = '\"0000-00-00 00:00:00\"') we can manually change the due date, but it is not firstly automatically calculating the due date...if that makes any sense...

        I truly appreciate your help with this! - trankin 7 months ago
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