Report to show all tickets that were ever owned by a user

1

Is there anyway to create a report that will show any ticket that was ever owned by a particular user(regardless of who currently owns the ticket)?

It doesn’t look like it can be done through a simple search and I’m struggling to figure out how to do an advanced search… that advanced!!

Any help would be appreciated!

Thanks!
Mike.

2

Mike,

I’m not on 4.4.2 yet, so I don’t have reports, but I can tell you that historical ticket ownership would be found at the transaction level, not at the ticket level, so you won’t be able to write TicketSQL to query for that.

My understanding is that reports can access some things that ReportBuilder can’t, I think that includes transaction data. I’ll know more when I get my test site fully upgraded to 4.4.2.

3

Barton is correct that you would have to query transactions for when owner was set to the user in question. In order to do this as a custom report one way of going about it would be to make an extension that has the new report html/Reports/TicketsUsersHaveOwned.html and the a simple web UI to load search results of tickets.

Something to get you started on how the logic for the RT API could flow is this:

my $tickets = RT::Tickets->new($self->TicketObj->CurrentUser);
$tickets->LimitQueue(VALUE => 'General');

my %ticket_ids;
while (my $ticket = $tickets->Next) {
    my $transactions = RT::Transactions->new(RT->SystemUser);
    $transactions->LimitToTicket($ticket->Id);
    while(my $transaction = $transactions->Next) {
        $ticket_ids{$ticket->Id}=1 if $transaction->Field eq 'Owner' and $transaction->Type eq 'Set' and $transaction->NewValue == 14;
    }
}

Where you would then load the ticket ids collected. Depending on the size of your database this could be a costly search (As the code is now) though due to it checking every transaction.

4

Searching all tickets that a user has ever owned could take a long time, but (depending on ticket volume), tickets created on the last month, week, or day might be reasonable, perhaps something like this:

$tickets->FromSQL("Created > '1 week ago'");

That could be called before $tickets->LimitQueue(VALUE => 'General');, or that could be spun into the TicketSQL:

$tickets->FromSQL(  "Queue = 'General' "
                  . "AND Created > '1 week ago'");
5

I ended up building a report in our reporting tool that I connected to RT’s DB. I wasn’t able to figure out how to do within RT without custom coding.

The way I connected the data to get what I was looking for… (caveat, this is how I was able to get it, I’m not sure if it’s accurate yet…)

Transactions.New Value = Users.id —> this is to get the new owner’s username from Users.name

Transactions.Old Value = Users.id --> to get the old username, I only used this for debugging and validating what I was seeing.

Transactions.ObjectId = Tickets.id --> to get the Ticket number and Subject line.

Then the where clause would be on the Transactions.New Value = username.

This returns all the transactions records that represent the action of updating the owner to the new username.

For some reason I haven’t figured out yet, it shows duplicate records for every single transaction, but this gave me enough info to answer the questions I was being asked.