/build/static/layout/Breadcrumb_cap_w.png

Creating True SLA Timers in the KBOX Helpdesk -Part III- The Rules

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


Comments

  • sorry for the spaces. I am not brave enough in the editor to do what seems to be required to get rid of them. Keep scrolling! :D - GillySpy 11 years ago
  • Great post and series! Keep it coming! - lmland 11 years ago
    • Thx. will post again soon. I have not been getting IT ninja notifications but this has been remedied now - GillySpy 11 years ago
  • Hi Gilly, I been following these posts and have created the first query "Adjust custom modified field" but immediately I'm running into an SQL error on the rule. It appears to be at 'and <TICKET_JOIN>.. I'm not certain if that line is needed or what does it do? The error output by KACE is:

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<TICKET_JOIN> and C.ID=0

    JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_' at line 5] in EXECUTE("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=0

    JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=HD_TICKET.HD_QUEUE_ID

    and FIELD_LABEL='Counter Last Updated'
    and QF.NAME='CUSTOM_3'")

    Thanks. - twit 11 years ago
    • it appears you have clicked the "run now" button or are running it on a schedule. Rules with <TICKET_JOIN> in them are designed intentionally to only run on 1 ticket "on ticket save". <TICKET_JOIN> is optional in most select queries even when "on ticket save" is the schedule. I put it there to protect you from acidentally running it on a different schedule - GillySpy 11 years ago
  • I get the same error. Not sure how to troubleshoot SQL really. I've tried to run reports on these custom fields to see if there is any data there, but I get nothing. - lmland 11 years ago
  • Not an SQL expert myself either. If you take out the whole TICKET_JOIN line then it runs without an error but I doubt it does what its supposed to do without it. - twit 11 years ago
  • Twit,
    Anything that is seen between the two LT (&amp;lt;) and GT (&amp;gt;) signs are custom KACE variables. These are variables that are NOT SQL syntax, but get converted to an usable value right before the rule is ran. An example would be &amp;lt;CHANGE_ID&amp;gt; which gets replaced with the value returned by HD_TICKET_CHANGE.ID right before running. This is really great since the ID does not exist until the change itself is created!

    If you are running these SQL Queries in a 3rd party utility (SQL Workbench, Flyspeed, Queryview) then they will give an error because they do not know how to handle these special variables. - mtree 11 years ago
    • correct see above. If you want to test on specific ticket as a sort of preview then replace <TICKET_JOIN> with "HD_TICKET.ID=123" or whatever ticket # you have - GillySpy 11 years ago
  • My attempt to use the first ticket rule's Select query fails when I try to see the results. I've cleared everything generated from the New Ticket Rule wizard screens and filled it in with GillySpy's 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'

    I understand what <TICKET_JOIN> and <CHANGE_ID> are doing, but can they be successfully used when clicking the "View Ticket Search Results" link under the select query box or am I shooting in the dark here?

    Here's the error I'm receiving. It looks like Kace is really twisting up my query. E.G. It completely skips these <TICKET_JOIN> and <CHANGE_ID> variables which leaves behind obvious syntax errors.

    I'm using the Kace1000 at v5.4.70402.

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' and C.ID= JOIN HD_FIELD QF ON QF.HD_QUEUE_ID=' at line 3] in EXECUTE("SELECT COUNT(*) FROM HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID and and C.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' and HD_TICKET.HD_QUEUE_ID = '2' LIMIT 1") - psunderhaus 11 years ago
    • "I understand what <TICKET_JOIN> and <CHANGE_ID> are doing, but can they be successfully used when clicking the "View Ticket Search Results" link under the select query box or am I shooting in the dark here?"

      It wont ever work on "View Ticket Search Results" or if you tell the rule to "Run Now"

      These are tokens that get turned into useful values when the kbox is running the Ticket Rule. However these values are also dependent on a ticket activating a rule, hence why it MUST be "On Ticket Save"

      If you use "View Ticket Search Results" then a Ticket is NOT kicking off the Rule, which then turns all tokens into null values, which then become syntax error.

      These rules with the tokens are set to run against a single ticket at a time. If you wanted you could change <CHANGE_ID> to a numerical value, but you would still get a single ticket. It would best to just run these tickets on their recommended settings. - mtree 11 years ago
      • Thank you, mtree. This leaves me feeling like I'm really working with a black box, but that'll have to do.

        For anyone else wanting more information on Tokens, Page 205 of the K1000 v5.4 Administrator Guide contains more information on &quot;Token Replacement Variables.&quot; (Use the Table of Contents, it's a huge doc ;-)

        http://www.kace.com/support/resources/~/media/Files/Support/Documentation/K1000/v54/K1000-Admin-Guide-v54.ashx

        Edit: Actually, this is for scripting. A different topic. Please disregard. - psunderhaus 11 years ago
      • Looking into this more, The Support Desk Administrator's guide would be the proper document:

        http://www.kace.com/support/resources/~/media/Files/Support/Documentation/K1000/v54/K1000-Service-Desk-v54.ashx - psunderhaus 11 years ago
    • IT Ninja apparently doesn't support nested replies above 3, so this is in regards to your later posts.

      I believe you wont find any in the admin guides about these tokens. The tokens article you listed is for KScripts and will not work with ticket rules. As far as I know there is no formal explanation for these tokens. I myself had to run a few tests before I understood their functionality.

      The only time you will really see these are in articles created by KACE employees like Gillyspy or from users who have tinkered around with ticket rules. - mtree 11 years ago
      • You're absolutely right. As I dig into the second guide I linked to I'm finding there isn't so much as a listing of valid tokens, let alone a guide to their use. - psunderhaus 11 years ago
      • yes for some reason <TICKET_JOIN> has never been documented, yet i find it extremely useful and in complex queries it is necessary for boosting performance. If you do not care about performance or protection from accidentally clicking "run now" then it is not necessary. If you leave it out of your query then "and HD_TICKET.ID=123" is implied and appended to the end of your "on ticket save" select query - GillySpy 11 years ago
    • no you cannot use "view ticket search results" with these queries. It is a bug in the system, HOWEVER, i find clicking that a slow way to troubleshoot and believe that anyone testing rules is better off running their tests in a tool like mysql workbench. workbench is better because you can run things more quickly then a web request; the query is formatted in an easier to read way (syntax highlighting); it has inline help for whatever you wish to explore; you are logged in as a "read-only" user and cannot mess up your database - GillySpy 11 years ago
      • I'm in Sequel Pro which is comparable to MySQL Workbench but this still leaves me completely in the dark on knowing what all the options are for the Rule tokens.

        Thanks for the clarification. - psunderhaus 11 years ago
  • I get the error when running the query in KACE. - lmland 11 years ago
    • Make sure that the rule configurations are correct. Any of the rules listed as a Frequency of "On Ticket Save" will NOT work when ran manually. They rely on tickets executing the rule and will not run properly on any other Frequency. - mtree 11 years ago
      • Ok, that is very helpful information (with your reply to psunderhaus and twit also). So, how do I know that the rules are working? I have tried to run a report on the custom fields, but get no results.

        I have escalation rules that I want to be based on a more accurate count of time open from this field and want to create rules for only tracking during business hours.

        Is there a report or some way to test that these rules are working and populating data in the custom fields? - lmland 11 years ago
  • @Imland:

    You could turn on the visibility of the custom fields and view them on each ticket, However a simple report to pull all this information is the following:
    Select
    HD_TICKET.TITLE,
    HD_TICKET.ID,
    HD_TICKET.CUSTOM_FIELD_VALUE14,
    HD_TICKET.CUSTOM_FIELD_VALUE13,
    HD_TICKET.CUSTOM_FIELD_VALUE12,
    HD_TICKET.CUSTOM_FIELD_VALUE11
    From
    HD_TICKET

    Keep in mind that only tickets created AFTER the rules were implemented will begin tracking this time.
    If you are still having issues then I would recommend going through all parts of this article again. - mtree 11 years ago
    • Ok, I actually missed one of the rules on this post. Fixed that, but all fields still contain no information. I understand that it will only work for tickets created after the rules, but 9 tickets were created after I fixed it and the fields are all blank. If the last rule runs every 15 minutes it should be adjusting it already. Any ideas? - lmland 11 years ago
      • I'm the same as Imland here. Understand how <Ticket_Join> etc work but the fields created on tickets by these rules remain blank, even though the tickets were created after the rules. Quite a few comments on this post now but I wonder if anyone other than the creator has actually got it to work? - twit 11 years ago
  • For some reason the rules seem to be functioning properly now. I don't believe I did anything or made changes that would have caused it to start working. So, I don't have any help or answers except that there are now values in the custom fields for me. - lmland 11 years ago
    • Upon further investigation there is now data in the fields, but it is clearly not correct. Everything in the Time Stalled field says 0. Tickets that have been in status New for 9+ days say 0 in the Time Opened field. - lmland 11 years ago
  • I added a blog post for a solution to the problem mentioned here: http://www.itninja.com/blog/view/update-to-sla-timer-ticket-rules-series - lmland 11 years ago
  • As I was working on a different rule that depends on the default modified field, I realized that the constant adjustment of the custom modified field must also be updating the default modified field which crews up any other ticket rules based on that field which I definitely have. A fix for this should be included in the tutorial. - lmland 10 years ago
  • Just a note to all that the rule 'Proactively Increment the counters' will stop built-in rules "WaitingOverdue" and "OverdueClose" from working because the proactive rule edits the ticket every 15 minutes. The 2 built-in rules I mentioned use the modified field as the point of reference to count the days from, so because the ticket is modified every 15 minutes, those built-in rules never hit their deadline and therefore never run because it only ever sees 15 minutes having past.

    Just a heads-up... Caught me out.

    Cheers. - twit 10 years ago
  • 7 years old and kace still doesnt track KPI's. - barchetta 3 years ago
This post is locked
 
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