Backfilling "Started" date for old tickets


Was hoping someone could give me a few pointers to create a one-off script to backfill the “Started:” date on all old tickets which do not have it set. I am trying to do an analysis on response times, and to compare response times from previous years, however, it seems we only started using the “Started” field in the last couple of years, since the majority of old tickets do not have it set.

What I’d like to do is something like:

For each ticket in queue (queue) with status resolved:
: If ticket has no Started: date:
: Find the date of the first outbound correspondence transaction and set started date to this.
: If there is no outbound correspondence, set started date to the resolved or last updated date

Then I think it will be much easier to generate stats of initial response times e.g. time from Create -> Started. I have a script which generates response times, based on:

Although it works for the last 2 years, seems that we didn’t have RT configured to do anything like automatically set the Started: date on all queues before then, so this script doesn’t count any tickets that don’t have a started date.

Or maybe modify this script so that it loads the dates, rather than modify past tickets. (Although this would also help with RT’s internal reports, since it will have a date to report against.)

Any pointers much appreciated.


I think something like this can be a starting place, you’ll most likely need to add in some complexity:

my $tickets = RT::Tickets->new( RT->SystemUser );

# Find all tickets without a started date that aren't in the initial status
my $query = qq[ ( Status = '__Inactive__' ) AND Started is NULL ];
$tickets->FromSQL ( $query );

while ( my $ticket = $tickets->Next ) {
    # Grab our transactions
    my $transactions = $ticket->Transactions;
    $transactions->Limit( FIELD => 'Type', VALUE => 'Correspond' );
    # Order oldest transactions first
    $transactions->OrderByCols({FIELD => "Created", ORDER => "ASC"});

    my $first_reply = $transactions->First;
    if ( $first_reply ) {
        my ($ret, $msg) = $ticket->SetStarted( $first_reply->Created );
        RT::Logger->error( $msg ) unless $ret;
    # No first reply transaction found
    else {
        my $date =  $ticket->ResolvedObj->DateTime || $ticket->LastUpdated;
        my ($ret, $msg) = $ticket->SetStarted( $date );
        RT::Logger->error( $msg ) unless $ret;

Thanks for this! It seems to be on the right lines for what I need.

I’ll have to try out something on my dev copy of the system and see what happens. The further back in time we go with requests, the worse the data quality gets, since the system wasn’t used in the same way back then.

It was a more-or-less default install and everybody seemed to hate the web interface, opting instead to reply only via email, so tickets, while completed, were resolved in RT weeks or months after the fact, and the various customisations in the web interface were not implemented or poorly understood.

Now we’ve added a bit of customisation, custom fields, plugins and SLAs to make the workflows easier to manage and track when something needs responding to, we get more benefit from the system, with better response times. At-a-glance, users can see immediately what needs responding to and where each ticket is in the process. Much better than trawling through a pile of emails and having to read through every ticket to understand which steps of the process have been done and what still needs to be done.

1 Like