Getting SetTimeWorkedAutomatically Working in RT 4.2.9

RT Users
1

Hi Folks,

Our management is adamant about having time tracked automatically on
tickets.

I’ve been banging my head against a wall for 2 months trying to get this to
work VIA pure SQL until I realized that the data model seems to not support
it. Specifically although there’s a transaction logged every time the ticket
goes from open to stalled back to opened, there’s no idea of a transaction
session so that if I try to write SQL that joins transaction to transaction,
and tries to subtract the latest time stamp for “opened” from the last
“opened” there’s no way to determine which two “Opened” transactions
corelate to the transaction before it, so it ends up double counting
(imagine a ticket that goes from Opened, to Stalled, to Opened, to Stalled
and so-on), I end up having subtracting all latest records from the original
record so it double counts.

So I’ve tried to use the Scrips to do this, and noticed there’s already a
user-contributed Scrip for “SetTimeWorkedAutomatically”:

https://rt-wiki.bestpractical.com/wiki/SetTimeWorkedAutomatically

However I can’t get this to work on my version of RT. First off, when I go
to create a Scrip there’s no option for “Custom action cleanup code”.

I only get 3x options for creating a Scrip:

“Custom Condition”

“Custom Action Preparation Code”

and

“Custom Action Commit Code”
…

I’ve tried pasting the perl script that’s on the above URL into any of the
3x fields but RT errors and fails to compile the Scrip.

So I then tried to create a custom action with little success.

I can provide my code for the custom action.

Can anyone help out with this?

View this message in context: http://requesttracker.8502.n7.nabble.com/Getting-SetTimeWorkedAutomatically-Working-in-RT-4-2-9-tp62258.html

2

geraldsnm writes:

Our management is adamant about having time tracked automatically on
tickets.

I’ve been banging my head against a wall for 2 months trying to get this to
work VIA pure SQL until I realized that the data model seems to not support
it. Specifically although there’s a transaction logged every time the ticket
goes from open to stalled back to opened, there’s no idea of a transaction
session so that if I try to write SQL that joins transaction to transaction,
and tries to subtract the latest time stamp for “opened” from the last
“opened” there’s no way to determine which two “Opened” transactions
corelate to the transaction before it, so it ends up double counting
(imagine a ticket that goes from Opened, to Stalled, to Opened, to Stalled
and so-on), I end up having subtracting all latest records from the original
record so it double counts.

So I’ve tried to use the Scrips to do this, and noticed there’s already a
user-contributed Scrip for “SetTimeWorkedAutomatically”:

SetTimeWorkedAutomatically - Request Tracker Wiki

However I can’t get this to work on my version of RT…

Can anyone help out with this?

Can’t help with the scrip.

Rolling my own, I would do the following:

  1. Determine the deltas.
    Join each transaction A of a ticket with each transaction B of the same ticket where A.timestamp > B.timestamp, and find the minimum delta time for each A, noting A.state as OldState, B.state as NewState, and diff(A.timestamp, B.timestamp) as Delta.
  2. Determine if the delta counts.
    For each ticket, if [OldState,NewState] is countable, add to total
  3. Print results.
    For each ticket, print total and other needed details.

Writing SQL for the above is likely hard. However, a procedural method like this could be used instead:

  1. Select all transactions for all relevant tickets, sorting by [Ticket Number, Timestamp]
  2. Process each transaction in turn
    If the ticket number changes:
    If already on a ticket, print details.
    Set TotalTime = 0
    Set StartTime = timestamp
    Set CurrentTime = timestamp
    Set CurrentState = State
    If this transaction ends a countable time, add it:
    If [CurrentState,State] should be counted:
    Set TotalTime = TotalTime + (timestamp - CurrentTime)
    Update tracking variables:
    Set CurrentTime = timestamp
    Set CurrentState = State
  3. Print the last entry
    Print details

/jeff
The information contained in this e-mail is for the exclusive use of the
intended recipient(s) and may be confidential, proprietary, and/or
legally privileged. Inadvertent disclosure of this message does not
constitute a waiver of any privilege. If you receive this message in
error, please do not directly or indirectly use, print, copy, forward,
or disclose any part of this message. Please also delete this e-mail
and all copies and notify the sender. Thank you.