Difference between revisions of "Rt-google-charts"

From Request Tracker Wiki
Jump to navigation Jump to search
 
m (9 revisions imported)
 
(8 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
= rt-google-charts graphing =
 
= rt-google-charts graphing =
  
This script will produce google charts calls for historical information on RT queues.
+
This script will produce google charts calls for historical information on RT queues. Information below is relevant to RT 3.8
  
 
You can specify the queue name and number of months to include. It will show the number of resolved, unresolved and outstanding tickets as they stood at that point in time for each month. It will also show a trend line as the percentage of resolved tickets.
 
You can specify the queue name and number of months to include. It will show the number of resolved, unresolved and outstanding tickets as they stood at that point in time for each month. It will also show a trend line as the percentage of resolved tickets.
 +
= RT4 Information =
 +
 +
I have recently tested the script on RT 4.0.5 and it ran perfectly. However the Reports section has been removed from RT4 and the interface significantly changed, so I still need to find a way to integrate it nicely in to the UI.
  
 
= Caveats =
 
= Caveats =
Line 23: Line 26:
 
  <hr />
 
  <hr />
 
  % print `/usr/local/whatever/rt-reports/rt-google-charts_1.pl`;
 
  % print `/usr/local/whatever/rt-reports/rt-google-charts_1.pl`;
 
  
 
I also altered the line not too far from the bottom which controls the tab highlighting:
 
I also altered the line not too far from the bottom which controls the tab highlighting:
  
 
  <& /Tools/Reports/Elements/Tabs, current_tab => 'Tools/Reports/6monthstats.html', Title => $title &>
 
  <& /Tools/Reports/Elements/Tabs, current_tab => 'Tools/Reports/6monthstats.html', Title => $title &>
 
  
 
Then I've referenced this file in /opt/rt3/local/html/Tools/Reports/index.html which was copied from the main code directory by copying an existing $tabs declaration and incrementing the letter it is assigned to:
 
Then I've referenced this file in /opt/rt3/local/html/Tools/Reports/index.html which was copied from the main code directory by copying an existing $tabs declaration and incrementing the letter it is assigned to:
Line 53: Line 54:
 
         description => loc('Last 6 month statistics for IT Support queue'),
 
         description => loc('Last 6 month statistics for IT Support queue'),
 
     },
 
     },
 
  
 
I then needed to do a similar thing to make the top tabs work in /opt/rt3/local/html/Tools/Reports/Elements/Tabs - also copied from main code path.
 
I then needed to do a similar thing to make the top tabs work in /opt/rt3/local/html/Tools/Reports/Elements/Tabs - also copied from main code path.
Line 60: Line 60:
  
 
  <nowiki>.
 
  <nowiki>.
|-- 6monthstats.html
+
        |-- 6monthstats.html
|-- Elements
+
        |-- Elements
|  `-- Tabs
+
        |  `-- Tabs
`-- index.html
+
        `-- index.html
</nowiki>
+
        </nowiki>
  
 
= Code =
 
= Code =
  
<nowiki>#!/usr/bin/perl
+
Massive problems with getting source code to work on this wiki all of a sudden, so here's a link to the file on dropbox temporarily: [http://dl.dropbox.com/u/8415602/rt-google-graphs.pl Dropbox link]
 
# Ryan Armanasco - 2009-12-08 - ryan@slowest.net
 
 
# Extract queue statistics from RT and generate Google Charts call to graph
 
#
 
# example graph: http://tinyurl.com/yk6lw63
 
#
 
# Produces stats for specified number of months, optionally including the current month,
 
# showing created, resolved and overall outstanding at the point in time for that month.
 
#
 
# Reopened and re-resolved tickets will skew the figures slightly.
 
#
 
# It has to do a log of database work, and it take a fair while depending on the size of your database
 
#
 
# Indicative stats:
 
#  to produce a 16 month cycle on a 4500 record database.  ESX clustered host with 3GHz Xeon, 2GB RAM = 6.5 seconds
 
 
use strict;
 
use DBI;
 
 
# SETTINGS - CUSTOMISE THESE
 
