<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.24.4">
</HEAD>
<BODY>
Hello<BR>
<BR>
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)<BR>
however you can probably use it as is if you update the "for q in" line only...<BR>
<BR>
the reports created show tickets historic information per week<BR>
- tickets created per week per queue<BR>
- tickets resolved per week per queue<BR>
- currently open tickets per week<BR>
<BR>
<B><U>bash script :</U></B><BR>
<BR>
<TT>#!/bin/bash</TT><BR>
<BR>
<TT>newTickets()</TT><BR>
<TT>{ </TT><BR>
<TT>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'" </TT><BR>
<TT>}</TT><BR>
<BR>
<TT>resolvedTickets()</TT><BR>
<TT>{ </TT><BR>
<TT>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'" </TT><BR>
<TT>}</TT><BR>
<BR>
<TT>openTickets()</TT><BR>
<TT>{ </TT><BR>
<TT>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')" </TT><BR>
<TT>}</TT><BR>
<BR>
<TT>HISTORY=9</TT><BR>
<TT>FULLHISTORY=100</TT><BR>
<TT>let H=$HISTORY+1</TT><BR>
<TT>DAY=$(date "+%Y-%m-%d" -d "$H weeks ago ")</TT><BR>
<TT>HTMLPATH=/var/www/html/statistics</TT><BR>
<BR>
<TT>echo "<h1>RT statistics</h1>" > $HTMLPATH/index.html</TT><BR>
<BR>
<TT>for q in "'ops.support'":operations "'team1.support','team2.support','team3.support'":backend "'team4.support','team5.support','team6..support'":frontend </TT><BR>
<TT>do</TT><BR>
<TT> QUEUE=$(printf $q | cut -d: -f1)</TT><BR>
<TT> QUEUENAME=$(printf $q | cut -d: -f2)</TT><BR>
<TT> echo "<h3>Tickets activity for queues $QUEUE</h3>">>$HTMLPATH/index.html</TT><BR>
<TT> echo "<table><tr><td><a href=tickets_full_$QUEUENAME.png><img src=tickets_$QUEUENAME.png></a></td>">> $HTMLPATH/index.html</TT><BR>
<TT> echo > $HTMLPATH/tickets_$QUEUENAME.csv</TT><BR>
<TT> echo > $HTMLPATH/tickets_full_$QUEUENAME.csv</TT><BR>
<TT> for d in `seq $FULLHISTORY -1 0`</TT><BR>
<TT> do</TT><BR>
<TT> BEFORE=$DAY</TT><BR>
<TT> DAY=$(date "+%Y-%m-%d" -d "$d weeks ago ")</TT><BR>
<TT> [ $d -lt $HISTORY ] && printf "%s;%i;%i;%i\n" $DAY $(newTickets) $(openTickets) $(resolvedTickets) >> $HTMLPATH/tickets_$QUEUENAME.csv</TT><BR>
<TT> printf "%s;%i;%i;%i\n" $DAY $(newTickets) $(openTickets) $(resolvedTickets) >> $HTMLPATH/tickets_full_$QUEUENAME.csv</TT><BR>
<TT> done </TT><BR>
<TT> sed -e "s/QUEUE/$QUEUENAME/g" /root/RTstats/graphic.plt | gnuplot</TT><BR>
<TT> awk -F\; ' BEGIN { printf("<td><pre>%16s%16s%16s%16s\n","WeekEndingDay","NewTickets","OpenTickets","ResolvedTickets") } </TT><BR>
<TT> { printf("%16s%16s%16s%16s\n",$1,$2,$3,$4) } </TT><BR>
<TT> END { print "</pre></td></tr></table><br>" } ' $HTMLPATH/tickets_$QUEUENAME.csv >> $HTMLPATH/index.html</TT><BR>
<TT>done </TT><BR>
<BR>
<BR>
<B><U>gnuplot script :</U></B><BR>
<TT>set datafile separator ";"</TT><BR>
<TT>set terminal png small</TT><BR>
<TT>set autoscale x</TT><BR>
<TT>set mxtics 1</TT><BR>
<TT>set yrange [0:140]</TT><BR>
<TT>set xdata time</TT><BR>
<TT>set timefmt "%Y-%m-%d"</TT><BR>
<TT>set format x "w%W"</TT><BR>
<TT>set style fill solid 9 border 0 </TT><BR>
<TT>set boxwidth 0.6 relative</TT><BR>
<TT>set style line 1 lt 1 linewidth 3 pt 1 ps 0.5</TT><BR>
<TT>set style line 2 lt 2 linewidth 3 pt 2 ps 0.5</TT><BR>
<TT>set style line 3 lt 9 linewidth 3 pt 3 ps 0.5</TT><BR>
<TT>set title "tickets activity for QUEUE"</TT><BR>
<TT>set output "/var/www/html/statistics/tickets_QUEUE.png"</TT><BR>
<TT>plot '/var/www/html/statistics/tickets_QUEUE.csv' using 1:3 title "Total remaining tickets" with boxes ls 3 , \</TT><BR>
<TT> '/var/www/html/statistics/tickets_QUEUE.csv' using 1:2 title "tickets created per week" with lp ls 1 , \</TT><BR>
<TT> '/var/www/html/statistics/tickets_QUEUE.csv' using 1:4 title "tickets resolved per week" with lp ls 2 </TT><BR>
<TT>set terminal png small transparent</TT><BR>
<TT>set boxwidth 1 relative</TT><BR>
<TT>set autoscale y</TT><BR>
<TT>set title "tickets full activity for QUEUE"</TT><BR>
<TT>set format x "%m/%y"</TT><BR>
<TT>set output "/var/www/html/statistics/tickets_full_QUEUE.png"</TT><BR>
<TT>plot '/var/www/html/statistics/tickets_full_QUEUE.csv' using 1:3 title "Total remaining tickets" with boxes ls 3 , \</TT><BR>
<TT> '/var/www/html/statistics/tickets_full_QUEUE.csv' using 1:2 title "tickets created per week" with lp ls 1 , \</TT><BR>
<TT> '/var/www/html/statistics/tickets_full_QUEUE.csv' using 1:4 title "tickets resolved per week" with lp ls 2 </TT><BR>
<BR>
<BR>
<BR>
On Tue, 2009-02-24 at 15:04 +0000, Calvin Chiang wrote:<BR>
<BLOCKQUOTE TYPE=CITE>
<TT><FONT COLOR="#000000">@Oleg</FONT></TT><BR>
<TT><FONT COLOR="#000000">is the gnuplot/mysql stuff fairly straightforward?</FONT></TT><BR>
<TT><FONT COLOR="#000000">(i dont code at all)</FONT></TT><BR>
</BLOCKQUOTE>
<TABLE CELLSPACING="0" CELLPADDING="0" WIDTH="100%">
<TR>
<TD>
<PRE>
--
Oleg Sladkoff
KELKOO - Service Architect
<A HREF="mailto:oleg.sladkoff@yahoo-inc.com">oleg.sladkoff@yahoo-inc.com</A>
Yahoo messenger : Kelkoooleg
tel : +33 (0)476848147
gsm : +33(0)675768532
</PRE>
</TD>
</TR>
</TABLE>
</BODY>
</HTML>