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.sladk...@yahoo-inc.com Yahoo messenger : Kelkoooleg tel : +33 (0)476848147 gsm : +33(0)675768532
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sa...@bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com