[rt-users] Search Ticket Transaction History in Query Builder?

[Brian Schrock]

Excellent suggestion! This is how I ended up doing it and Operations is testing it now.

I created a custom field named "Last Reopened Date" and applied that to the Queues I care about.

I then added this scrip to the queues in question.

Description: On Reopen Set Date
Condition: User Defined
Action: User Defined
Template: Global template: Blank
Stage: Transactioncreate

Custom Condition:
my $txn = $self->TransactionObj;
my $type = $txn->Type;

unless (
    ($type eq "Status") ||
    ($type eq 'Set' && $txn->Field eq 'Status') ||
    ($txn->OldValue eq "resolved") ||
    ($txn->OldValue eq "stalled") ||
    ($txn->NewValue eq "open") ||
    ($txn->Data eq 'Ticket auto-opened on incoming correspondence')
  ) {
  return 0;
}

return 1;

Custom action cleanup code:
my $Ticket = $self->TicketObj;
my $CFName = 'Last Reopened Date';
my $CF = RT::CustomField->new( $RT::SystemUser );
$CF->LoadByNameAndQueue( Name => $CFName, Queue => $Ticket->Queue );

my @date  = localtime(time());
my $year = $date[5] += 1900 ;
my $month = $date[4] += 1 ;
my $day = $date[3] ;
my $date_stamp = "$year-$month-$day";

$Ticket->AddCustomFieldValue( Field => $CF, Value => $date_stamp);

return 1;

Based on my quick testing here this works perfectly.


-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Tuesday, June 12, 2012 9:02 PM
To: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] Search Ticket Transaction History in Query Builder?

> SELECT Tickets.id, Tickets.Created, Tickets.Subject, Tickets.Status,
> Queues.Name FROM Transactions, Tickets, Queues WHERE
> Tickets.Queue=Queues.id AND Queues.Name="Test Queue 1"
> AND Tickets.Status='open'
> AND Transactions.ObjectId=Tickets.id
> AND Transactions.OldValue REGEXP "stalled|resolved"
> AND Transactions.NewValue='open'
> AND Transactions.Data = 'Ticket auto-opened on incoming correspondence'
> AND Transactions.Created >= @START
> AND Transactions.Created <= @FINISH

Your join between Tickets and Transactions is wrong.  Transactions also apply to objects other than tickets, so you need to limit by ObjectType too not just ObjectId.

(The query is also not quite what you want because it will miss tickets which are currently Status != 'open' but were auto-opened at some point earlier in the time frame.)

To solve your problem using RT's normal customization routes, I suggest extending the default auto-open scrip (user-defined action) to set a DateTime custom field on the ticket when it fires.  This greatly simplifies your search and lets you run it in RT from the web.

Your report can then be a standard RT saved search used in a chart or a dashboard.

________________________________

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.