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
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
Anything that is seen between the two LT (&lt;) and GT (&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 &lt;CHANGE_ID&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
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
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
For anyone else wanting more information on Tokens, Page 205 of the K1000 v5.4 Administrator Guide contains more information on "Token Replacement Variables." (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
http://www.kace.com/support/resources/~/media/Files/Support/Documentation/K1000/v54/K1000-Service-Desk-v54.ashx - psunderhaus 11 years ago
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
Thanks for the clarification. - psunderhaus 11 years ago
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
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
Just a heads-up... Caught me out.
Cheers. - twit 11 years ago