my $queue='IT Support'; # RT queue to operate on
 
my $cycles=16; # how many months to report on
 
my $thismonth=1; # include current month in graph?
 
my $graphtitle="Request Tracker $queue Queue";
 
 
# DATABASE - CUSTOMISE THESE
 
my $host='pdcithelp2';
 
my $db  ='rt';
 
my $user='report';
 
my $pass='seH2aet3';
 
 
# GRAPH DIMENSIONS - CUSTOMISE THESE
 
#  (340,000 [X x Y] pixel maximum)
 
my $chartx=850;
 
my $charty=350;
 
die "Chart area $chartx * $charty is greater than 340,000 pixles - google will reject it\n" if $charty * $chartx &gt; 340000;
 
 
#====================================================
 
# DON'T TOUCH ANYTHING BELOW HERE
 
#====================================================
 
 
# friendly month names for labels/titles
 
my @months = qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/;
 
 
# connect to RT database
 
my $dbh = DBI-&gt;connect('dbi:mysql:'.$db.';host='.$host,$user,$pass)
 
or die "Connection Error: $DBI::errstr\n";
 
 
my $data; # stores all returned/calulcated ticket stats - hashref
 
my $max=0; # tracks maximum value to scale graph
 
my $start_date; # track starting time period for labels
 
my $end_date; # track ending time period for labels
 
my @perc; # percentage calculation during output
 
 
# loop through requested month range
 
for (1..$cycles) {
 
 
# reset counters each loop
 
my $created=0;
 
my $outstanding=0;
 
my $resolved=0;
 
 
# lazy way to work out x months before present
 
my $offset=$_;
 
$offset -= 1 if $thismonth;
 
my $date = `date --date="$offset months ago" +"%Y-%m"`;
 
chomp($date);
 
# store info for label generation
 
$data-&gt;{date}[$_-1]=$date;
 
 
# CREATED TICKET STATS
 
my $sql = "
 
select
 
count(*)
 
from
 
Tickets
 
where
 
Created like '$date-%'
 
and
 
queue = (select id from Queues where name='$queue')
 
and
 
type &lt;&gt; 'reminder'
 
  and
 
status &lt;&gt; 'deleted';";
 
 
# execute and retrieve
 
        my $sth = $dbh-&gt;prepare($sql);
 
        $sth-&gt;execute();
 
$created = ($sth-&gt;fetchrow_array)[0];
 
 
# RESOLVED TICKET STATS
 
my $sql = "
 
select
 
count(distinct tr.objectid) as resolved,
 
round(max(datediff(ti.resolved,ti.created))) as maxage,
 
round(avg(datediff(ti.resolved,ti.created))) as avgage
 
from
 
Transactions tr,
 
Tickets ti
 
where
 
tr.type='Status'
 
and
 
(tr.newvalue='resolved' or tr.newvalue='deleted')
 
and
 
tr.objecttype='RT::Ticket'
 
and
 
ti.Queue=(select id from Queues where Name='$queue')
 
and
 
tr.objectid = ti.id
 
and
 
tr.created like '$date-%';";
 
 
# returns this
 
#+---------+--------+---------+
 
#| tickets | maxage | avgage  |
 
#+---------+--------+---------+
 
#|    107 |    192 | 18.8692 |
 
#+---------+--------+---------+
 
 
# execute and retrieve
 
my $sth = $dbh-&gt;prepare($sql);
 
$sth-&gt;execute();
 
($resolved, $data-&gt;{max}[$_-1], $data-&gt;{avg}[$_-1]) = ($sth-&gt;fetchrow_array);
 
 
        # need to step date forward a month to get accurate figures
 
my $offset=$_ - 1;
 
        $offset -= 1 if $thismonth;
 
        my $dateforward = `date --date="$offset months ago" +"%Y-%m"`;
 
        chomp($dateforward);
 
 
# OUTSTANDING TICKET STATS
 
my $sql = "
 
select
 
count(*) as tickets
 
from
 
Transactions tr,
 
Tickets ti
 
where
 
tr.type='Create'
 
and
 
tr.objecttype='RT::Ticket'
 
and
 
ti.Queue=(select id from Queues where Name='$queue')
 
and
 
tr.objectid = ti.id
 
and
 
ti.created &lt; '$dateforward'
 
and not
 
tr.objectid in (
 
select
 
distinct tr.objectid
 
from
 
Transactions tr,
 
Tickets ti
 
where
 
tr.type='Status'
 
and
 
tr.newvalue='resolved'
 
and
 
tr.objecttype='RT::Ticket'
 
and
 
ti.Queue=(select id from Queues where Name='$queue')
 
and
 
tr.objectid = ti.id
 
and
 
tr.created &lt; '$dateforward'
 
)
 
 
and
 
tr.objectid not in (
 
select
 
id
 
from
 
Tickets
 
where
 
id &lt;&gt; effectiveID
 
)
 
and not
 
tr.objectid in (
 
select
 
id
 
from
 
Tickets
 
where
 
resolved &lt; '$dateforward'
 
and not
 
resolved = '1970-01-01 00:00:00'
 
)
 
and
 
ti.type &lt;&gt; 'reminder'
 
order by tr.objectid";
 
 
# execute and retrieve
 
        my $sth = $dbh-&gt;prepare($sql);
 
        $sth-&gt;execute();
 
$outstanding = ($sth-&gt;fetchrow_array)[0];
 
 
# store all the data somewhere
 
$data-&gt;{cre}[$_-1] = $created;
 
$data-&gt;{out}[$_-1]=$outstanding;
 
$data-&gt;{res}[$_-1]=$resolved;
 
$data-&gt;{ctd}[$_-1]=$created;
 
 
if ($created &gt; $resolved) {
 
$max = $created if $created &gt; $max;
 
} else {
 
$max = $resolved if $resolved &gt; $max;
 
}
 
}
 
 
$max = $max + 10 + (50-($max % 50));
 
 
print '&lt;img src="';
 
 
print &lt;&lt;GRAPH;
 
