Rt-google-charts: Difference between revisions

From Request Tracker Wiki
Jump to navigation Jump to search
No edit summary
 
(added some RT4 specific information)
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. Information below is relevant to RT 3.8+
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 =
= RT4 Information =
I have recently tested the script on RT 4.0.5 and it run 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.
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 59: Line 59:


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


= Code =
= Code =


  <nowiki>#!/usr/bin/perl
  <nowiki>#!/usr/bin/perl
   
   
    # Ryan Armanasco - 2009-12-08 - ryan@slowest.net
    # Ryan Armanasco - 2009-12-08 - ryan@slowest.net
   
   
    # Extract queue statistics from RT and generate Google Charts call to graph
    # Extract queue statistics from RT and generate Google Charts call to graph
    #
    #
    # example graph: http://tinyurl.com/yk6lw63
    # example graph: http://tinyurl.com/yk6lw63
    #
    #
    # Produces stats for specified number of months, optionally including the current month,
    # 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.
    # showing created, resolved and overall outstanding at the point in time for that month.
    #
    #
    # Reopened and re-resolved tickets will skew the figures slightly.
    # 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
    # It has to do a log of database work, and it take a fair while depending on the size of your database
    #
    #
    # Indicative stats:
    # Indicative stats:
    #  to produce a 16 month cycle on a 4500 record database.  ESX clustered host with 3GHz Xeon, 2GB RAM = 6.5 seconds
    #  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 strict;
    use DBI;
    use DBI;
   
   
    # SETTINGS - CUSTOMISE THESE
    # SETTINGS - CUSTOMISE THESE
    my $queue='IT Support'; # RT queue to operate on
    my $queue='IT Support'; # RT queue to operate on
    my $cycles=16; # how many months to report on
    my $cycles=16; # how many months to report on
    my $thismonth=1; # include current month in graph?
    my $thismonth=1; # include current month in graph?
    my $graphtitle="Request Tracker $queue Queue";
    my $graphtitle="Request Tracker $queue Queue";
   
   
    # DATABASE - CUSTOMISE THESE
    # DATABASE - CUSTOMISE THESE
    my $host='pdcithelp2';
    my $host='pdcithelp2';
    my $db  ='rt';
    my $db  ='rt';
    my $user='report';
    my $user='report';
    my $pass='seH2aet3';
    my $pass='seH2aet3';
   
   
    # GRAPH DIMENSIONS - CUSTOMISE THESE
    # GRAPH DIMENSIONS - CUSTOMISE THESE
    #  (340,000 [X x Y] pixel maximum)
    #  (340,000 [X x Y] pixel maximum)
    my $chartx=850;
    my $chartx=850;
    my $charty=350;
    my $charty=350;
    die "Chart area $chartx * $charty is greater than 340,000 pixles - google will reject it\n" if $charty * $chartx &gt; 340000;
    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
    # DON'T TOUCH ANYTHING BELOW HERE
    #====================================================
    #====================================================
   
   
    # friendly month names for labels/titles
    # friendly month names for labels/titles
    my @months = qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/;
    my @months = qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/;
   
   
    # connect to RT database
    # connect to RT database
    my $dbh = DBI-&gt;connect('dbi:mysql:'.$db.';host='.$host,$user,$pass)
    my $dbh = DBI-&gt;connect('dbi:mysql:'.$db.';host='.$host,$user,$pass)
    or die "Connection Error: $DBI::errstr\n";
    or die "Connection Error: $DBI::errstr\n";
   
   
    my $data; # stores all returned/calulcated ticket stats - hashref
    my $data; # stores all returned/calulcated ticket stats - hashref
    my $max=0; # tracks maximum value to scale graph
    my $max=0; # tracks maximum value to scale graph
    my $start_date; # track starting time period for labels
    my $start_date; # track starting time period for labels
    my $end_date; # track ending time period for labels
    my $end_date; # track ending time period for labels
    my @perc; # percentage calculation during output
    my @perc; # percentage calculation during output
 
 
    # generate the decending year/month combos - previous `date --date="x months ago" has issues on the first day of the month, so here's a more fool-proof version.
    # generate the decending year/month combos - previous `date --date="x months ago" has issues on the first day of the month, so here's a more fool-proof version.
    sub makedate {
    sub makedate {
          my $ddate = shift;
          my $ddate = shift;
          my $doffset = shift;
          my $doffset = shift;
          my $year;
          my $year;
          my $month;
          my $month;
 
 
          $doffset--;
          $doffset--;
 
 
          $ddate =~ /(\d\d\d\d)-(\d\d)/;
          $ddate =~ /(\d\d\d\d)-(\d\d)/;
                  $year = $1;
                  $year = $1;
                  $month = $2;
                  $month = $2;
 
 
          for(0..$doffset) {
          for(0..$doffset) {
                  $month--;
                  $month--;
                  if ($month == 0) {
                  if ($month == 0) {
                          $month = 12;
                          $month = 12;
                          $year--;
                          $year--;
                  }
                  }
                  if (length($month) ==1) {
                  if (length($month) ==1) {
                          $month = "0".$month;
                          $month = "0".$month;
                  }
                  }
          }
          }
 
 
          if ($doffset == -2) {
          if ($doffset == -2) {
                  $month++;
                  $month++;
                  if ($month == 13) {
                  if ($month == 13) {
                          $month = 1;
                          $month = 1;
                          $year++;
                          $year++;
                  }
                  }
                  if (length($month) ==1) {
                  if (length($month) ==1) {
                          $month = "0".$month;
                          $month = "0".$month;
                  }
                  }
          }
          }
          return "$year-$month";
          return "$year-$month";
    }
    }
   
   
    # loop through requested month range
    # loop through requested month range
    for (1..$cycles) {
    for (1..$cycles) {
   
   
    # reset counters each loop
    # reset counters each loop
    my $created=0;
    my $created=0;
    my $outstanding=0;
    my $outstanding=0;
    my $resolved=0;
    my $resolved=0;
   
   
    # lazy way to work out x months before present
    # lazy way to work out x months before present
    my $offset=$_;
    my $offset=$_;
    $offset -= 1 if $thismonth;
    $offset -= 1 if $thismonth;
    # existing faulty version: my $date = `date --date="$offset months ago" +"%Y-%m"`;
    # existing faulty version: my $date = `date --date="$offset months ago" +"%Y-%m"`;
    # existing faulty version: chomp($date);
    # existing faulty version: chomp($date);
        my $date = makedate($start_date,$offset);
          my $date = makedate($start_date,$offset);
 
 
    # store info for label generation
    # store info for label generation
    $data-&gt;{date}[$_-1]=$date;
    $data-&gt;{date}[$_-1]=$date;
   
   
    # CREATED TICKET STATS
    # CREATED TICKET STATS
    my $sql = "
    my $sql = "
    select
    select
    count(*)
    count(*)
    from
    from
    Tickets
    Tickets
    where
    where
    Created like '$date-%'
    Created like '$date-%'
    and
    and
    queue = (select id from Queues where name='$queue')
    queue = (select id from Queues where name='$queue')
    and
    and
    type &lt;&gt; 'reminder'
    type &lt;&gt; 'reminder'
      and
      and
    status &lt;&gt; 'deleted';";
    status &lt;&gt; 'deleted';";
   
   
    # execute and retrieve
    # execute and retrieve
          my $sth = $dbh-&gt;prepare($sql);
          my $sth = $dbh-&gt;prepare($sql);
          $sth-&gt;execute();
          $sth-&gt;execute();
    $created = ($sth-&gt;fetchrow_array)[0];
    $created = ($sth-&gt;fetchrow_array)[0];
   
   
    # RESOLVED TICKET STATS
    # RESOLVED TICKET STATS
    my $sql = "
    my $sql = "
    select
    select
    count(distinct tr.objectid) as resolved,
    count(distinct tr.objectid) as resolved,
    round(max(datediff(ti.resolved,ti.created))) as maxage,
    round(max(datediff(ti.resolved,ti.created))) as maxage,
    round(avg(datediff(ti.resolved,ti.created))) as avgage
    round(avg(datediff(ti.resolved,ti.created))) as avgage
    from
    from
    Transactions tr,
    Transactions tr,
    Tickets ti
    Tickets ti
    where
    where
    tr.type='Status'
    tr.type='Status'
    and
    and
    (tr.newvalue='resolved' or tr.newvalue='deleted')
    (tr.newvalue='resolved' or tr.newvalue='deleted')
    and
    and
    tr.objecttype='RT::Ticket'
    tr.objecttype='RT::Ticket'
    and
    and
    ti.Queue=(select id from Queues where Name='$queue')
    ti.Queue=(select id from Queues where Name='$queue')
    and
    and
    tr.objectid = ti.id
    tr.objectid = ti.id
    and
    and
    tr.created like '$date-%';";
    tr.created like '$date-%';";
   
   
    # returns this
    # returns this
    #+---------+--------+---------+
    #+---------+--------+---------+
    #| tickets | maxage | avgage  |
    #| tickets | maxage | avgage  |
    #+---------+--------+---------+
    #+---------+--------+---------+
    #|    107 |    192 | 18.8692 |
    #|    107 |    192 | 18.8692 |
    #+---------+--------+---------+
    #+---------+--------+---------+
   
   
    # execute and retrieve
    # execute and retrieve
    my $sth = $dbh-&gt;prepare($sql);
    my $sth = $dbh-&gt;prepare($sql);
    $sth-&gt;execute();
    $sth-&gt;execute();
    ($resolved, $data-&gt;{max}[$_-1], $data-&gt;{avg}[$_-1]) = ($sth-&gt;fetchrow_array);
    ($resolved, $data-&gt;{max}[$_-1], $data-&gt;{avg}[$_-1]) = ($sth-&gt;fetchrow_array);
   
   
            # need to step date forward a month to get accurate figures
            # need to step date forward a month to get accurate figures
        # previous faulty version: my $offset=$_ - 1;
        # previous faulty version: my $offset=$_ - 1;
            # previous faulty version: $offset -= 1 if $thismonth;
            # previous faulty version: $offset -= 1 if $thismonth;
            # previous faulty version: my $dateforward = `date --date="$offset months ago" +"%Y-%m"`;
            # previous faulty version: my $dateforward = `date --date="$offset months ago" +"%Y-%m"`;
            # previous faulty version: chomp($dateforward);
            # previous faulty version: chomp($dateforward);
            my $dateforward = makedate($start_date, ($offset-1) );
            my $dateforward = makedate($start_date, ($offset-1) );
   
   
    # OUTSTANDING TICKET STATS
    # OUTSTANDING TICKET STATS
    my $sql = "
    my $sql = "
    select
    select
    count(*) as tickets
    count(*) as tickets
    from
    from
    Transactions tr,
    Transactions tr,
    Tickets ti
    Tickets ti
    where
    where
    tr.type='Create'
    tr.type='Create'
    and
    and
    tr.objecttype='RT::Ticket'
    tr.objecttype='RT::Ticket'
    and
    and
    ti.Queue=(select id from Queues where Name='$queue')
    ti.Queue=(select id from Queues where Name='$queue')
    and
    and
    tr.objectid = ti.id
    tr.objectid = ti.id
    and
    and
    ti.created &lt; '$dateforward'
    ti.created &lt; '$dateforward'
    and not
    and not
    tr.objectid in (
    tr.objectid in (
    select
    select
    distinct tr.objectid
    distinct tr.objectid
    from
    from
    Transactions tr,
    Transactions tr,
    Tickets ti
    Tickets ti
    where
    where
    tr.type='Status'
    tr.type='Status'
    and
    and
    tr.newvalue='resolved'
    tr.newvalue='resolved'
    and
    and
    tr.objecttype='RT::Ticket'
    tr.objecttype='RT::Ticket'
    and
    and
    ti.Queue=(select id from Queues where Name='$queue')
    ti.Queue=(select id from Queues where Name='$queue')
    and
    and
    tr.objectid = ti.id
    tr.objectid = ti.id
    and
    and
    tr.created &lt; '$dateforward'
    tr.created &lt; '$dateforward'
    )
    )
   
   
    and
    and
    tr.objectid not in (
    tr.objectid not in (
    select
    select
    id
    id
    from
    from
    Tickets
    Tickets
    where
    where
    id &lt;&gt; effectiveID
    id &lt;&gt; effectiveID
    )
    )
    and not
    and not
    tr.objectid in (
    tr.objectid in (
    select
    select
    id
    id
    from
    from
    Tickets
    Tickets
    where
    where
    resolved &lt; '$dateforward'
    resolved &lt; '$dateforward'
    and not
    and not
    resolved = '1970-01-01 00:00:00'
    resolved = '1970-01-01 00:00:00'
    )
    )
    and
    and
    ti.type &lt;&gt; 'reminder'
    ti.type &lt;&gt; 'reminder'
    order by tr.objectid";
    order by tr.objectid";
   
   
    # execute and retrieve
    # execute and retrieve
          my $sth = $dbh-&gt;prepare($sql);
          my $sth = $dbh-&gt;prepare($sql);
          $sth-&gt;execute();
          $sth-&gt;execute();
    $outstanding = ($sth-&gt;fetchrow_array)[0];
    $outstanding = ($sth-&gt;fetchrow_array)[0];
   
   
    # store all the data somewhere
    # store all the data somewhere
    $data-&gt;{cre}[$_-1] = $created;
    $data-&gt;{cre}[$_-1] = $created;
    $data-&gt;{out}[$_-1]=$outstanding;
    $data-&gt;{out}[$_-1]=$outstanding;
    $data-&gt;{res}[$_-1]=$resolved;
    $data-&gt;{res}[$_-1]=$resolved;
    $data-&gt;{ctd}[$_-1]=$created;
    $data-&gt;{ctd}[$_-1]=$created;
   
   
    if ($created &gt; $resolved) {
    if ($created &gt; $resolved) {
    $max = $created if $created &gt; $max;
    $max = $created if $created &gt; $max;
    } else {
    } else {
    $max = $resolved if $resolved &gt; $max;
    $max = $resolved if $resolved &gt; $max;
    }
    }
    }
    }
   
   
    $max = $max + 10 + (50-($max % 50));
    $max = $max + 10 + (50-($max % 50));
   
   
    print '&lt;img src="';
    print '&lt;img src="';
   
   
    print &lt;&lt;GRAPH;
    print &lt;&lt;GRAPH;
    http://chart.apis.google.com/chart?
    http://chart.apis.google.com/chart?
    cht=bvg
    cht=bvg
    &amp;chs=${chartx}x${charty}
    &amp;chs=${chartx}x${charty}
    &amp;chbh=a
    &amp;chbh=a
    &amp;chds=0,$max
    &amp;chds=0,$max
    &amp;chco=0033ff,1eec44,e00a0a,c342bb
    &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;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;chts=000000,20
    &amp;chf=bg,s,88888810|c,s,00000010
    &amp;chf=bg,s,88888810|c,s,00000010
    &amp;chma=30,40,50,50|100,20
    &amp;chma=30,40,50,50|100,20
    &amp;chxt=x,y,r,x,x,x,x,x
    &amp;chxt=x,y,r,x,x,x,x,x
    &amp;chdl=created|resolved|outstanding|% resolved
    &amp;chdl=created|resolved|outstanding|% resolved
    &amp;chtt=$graphtitle
    &amp;chtt=$graphtitle
    &amp;chxl=0:|
    &amp;chxl=0:|
