[rt-users] anybody using RTx::Statistics

[Oleg Sladkoff]

Hello

If you want to adapt it , then you need to know bash/awk/sql/gnuplot but
this is not complicated (only a few lines of code)
however you can probably use it as is if you update the "for q in" line
only...

the reports created show tickets historic information per week
- tickets created per week per queue
- tickets resolved per week per queue
- currently open tickets per week

bash script :

#!/bin/bash

newTickets()
{ 
mysql -urt3 -pxxxxxxxx -h myhost rt3 -N -e "select count(*) from
Tickets,Queues where Tickets.queue=Queues.id and Queues.name in ($QUEUE)
and date(Tickets.Created) between '$BEFORE' and '$DAY'"  
}

resolvedTickets()
{ 
mysql -urt3 -pxxxxxxxx -h myhost rt3 -N -e "select count(*) from
Tickets,Queues where Tickets.queue=Queues.id and Queues.name in ($QUEUE)
and date(Tickets.Resolved) between '$BEFORE' and '$DAY'" 
}

openTickets()
{ 
mysql -urt3 -pxxxxxxxx -h myhost rt3 -N -e "select count(*) from
Tickets,Queues where Tickets.queue=Queues.id and Queues.name in ($QUEUE)
and date(Tickets.Created)<='$DAY' and ( date(Tickets.Resolved)>'$DAY' or
status='new' or status='open' or status='stalled')" 
}

HISTORY=9
FULLHISTORY=100
let H=$HISTORY+1
DAY=$(date "+%Y-%m-%d" -d "$H weeks ago ")
HTMLPATH=/var/www/html/statistics

echo "<h1>RT statistics</h1>" > $HTMLPATH/index.html

for q in  "'ops.support'":operations
"'team1.support','team2.support','team3.support'":backend
"'team4.support','team5.support','team6..support'":frontend 
do
  QUEUE=$(printf $q | cut -d: -f1)
  QUEUENAME=$(printf $q | cut -d: -f2)
  echo "<h3>Tickets activity for queues
$QUEUE</h3>">>$HTMLPATH/index.html
  echo "<table><tr><td><a href=tickets_full_$QUEUENAME.png><img
src=tickets_$QUEUENAME.png></a></td>">> $HTMLPATH/index.html
  echo > $HTMLPATH/tickets_$QUEUENAME.csv
  echo > $HTMLPATH/tickets_full_$QUEUENAME.csv
  for d in  `seq $FULLHISTORY -1 0`
  do
    BEFORE=$DAY
    DAY=$(date "+%Y-%m-%d" -d "$d weeks ago ")
    [ $d -lt $HISTORY ] && printf "%s;%i;%i;%i\n" $DAY $(newTickets)
$(openTickets) $(resolvedTickets) >>  $HTMLPATH/tickets_$QUEUENAME.csv
    printf "%s;%i;%i;%i\n" $DAY $(newTickets) $(openTickets)
$(resolvedTickets) >>  $HTMLPATH/tickets_full_$QUEUENAME.csv
  done 
  sed -e "s/QUEUE/$QUEUENAME/g" /root/RTstats/graphic.plt | gnuplot
  awk -F\; ' BEGIN { printf("<td><pre>%16s%16s%16s%16s
\n","WeekEndingDay","NewTickets","OpenTickets","ResolvedTickets") } 
                   { printf("%16s%16s%16s%16s\n",$1,$2,$3,$4) } 
             END   { print "</pre></td></tr></table><br>" } '
$HTMLPATH/tickets_$QUEUENAME.csv  >> $HTMLPATH/index.html
done 


gnuplot script :
set datafile separator ";"
set terminal png small
set autoscale x
set mxtics 1
set yrange [0:140]
set xdata time
set timefmt "%Y-%m-%d"
set format x "w%W"
set style fill solid 9 border 0 
set boxwidth 0.6 relative
set style line 1 lt 1 linewidth 3 pt 1 ps 0.5
set style line 2 lt 2 linewidth 3 pt 2 ps 0.5
set style line 3 lt 9 linewidth 3 pt 3 ps 0.5
set title "tickets activity for QUEUE"
set output "/var/www/html/statistics/tickets_QUEUE.png"
plot '/var/www/html/statistics/tickets_QUEUE.csv' using 1:3 title "Total
remaining tickets" with boxes ls 3   , \
     '/var/www/html/statistics/tickets_QUEUE.csv' using 1:2 title
"tickets created per week" with lp ls 1 , \
     '/var/www/html/statistics/tickets_QUEUE.csv' using 1:4 title
"tickets resolved per week" with lp ls 2 
set terminal png small transparent
set boxwidth 1 relative
set autoscale y
set title "tickets full activity for QUEUE"
set format x "%m/%y"
set output "/var/www/html/statistics/tickets_full_QUEUE.png"
plot '/var/www/html/statistics/tickets_full_QUEUE.csv' using 1:3 title
"Total remaining tickets" with boxes ls 3   , \
     '/var/www/html/statistics/tickets_full_QUEUE.csv' using 1:2 title
"tickets created per week" with lp ls 1 , \
     '/var/www/html/statistics/tickets_full_QUEUE.csv' using 1:4 title
"tickets resolved per week" with lp ls 2 



On Tue, 2009-02-24 at 15:04 +0000, Calvin Chiang wrote:

> @Oleg
> is the gnuplot/mysql stuff fairly straightforward?
> (i dont code at all)

-- 
Oleg Sladkoff   
KELKOO - Service Architect
oleg.sladkoff at yahoo-inc.com
Yahoo messenger : Kelkoooleg
tel : +33 (0)476848147 
gsm : +33(0)675768532
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090224/d917c415/attachment.htm>