Reporting on active/busy tickets

RT Users
1

Hi,

I’d like to produce a report of the top 10 tickets which have been updated in the last week ordered by sum of correspond transactions. This is to identify tickets where there’s a lot of back and forth between us and a client.

Any idea how I can do this in TicketSQL ?

Simon.

2

Not sure you can with TicketSQL, as it doesn’t let you do the required grouping/ordering or get access to the count of transactions associated with a ticket.

You can do it in the underlying full SQL in the database of course:

select Tickets.id, Tickets.LastUpdated, count(Transactions.ObjectId) from Tickets, Transactions where Tickets.LastUpdated > date_sub(now(), interval 7 day) and Transactions.ObjectType = "RT::Ticket" and Transactions.ObjectId = Tickets.id group by Tickets.id order by count(Transactions.ObjectId) desc limit 10;

Where we’ve had requests for reports for management information that has needed searching transactions, that TicketSQL can’t manage, we’ve created a directory of:

/opt/rt4/local/Reports

and then popped an RT style “html” file in that that embeds the require MySQL search and formatting. We also have a local version of the html file Elements/Tabs that has an extra line for each of these reports to make them appear in the “Reports” menu.

So for example we wrote a report that looks at tickets that have swapped between queues in a given period. The HTML file that embeds this MySQL query lives in:

/opt/rt4/local/html/Reports/TicketsSwappingQueues.html

The contents of this file look like this:

<%ARGS>
$daysAgo => 30
</%ARGS>
<%INIT>
my $title = loc("Tickets that have been manually swapped between queues");
my $query = '';
</%INIT>
<& /Elements/Header, Title => $title &>
<& /Elements/Tabs &>

<form method="post" action="TicketsSwappingQueues.html">

% my $sql = 'select Users.Name as username, Users.id as userId, oldQueue.Name as oldQueueName, oldQueue.Id as oldQueueId, newQueue.Name as newQueueName, newQueue.Id as newQueueId, Transactions.ObjectId as ticketId, Tickets.Subject as ticketSubject, Tickets.Status as ticketStatus, Transactions.Created as swappedDateTime from Users, Queues as oldQueue, Queues as newQueue, Transactions, Tickets where Transactions.ObjectType = "RT::Ticket" and Transactions.Field="Queue" and Transactions.Type = "Set" and Transactions.Creator = Users.id and Transactions.OldValue = oldQueue.id and Transactions.NewValue = newQueue.id and Users.Name != "RT_System" and Transactions.created > date_sub(now(), interval ? day) and Transactions.ObjectId = Tickets.id order by Transactions.created';
% my $handle = RT::Handle->new();
% $handle->Connect();
% my $sh = $handle->dbh->prepare($sql);
% $sh->execute($daysAgo);
% if($sh->rows) {
%    print "<p>Tickets that have been switched between queues manually in the last $daysAgo days:</p>\n";
%    print "<table border=1><tr><th>Ticket ID</th><th>Subject</th><th>Old Queue</th><th>New Queue</th><th>Username</th><th>When</th><th>Current status</th></tr>\n";
%    while(my $row = $sh->fetchrow_hashref()) {
%        print "<tr><td><a href='/Ticket/Display.html?id=$row->{'ticketid'}'>$row->{'ticketid'}</a></td>";
%        print "<td><a href='/Ticket/Display.html?id=$row->{'ticketid'}'>$row->{'ticketsubject'}</a></td>";  
%        print "<td><a href='/Search/Results.html?Query=Queue\%3D\%27$row->{'oldqueuename'}\%27'>$row->{'oldqueuename'}</a></td>";
%        print "<td><a href='/Search/Results.html?Query=Queue\%3D\%27$row->{'newqueuename'}\%27'>$row->{'newqueuename'}</a></td>";
%        print "<td><a href='/User/Summary.html?id=$row->{'userid'}'>$row->{'username'}</a></td>";
%        print "<td>$row->{'swappeddatetime'}</td>";
%        print "<td>$row->{'ticketstatus'}</td>";
%        print "</tr>\n";
%    }
%    print "</table>\n";
% } else {
%    print "<p>No tickets have been switched between queues manually in the last $daysAgo days.</p>\n";
% }
% $sh->finish;


<hr />

<br /><&|/l&>Days to search back</&>: <input name="daysAgo" value="<%$daysAgo%>">


<& /Elements/Submit &>
</form>

Then in the file /opt/rt4/local/Elements/Tabs we have an extra bit of code to include this in the Reports menu:

$reports->child( ticketsswappingqueues =>
        title       => loc('Tickets that have been manually swapped between queues'),
        path        => '/Reports/TicketsSwappingQueues.html',
        description => loc('List tickets that have been manually swapped between queues within a given number of days before now'),
    );

OK, its not a report that the users can build for themselves from scratch as you can with TicketSQL, but its fine for “canned” management reports that are written once by us techies and then run occasionally by the management types. As you can see from the example above you can even slip in some parameters that they can tweak (in this case how many days back to search for, defaulting to 30).

That’s the great thing about RT - you can extend its functionality quite easily with a bit of Perl and MySQL tinkering. :slight_smile:

1 Like
3

thanks for this detailed setup, very interesting and hackable.
Is it explained somewhere in the wiki ? It definitely should.

1 Like