GRAPH
  GRAPH
   
   
    # x axis MMM 'YY labels
    # x axis MMM 'YY labels
    foreach my $label (reverse(@{$data-&gt;{date}})) {
    foreach my $label (reverse(@{$data-&gt;{date}})) {
    $label =~ /\d\d(\d\d)-(\d\d)/;
    $label =~ /\d\d(\d\d)-(\d\d)/;
    print "$months[$2-1] '$1|";
    print "$months[$2-1] '$1|";
    }
    }
   
   
    # graphs scalings/axis require some calculations
    # graphs scalings/axis require some calculations
    print '|1:|0|'.sprintf('%2d',($max/2))."|$max|2:|0|50|100";
    print '|1:|0|'.sprintf('%2d',($max/2))."|$max|2:|0|50|100";
    print '|3:|';
    print '|3:|';
    print '|4:|'.'|'x(($cycles/2)-1).'|average resolved ticket age (days)';
    print '|4:|'.'|'x(($cycles/2)-1).'|average resolved ticket age (days)';
    print '|5:|'.join('|',reverse(@{$data-&gt;{avg}}));
    print '|5:|'.join('|',reverse(@{$data-&gt;{avg}}));
    print '|6:|'.'|'x(($cycles/2)-1).'|maximum resolved ticket age (days)';
    print '|6:|'.'|'x(($cycles/2)-1).'|maximum resolved ticket age (days)';
    print '|7:|'.join('|',reverse(@{$data-&gt;{max}}));
    print '|7:|'.join('|',reverse(@{$data-&gt;{max}}));
   
   
    # output the data sets
    # output the data sets
    print "&amp;chd=t3:";
    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 ); }
    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 join(',',reverse(@{$data-&gt;{ctd}})).'|'.join(',',reverse(@{$data-&gt;{res}})).'|'.join(',',reverse(@{$data-&gt;{out}})).'|'.join(',',@perc);
   
   
    print '" border=1 /&gt;';
    print '" border=1 /&gt;';
   
   
    exit;
    exit;
   
   
    </nowiki>
    </nowiki>


