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
[email protected] <mailto:[email protected]>
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
_____________________________________________________________________________________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [email protected]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