http://chart.apis.google.com/chart?
 
cht=bvg
 
&amp;chs=${chartx}x${charty}
 
&amp;chbh=a
 
&amp;chds=0,$max
 
&amp;chco=0033ff,1eec44,e00a0a,c342bb
 
&amp;chm=N,000000,0,-1,11|N,000000,1,-1,11|N,000000,2,-1,11|D,c342bb,3,0,4,-1
 
&amp;chts=000000,20
 
&amp;chf=bg,s,88888810|c,s,00000010
 
&amp;chma=30,40,50,50|100,20
 
&amp;chxt=x,y,r,x,x,x,x,x
 
&amp;chdl=created|resolved|outstanding|% resolved
 
&amp;chtt=$graphtitle
 
&amp;chxl=0:|
 
GRAPH
 
 
# x axis MMM 'YY labels
 
foreach my $label (reverse(@{$data-&gt;{date}})) {
 
$label =~ /\d\d(\d\d)-(\d\d)/;
 
print "$months[$2-1] '$1|";
 
}
 
 
# graphs scalings/axis require some calculations
 
print '|1:|0|'.sprintf('%2d',($max/2))."|$max|2:|0|50|100";
 
print '|3:|';
 
print '|4:|'.'|'x(($cycles/2)-1).'|average resolved ticket age (days)';
 
print '|5:|'.join('|',reverse(@{$data-&gt;{avg}}));
 
print '|6:|'.'|'x(($cycles/2)-1).'|maximum resolved ticket age (days)';
 
print '|7:|'.join('|',reverse(@{$data-&gt;{max}}));
 
 
# output the data sets
 
print "&amp;chd=t3:";
 
for (0..$cycles-1) { $perc[$_] = sprintf('%2d',($max-2)/100*(($data-&gt;{ctd}[$cycles-1-$_]-$data-&gt;{out}[$cycles-1-$_])/$data-&gt;{ctd}[$cycles-1-$_]) * 100 ); }
 
