[rt-users] anybody using RTx::Statistics

[Calvin Chiang]

HI Oleg,

that's brilliant thanks!
i'll jump in and get my hands dirty

Cheers

Oleg Sladkoff wrote:
> 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 <mailto:oleg.sladkoff at yahoo-inc.com>
> Yahoo messenger : Kelkoooleg
> tel : +33 (0)476848147 
> gsm : +33(0)675768532
>         
>
>
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________

-- 
Calvin Chiang
Network Admin
Utilyx
1st Floor, 55 North Wharf Road
Paddington
London, W2 1LA
Tel: 020 7087 8673
www.utilyx.com



______________________________________________________________________
"Utilyx" is the trading name of "Utilyx Limited" and "Utilyx Risk Management Limited" (URML). URML is authorised and regulated by the Financial Services Authority (FSA). This message contains information that may be privileged or confidential and is the property of Utilyx. It is intended only for the person to whom it is addressed. No confidentiality or privilege is waived or lost by any mistransmission. Any views or opinions expressed in this message are solely those of the author and do not necessarily represent those of Utilyx. Unless otherwise stated, any pricing information given in this message is indicative only and does not constitute an offer to deal at any price quoted. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090224/1cf9190b/attachment.htm>