Find out the original queue for a ticket that has been moved

RT Users
1

I have a “master” queue where almost everyone sends requests. A clerk
looks at the tickets there and then places them in a different queue for
processing. I’d like to have a query/report that shows the ticket, the
queue it is currently in, and the queue it originated from if different
from the current queue.

The Ticket table only has the current queue, and I can’t find any
obvious way to join Transactions. Hints or better ideas?
Ernesto Hernández-Novich - Linux 2.6.18 i686 - Unix: Live free or die!
Geek by nature, Linux by choice, Debian of course.
If you can’t aptitude it, it isn’t useful or doesn’t exist.
GPG Key Fingerprint = 438C 49A2 A8C7 E7D7 1500 C507 96D6 A3D6 2F4C 85E3

2

I have a “master” queue where almost everyone sends requests. A clerk
looks at the tickets there and then places them in a different queue for
processing. I’d like to have a query/report that shows the ticket, the
queue it is currently in, and the queue it originated from if different
from the current queue.

The Ticket table only has the current queue, and I can’t find any
obvious way to join Transactions. Hints or better ideas?

I’ve got this query

select d.name as “Source Queue”,
h.name as “Destination Queue”,
u.name as “Moved by”,
t.created as “On”
from transactions t, queues d, queues h, users u
where t.objecttype = ‘RT::Ticket’
and t.type = ‘Set’
and t.field = ‘Queue’
and t.oldvalue = d.id
and t.newvalue = h.id
and t.creator = u.id
and t.objectid = ?
order t.created;

that brings the exact information needed by the report given a ticket
number as the single parameter. Now I need to turn this into an RT
custom Report, and so far I’ve been able to create the form to get the
value of the ticket and place it in the Reports tab, but so far I
haven’t been able to figure out how to translate the SQL query into RT’s
API (if possible).

I’ve looked at CreatedByDates.html,
ResolvedByDates.html,ResolvedByOwner.html and TimeWorked in the WiKi,
but they all use TicketSQL which I (obvioulsy?) cannot use. Any hints on
what to read or do?

Ernesto Hernández-Novich - Linux 2.6.18 i686 - Unix: Live free or die!
Geek by nature, Linux by choice, Debian of course.
If you can’t aptitude it, it isn’t useful or doesn’t exist.
GPG Key Fingerprint = 438C 49A2 A8C7 E7D7 1500 C507 96D6 A3D6 2F4C 85E3

3

I have a “master” queue where almost everyone sends requests. A clerk
looks at the tickets there and then places them in a different queue for
processing. I’d like to have a query/report that shows the ticket, the
queue it is currently in, and the queue it originated from if different
from the current queue.
[…]
that brings the exact information needed by the report given a ticket
number as the single parameter. Now I need to turn this into an RT
custom Report, and so far I’ve been able to create the form to get the
value of the ticket and place it in the Reports tab, but so far I
haven’t been able to figure out how to translate the SQL query into RT’s
API (if possible).

I finally managed to get the report quite right; there was an additional
requirement since tickets may move more than once (enters “General”,
then gets moved to “DBAs”, then moved onto “Applications” and so on
until closed). Having a single ticket number in $TicketNumber, I wrote
the query to find all the “move” transactions as follows:

my $Ticket = RT::Ticket->new( $session{‘CurrentUser’} );
$Ticket->LoadById( $TicketNumber );
my $Transactions = $Ticket->Transactions();
$Transactions->Limit(
FIELD => ‘Type’, OPERATOR => ‘=’, VALUE => ‘Set’,
ENTRYAGGREGATOR => ‘AND’
);
$Transactions->Limit(
FIELD => ‘Filed’, OPERATOR => ‘=’, VALUE => ‘Queue’,
ENTRYAGGREGATOR => ‘AND’
);
$Transactions->OrderBy( FIELD => ‘Created’ );
while ( my $t = $Transactions->Next() ) {
my $srcQueue = RT::Queue->new( $session{‘CurrentUser’} );
$srcQueue->LoadById( $t->OldValue );
my $dstQueue = RT::Queue->new( $session{‘CurrentUser’} );
$dstQueue->LoadById( $t->NewValue );
print "Moved from ", $srcQueue->Name,
" to ", $dstQueue->Name,
" on ", $t->Created,
" by ", $t->CreatorObj->Name;
}

Comments and suggestions for improvement are welcome :slight_smile:
Ernesto Hernández-Novich - Linux 2.6.18 i686 - Unix: Live free or die!
Geek by nature, Linux by choice, Debian of course.
If you can’t aptitude it, it isn’t useful or doesn’t exist.
GPG Key Fingerprint = 438C 49A2 A8C7 E7D7 1500 C507 96D6 A3D6 2F4C 85E3