print join(',',reverse(@{$data-&gt;{ctd}})).'|'.join(',',reverse(@{$data-&gt;{res}})).'|'.join(',',reverse(@{$data-&gt;{out}})).'|'.join(',',@perc);
 
 
print '" border=1 /&gt;';
 
 
exit;
 
 
</nowiki>
 
  
 
= Feedback =
 
= Feedback =
Line 335: Line 79:
  
 
Ryan Armanasco - [mailto:ryan@slowest.net ryan@slowest.net] - 2010-02-12
 
Ryan Armanasco - [mailto:ryan@slowest.net ryan@slowest.net] - 2010-02-12
 +
[[Category:reporting]]
 +
[[Category:graphing]]

Latest revision as of 16:36, 6 April 2016

rt-google-charts graphing

This script will produce google charts calls for historical information on RT queues. Information below is relevant to RT 3.8

You can specify the queue name and number of months to include. It will show the number of resolved, unresolved and outstanding tickets as they stood at that point in time for each month. It will also show a trend line as the percentage of resolved tickets.

RT4 Information

I have recently tested the script on RT 4.0.5 and it ran perfectly. However the Reports section has been removed from RT4 and the interface significantly changed, so I still need to find a way to integrate it nicely in to the UI.

Caveats

Due to the fact it has to crawl so much data to work out the historical states - it will take some time to complete. To produce a 16 month cycle on a 4500 record database on an ESX clustered host with 3GHz Xeon, 2GB RAM, took 6.5 seconds.

Tickets which are reopened and resolved from previous months can not be accurately accounted for without far more code, they will skew the figures. If these types of transactions make up the bulk of your RT usage, these graphs may not be useful to you.

Example

Example 6 months: http://tinyurl.com/ykoozth Example 16 months: http://tinyurl.com/yk6lw63

Integration

To include these in the Tools -> Reports section of RT you can make changes to the local directory in RT. I've not the time or motivation to learn Mason, so I've hacked it in the only way I could figure out after a few minutes of playing.

In /opt/rt3/local/html/Tools/Reports/ I copied an existing report html file from the main code directory and altered it to suit calling it 6monthstats.html In it, I've simple added:

<hr />

% print /usr/local/whatever/rt-reports/rt-google-charts_1.pl;

I also altered the line not too far from the bottom which controls the tab highlighting:

<& /Tools/Reports/Elements/Tabs, current_tab => 'Tools/Reports/6monthstats.html', Title => $title &>

Then I've referenced this file in /opt/rt3/local/html/Tools/Reports/index.html which was copied from the main code directory by copying an existing $tabs declaration and incrementing the letter it is assigned to:

my $tabs = {

    A => {
        title       => loc('Resolved by owner'),
        path        => '/Tools/Reports/ResolvedByOwner.html',
        description => loc('Examine tickets resolved in a queue, grouped by owner'),
    },
    B => {
        title       => loc('Resolved in date range'),
        path        => '/Tools/Reports/ResolvedByDates.html',
        description => loc('Examine tickets resolved in a queue between two dates'),
    },
    C => {
        title       => loc('Created in a date range'),
        path        => '/Tools/Reports/CreatedByDates.html',
        description => loc('Examine tickets created in a queue between two dates'),
    },
    D => {
        title       => loc('IT Support - 6 month statistics'),
        path        => '/Tools/Reports/6monthstats.html',
        description => loc('Last 6 month statistics for IT Support queue'),
    },

I then needed to do a similar thing to make the top tabs work in /opt/rt3/local/html/Tools/Reports/Elements/Tabs - also copied from main code path.

The tree of my /opt/rt3/local/html/Tools/Reports directory looks like this:

. |-- 6monthstats.html |-- Elements | <code>-- Tabs</code>-- index.html

Code

Massive problems with getting source code to work on this wiki all of a sudden, so here's a link to the file on dropbox temporarily: Dropbox link

Feedback

I'd love to know if you find this useful and put it to work in your environment. See contact details below.

I'm also keen to get confirmation and any improvement suggestions on the large "OUTSTANDING TICKET STATS" query in the script. It took me a long time to concoct and I've tested it the best I can - but probably many areas for improvement.

Author

Ryan Armanasco - ryan@slowest.net - 2010-02-12