Search Ticket Transaction History in Query Builder?

Brian_Schrock · June 12, 2012, 9:22pm

All,

The issues are:

 How do I search transaction history using the query builder?

 How do I convert an SQL statement like the one lower down into something I can use in a custom report?

People here in my company have asked me if they can get a report on the number of tickets whose status was changed from stalled or resolved into open during a specific time frame because of an external e-mail from a customer. I think I have the SQL statement working, but I cannot figure out how to do the same thing with RT’s query builder. I have been all over google and the docs and I am not finding anything useful to help point me in the right direction. It looks like this issue comes up every so often on the mailing list, but no one has really been answered well, I hope I fare better.

Any suggestions or help would be greatly appreciated.

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

Brian Schrock
Linux Administrator
Network Operations
The Garden City Group, Inc.
5151 Blazer Parkway Suite A
Dublin, ohio 43017
Telephone: 614-289-5457
Mobile: 614-745-5491
Email: Brian.Schrock@gardencitygroup.com

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.

Joe_Harris · June 12, 2012, 11:43pm

This is not available in the query builder. I asked the same question a while back and basically the query builder works on the tickets taw only and cannot do cross queries. I ended up writing a shell script for my needs which basically was “any update performed by any member of a particular group yesterday”. The next morning it is sent to the manager (or the group) depending on the system and manager. I can share if you’re interested. I “may” have already shared an example on the list here if you search back a month or two.On Jun 12, 2012, at 5:22 PM, Brian Schrock Brian.Schrock@gardencitygroup.com wrote:

All,

The issues are:
 How do I search transaction history using the query builder?
 How do I convert an SQL statement like the one lower down into something I can use in a custom report?
People here in my company have asked me if they can get a report on the number of tickets whose status was changed from stalled or resolved into open during a specific time frame because of an external e-mail from a customer. I think I have the SQL statement working, but I cannot figure out how to do the same thing with RT’s query builder. I have been all over google and the docs and I am not finding anything useful to help point me in the right direction. It looks like this issue comes up every so often on the mailing list, but no one has really been answered well, I hope I fare better. J

Any suggestions or help would be greatly appreciated.

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

Brian Schrock
Linux Administrator
Network Operations
The Garden City Group, Inc.
5151 Blazer Parkway Suite A
Dublin, ohio 43017
Telephone: 614-289-5457
Mobile: 614-745-5491
Email: Brian.Schrock@gardencitygroup.com

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.

Thomas_Sibley · June 13, 2012, 1:01am

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.

Brian_Schrock · June 13, 2012, 8:15pm

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@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Tuesday, June 12, 2012 9:02 PM
To: rt-users@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.

Thomas_Sibley · June 13, 2012, 8:23pm

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’)
) {

I think your logic is wrong here. You’re OR-ing all those together
instead of a combination of AND and OR with parentheses to group correctly.

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);

Avoid the localtime math and just pass:

Value => $self->TransactionObj->CreatedObj->AsString

Brian_Schrock · June 13, 2012, 8:24pm

Yep, I was just staring at that and thinking how permissive that was. Fixing it now.

Brian J. Schrock
Network Operations
The Garden City Group, Inc.
5151 Blazer Parkway Suite A
Dublin, Ohio 43017
Telephone: 614-289-5457
Email: Brian.Schrock@gcginc.comFrom: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Wednesday, June 13, 2012 4:23 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Search Ticket Transaction History in Query Builder?

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’)
) {

I think your logic is wrong here. You’re OR-ing all those together instead of a combination of AND and OR with parentheses to group correctly.

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);

Avoid the localtime math and just pass:

Value => $self->TransactionObj->CreatedObj->AsString

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.

Brian_Schrock · June 13, 2012, 8:31pm

Ok fixed it, this appears to be working.

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

return 0 unless ($type eq “Status”) || ($type eq ‘Set’ && $txn->Field eq ‘Status’);
return 0 unless ($txn->OldValue eq “resolved”) || ($txn->OldValue eq “stalled”);
return 0 unless ($txn->NewValue eq “open”);
return 0 unless ($txn->Data eq ‘Ticket auto-opened on incoming correspondence’);

return 1;

Brian J. Schrock
Network Operations
The Garden City Group, Inc.
5151 Blazer Parkway Suite A
Dublin, Ohio 43017
Telephone: 614-289-5457
Email: Brian.Schrock@gcginc.comFrom: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Brian Schrock
Sent: Wednesday, June 13, 2012 4:24 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Search Ticket Transaction History in Query Builder?

Yep, I was just staring at that and thinking how permissive that was. Fixing it now.

Brian J. Schrock
Network Operations
The Garden City Group, Inc.
5151 Blazer Parkway Suite A
Dublin, Ohio 43017
Telephone: 614-289-5457
Email: Brian.Schrock@gcginc.com

From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Thomas Sibley
Sent: Wednesday, June 13, 2012 4:23 PM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] Search Ticket Transaction History in Query Builder?

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’)
) {

I think your logic is wrong here. You’re OR-ing all those together instead of a combination of AND and OR with parentheses to group correctly.

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);

Avoid the localtime math and just pass:

Value => $self->TransactionObj->CreatedObj->AsString

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.