In the last entry we were left with a mission to handle these scenarios:

Here are the rules:

Case

Rule Name

1

Adjust custom modified field

2

React to changes that adjust status

3,5,6

Proact to OTS Rules that adjust status

4

Proactively increment the counters

 

Let's tackle these one at a time. Note that when I do not provide a setting you can leave that blank

Adjust Custom Modified Field

Because the system modified field will get updated when the counter gets updated we need our own custom field. You could update the OEM modified field but I don't want to play that game because I want the internal MODIFIED column to represent when the record was touched and my rule is touching the ticket.

Title Adjust custom modified field
Frequency On Ticket Save
Order 140
Select Query
select 
     C.ID
 FROM HD_TICKET
         JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID
          and <TICKET_JOIN> and C.ID=<CHANGE_ID>
         JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
                and FIELD_LABEL='Counter Last Updated'
                and QF.NAME='CUSTOM_15'
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)

 

 

 

 

 

 

 

 

 

 

 

 

 

React to Changes That Adjust Status

If there are changes in status of a ticket then the state of a ticket is potentially changing and then we need to update the timers at that moment

Title React to Changes That Adjust Status
Frequency On Ticket Save
Order 160
Select Query
select
     C.ID
from
          HD_TICKET
  JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=<CHANGE_ID>
  JOIN HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID=C.ID and F.FIELD_CHANGED='STATUS_NAME'
  JOIN HD_STATUS S ON S.NAME=F.BEFORE_VALUE and S.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
  JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID and FIELD_LABEL='Counter Last Updated'
            and QF.NAME='CUSTOM_15'
WHERE
    /* save has been clicked since last update */
  HD_TICKET.MODIFIED  > ifnull(cast(CUSTOM_FIELD_VALUE14 as datetime),'0000-00-00')  
  and (
    LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE12)>0 OR
    LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE13)>0
  )
Update Query
update HD_TICKET as T 
   JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=T.ID and C.ID=<TICKET_IDS>
   JOIN HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID=C.ID and F.FIELD_CHANGED='STATUS_NAME'
   JOIN HD_STATUS BEFORE_S ON BEFORE_S.NAME=F.BEFORE_VALUE and BEFORE_S.HD_QUEUE_ID=T.HD_QUEUE_ID
set
  CUSTOM_FIELD_VALUE13 =
     case /* time opened is the greatest and has been set since the last timer. e.g a rule set it */
      when BEFORE_S.STATE= 'Opened'
           then TRUNCATE(CUSTOM_FIELD_VALUE13 +
                   TIMESTAMPDIFF(MICROSECOND,CUSTOM_FIELD_VALUE14, NOW())/60/60/1000000,3)
      else CUSTOM_FIELD_VALUE13 end,
   CUSTOM_FIELD_VALUE12  =
      case when BEFORE_S.STATE='Stalled'
            then TRUNCATE(CUSTOM_FIELD_VALUE12 +
                   TIMESTAMPDIFF(MICROSECOND,CUSTOM_FIELD_VALUE14, NOW())/60/60/1000000,3)
        else T.CUSTOM_FIELD_VALUE12 end,
  T.CUSTOM_FIELD_VALUE14 = NOW()

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Proact to other rules (on ticket save rules) that adjust the status/state of a ticket

see explanations of scenarios 3, 5 and 6 from part II of this blog

Title Proact to OTS rules that adjust status
Frequency On Ticket Save
Order 160
Select Query
select HD_TICKET.ID 
from
            HD_TICKET
        JOIN  HD_STATUS S ON S.ID=HD_STATUS_ID
        JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID
        and FIELD_LABEL='Counter Last Updated' and QF.NAME='CUSTOM_15'
    LEFT JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and C.ID=0+'<CHANGE_ID>'
    LEFT JOIN HD_TICKET_CHANGE_FIELD F ON F.HD_TICKET_CHANGE_ID=C.ID and F.FIELD_CHANGED='STATUS_NAME'
WHERE
   (
        HD_TICKET.MODIFIED  >= ifnull(cast(CUSTOM_FIELD_VALUE14 as datetime),'0000-00-00')
        OR S.STATE IN ('Stalled','Opened')
    ) /* save has been clicked since last update */
    and F.ID IS NULL /* either save button was clicked in isolation OR status was not changed */
    and
    (
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE12)>0 OR
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE13)>0
    )
Update Query
update HD_TICKET as T 
   JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
set
  CUSTOM_FIELD_VALUE13 =
     case /* time opened is the greatest and has been set since the last timer. e.g a rule set it */
      when S.STATE= 'Opened'
         then  TRUNCATE(CUSTOM_FIELD_VALUE13 +
          TIMESTAMPDIFF(MICROSECOND,GREATEST(CUSTOM_FIELD_VALUE14,TIME_OPENED), NOW())/60/60/1000000,3)
      else CUSTOM_FIELD_VALUE13 end,
   CUSTOM_FIELD_VALUE12  =
      case when S.STATE='Stalled'
        then TRUNCATE(CUSTOM_FIELD_VALUE12 +
         TIMESTAMPDIFF(MICROSECOND,GREATEST(CUSTOM_FIELD_VALUE14,TIME_STALLED), NOW())/60/60/1000000,3)
        else T.CUSTOM_FIELD_VALUE12 end,
  T.CUSTOM_FIELD_VALUE14 = NOW()
where
    T.ID =<TICKET_IDS>

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Proactive Increment the Counters

We cannot sit around and wait for a ticket to be updated or have the save button pressed so we will update the timer on all relevant tickets.

Note that I did not check state here in case you wanted to add in a third counter for closed state

Title Proactively increment the counters
Frequency Every 15 minutes
Order 1000
Select Query
select HD_TICKET.ID 
from  
            HD_TICKET
        JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID  
            and FIELD_LABEL='Counter Last Updated' and QF.NAME='CUSTOM_15'
where
  /* only care about tickets where relevant time has changed */
    NOW()>ifnull(cast(CUSTOM_FIELD_VALUE14 as datetime),'0000-00-00')
    and
    (
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE12)>0 OR
        LENGTH(HD_TICKET.CUSTOM_FIELD_VALUE13)>0
    )
Update Query
update 
   HD_TICKET as T
  JOIN HD_STATUS S on T.HD_STATUS_ID=S.ID
set
  /* total seconds stalled */
  T.CUSTOM_FIELD_VALUE12 =
              case when S.STATE='Stalled' and HOURS.BIZ then TRUNCATE(CUSTOM_FIELD_VALUE12 +
               TIMESTAMPDIFF(MICROSECOND, CUSTOM_FIELD_VALUE14, NOW())/60/60/1000000,3)
              else CUSTOM_FIELD_VALUE12 end,
  T.CUSTOM_FIELD_VALUE13 =
              case when S.STATE='Opened' and HOURS.BIZ then TRUNCATE(CUSTOM_FIELD_VALUE13 +
                TIMESTAMPDIFF(MICROSECOND, CUSTOM_FIELD_VALUE14, NOW())/60/60/1000000,3)
              else CUSTOM_FIELD_VALUE13 end,
  T.CUSTOM_FIELD_VALUE14 = NOW()
where
  T.ID in (<TICKET_IDS>)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

There you have it the basic structure. Next post is

Part IV : Modify these rules to dealing with Queues that already have tickets and other rules

Part V: Adding in the concept of Business Hours

Part VI: Adding in Business Hours in a centralized location

Part VII: Adding an interface in the Service Desk to Change those Business Hours for each Queue