<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
HI Oleg,<br>
<br>
that's brilliant thanks!<br>
i'll jump in and get my hands dirty<br>
<br>
Cheers<br>
<br>
Oleg Sladkoff wrote:
<blockquote
cite="mid:1235491121.14998.15.camel@prettyenter.grenoble.corp.yahoo.com"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta name="GENERATOR" content="GtkHTML/3.24.4">
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 cellpadding="0" cellspacing="0" width="100%">
<tbody>
<tr>
<td>
<pre>--
Oleg Sladkoff
KELKOO - Service Architect
<a moz-do-not-send="true" 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>
</tbody>
</table>
<br>
______________________________________________________________________<br>
This email has been scanned by the MessageLabs Email Security System.<br>
For more information please visit <a class="moz-txt-link-freetext" href="http://www.messagelabs.com/email">http://www.messagelabs.com/email</a> <br>
______________________________________________________________________<br>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Calvin Chiang
Network Admin
Utilyx
1st Floor, 55 North Wharf Road
Paddington
London, W2 1LA
Tel: 020 7087 8673
<a class="moz-txt-link-abbreviated" href="http://www.utilyx.com">www.utilyx.com</a>
</pre>
<BR>
______________________________________________________________________<BR>
"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.<BR>
______________________________________________________________________<BR>
This email has been scanned by the MessageLabs Email Security System.<BR>
For more information please visit http://www.messagelabs.com/email <BR>
______________________________________________________________________<BR>
</body>
</html>