= Feedback =
= Feedback =

Revision as of 01:46, 14 March 2012

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
     |   `-- Tabs
     `-- index.html

Code

#!/usr/bin/perl
     
     # 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 > 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->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
   
     # generate the decending year/month combos - previous `date --date="x months ago" has issues on the first day of the month, so here's a more fool-proof version.
     sub makedate {
           my $ddate = shift;
           my $doffset = shift;
           my $year;
           my $month;
   
           $doffset--;
   
           $ddate =~ /(\d\d\d\d)-(\d\d)/;
                   $year = $1;
                   $month = $2;
   
           for(0..$doffset) {
                   $month--;
                   if ($month == 0) {
                           $month = 12;
                           $year--;
                   }
                   if (length($month) ==1) {
                           $month = "0".$month;
                   }
           }
   
           if ($doffset == -2) {
                   $month++;
                   if ($month == 13) {
                           $month = 1;
                           $year++;
                   }
                   if (length($month) ==1) {
                           $month = "0".$month;
                   }
           }
           return "$year-$month";
     }
     
     # 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;
     	# existing faulty version: my $date = `date --date="$offset months ago" +"%Y-%m"`;
     	# existing faulty version: chomp($date);
          my $date = makedate($start_date,$offset);
   
     	# store info for label generation
     	$data->{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 <> 'reminder'
     		  	and
     				status <> 'deleted';";
     
     	# execute and retrieve
           my $sth = $dbh->prepare($sql);
           $sth->execute();
     	$created = ($sth->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->prepare($sql);
     	$sth->execute();
     	($resolved, $data->{max}[$_-1], $data->{avg}[$_-1]) = ($sth->fetchrow_array);
     
             # need to step date forward a month to get accurate figures
       	  # previous faulty version: my $offset=$_ - 1;
             # previous faulty version: $offset -= 1 if $thismonth;
             # previous faulty version: my $dateforward = `date --date="$offset months ago" +"%Y-%m"`;
             # previous faulty version: chomp($dateforward);
             my $dateforward = makedate($start_date, ($offset-1) );
     
     	# 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 < '$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 < '$dateforward'
     						)
     
     			and
     				tr.objectid not in 	(
     							select
     								id
     							from
     								Tickets
     							where
     									id <> effectiveID
     							)
     			and not
     				tr.objectid in (
     						select
     							id
     						from
     							Tickets
     						where
     								resolved < '$dateforward'
     							and not
     								resolved = '1970-01-01 00:00:00'
     						)
     			and
     				ti.type <> 'reminder'
     		order by tr.objectid";
     
     	# execute and retrieve
           my $sth = $dbh->prepare($sql);
           $sth->execute();
     	$outstanding = ($sth->fetchrow_array)[0];
     
     	# store all the data somewhere
     	$data->{cre}[$_-1] = $created;
     	$data->{out}[$_-1]=$outstanding;
     	$data->{res}[$_-1]=$resolved;
     	$data->{ctd}[$_-1]=$created;
     
     	if ($created > $resolved) {
     		$max = $created if $created > $max;
     	} else {
     		$max = $resolved if $resolved > $max;
     	}
     }
     
     $max = $max + 10 + (50-($max % 50));
     
     print '<img src="';
     
     print <<GRAPH;
     http://chart.apis.google.com/chart?
     cht=bvg
     &chs=${chartx}x${charty}
     &chbh=a
     &chds=0,$max
     &chco=0033ff,1eec44,e00a0a,c342bb
     &chm=N,000000,0,-1,11|N,000000,1,-1,11|N,000000,2,-1,11|D,c342bb,3,0,4,-1
     &chts=000000,20
     &chf=bg,s,88888810|c,s,00000010
     &chma=30,40,50,50|100,20
     &chxt=x,y,r,x,x,x,x,x
     &chdl=created|resolved|outstanding|% resolved
     &chtt=$graphtitle
     &chxl=0:|
  GRAPH
     
     # x axis MMM 'YY labels
     foreach my $label (reverse(@{$data->{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->{avg}}));
     print '|6:|'.'|'x(($cycles/2)-1).'|maximum resolved ticket age (days)';
     print '|7:|'.join('|',reverse(@{$data->{max}}));
     
     # output the data sets
     print "&chd=t3:";
     for (0..$cycles-1) { $perc[$_] = sprintf('%2d',($max-2)/100*(($data->{ctd}[$cycles-1-$_]-$data->{out}[$cycles-1-$_])/$data->{ctd}[$cycles-1-$_]) * 100 ); }
     print join(',',reverse(@{$data->{ctd}})).'|'.join(',',reverse(@{$data->{res}})).'|'.join(',',reverse(@{$data->{out}})).'|'.join(',',@perc);
     
     print '" border=1 />';
     
     exit;

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

Retrieved from "https://rt-wiki.bestpractical.com/index.php?title=Rt-google-charts&oldid=3262"