I have followed the tutorial so far to create a SLA timer in the Service Desk (http://www.itninja.com/blog/view/creating-true-sla-timers-in-the-kbox-helpdesk-part-1-what-you-really-want-from-your-data) so I can get more accurate account of time a ticket is in an opened or stalled state. I left a comment on Part III along with another user that there is an error when we run the SQL on the first ticket rule "Adjust custom modified field".

I also want to use it to set Business Hours so the timer only counts business hours as time opened. The SQL on these rules is far beyond me.

Here's the sql select query for the "Adjust custom modified field" rule:

update 

        HD_TICKET as T 

   JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>

set 

   T.CUSTOM_FIELD_VALUE11 = NOW(),

   T.CUSTOM_FIELD_VALUE12 = 

             IF(C.DESCRIPTION LIKE 'Ticket Created%' OR T.CUSTOM_FIELD_VALUE12='',

                 0,T.CUSTOM_FIELD_VALUE12) ,

   T.CUSTOM_FIELD_VALUE13 = 

             IF(C.DESCRIPTION LIKE 'Ticket Created%' OR T.CUSTOM_FIELD_VALUE13='',

                 0,T.CUSTOM_FIELD_VALUE13) ,

   T.CUSTOM_FIELD_VALUE14 = 

             IF(C.DESCRIPTION LIKE 'Ticket Created%' OR T.CUSTOM_FIELD_VALUE14='',

                 NOW(),T.CUSTOM_FIELD_VALUE14)

And here's the update query:

update 

        HD_TICKET as T 

   JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>

set 

   T.CUSTOM_FIELD_VALUE11 = NOW(),

   T.CUSTOM_FIELD_VALUE12 = 

             IF(C.DESCRIPTION LIKE 'Ticket Created%' OR T.CUSTOM_FIELD_VALUE12='',

                 0,T.CUSTOM_FIELD_VALUE12) ,

   T.CUSTOM_FIELD_VALUE13 = 

             IF(C.DESCRIPTION LIKE 'Ticket Created%' OR T.CUSTOM_FIELD_VALUE13='',

                 0,T.CUSTOM_FIELD_VALUE13) ,

   T.CUSTOM_FIELD_VALUE14 = 

             IF(C.DESCRIPTION LIKE 'Ticket Created%' OR T.CUSTOM_FIELD_VALUE14='',

                 NOW(),T.CUSTOM_FIELD_VALUE14)

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity

Share