[rt-users] Re: RT 2 stats help (fwd)

[David M Dennis]

forwarded per request.

---------- Forwarded message ----------
Date: Tue, 03 Feb 2004 11:11:36 +1100
From: Teo de Hesselle <tdehesse at uts.edu.au>
To: David M Dennis <dmd at speakeasy.org>
Cc: rt-users at lists.bestpractical.com
Subject: Re: RT 2 stats help

On Tue, 2004-02-03 at 10:53, David M Dennis wrote:
> Dear Teo,
>
> I have spent the better part of today looking through archives
> for RT 2 statistics.  Apparently all the links are gone.
>
> Would like to upgrade to RT3, but thats not an option currently.
> Was wondering if you could provide a link to the stats perl source
> referenced in
>
> http://peabody.itd.uts.edu.au/rt2stats ?

Eww... That code really is horrible. It simply doesn't scale at all.
Probably would be workable if the output was pre-generated though. It
was pretty, though.

If you really want it, it's at http://lisa.itd.uts.edu.au/rt-stats/


Here's what we replaced it with - this is a small shell script which
works for us against RT 2.0.8. The output is suitable for piping to your
system's sendmail program. You will need to know what your Queue's ID
number is in the database. Other than that, it works quite nicely.



#!/bin/bash
#
# Copyright 2003, Teo de Hesselle <tdehesse at uts.edu.au>
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation; either version 2 of the License, or
#    (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License
#    along with this program; if not, write to the Free Software
#    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
#
#  I would appreciate it if useful modifications were mailed to me at the
#  email address above. Thank you.
#

if [ -z "$1" ]; then
 echo Usage: $0 queue_id
 exit 1;
fi

FROM="RT Stats <void at uts.edu.au>"
PATH=/usr/local/bin:/usr/bin

FIRST=`date -u -d "12am 7 days ago" +"%y-%m-%d %H:%M:%S"`
LAST=`date -u -d "12am today" +"%y-%m-%d %H:%M:%S"`

QUEUE=`echo "SELECT Name FROM Queues WHERE id=$1" | mysql --batch -N -u
root rt2`

SUBJECT="$QUEUE RT Queue Statistics for week ending `date -d "12pm
yesterday" +"%B %d %Y"`"

echo "From: $FROM
Subject: $SUBJECT

$SUBJECT


Tickets Created this week:

Date            Tickets
==========      ======= "

echo "
  SELECT Created
  FROM Tickets
  WHERE Queue=$1
   AND Created>\"$FIRST\"
   AND Created<\"$LAST\";" \
  | mysql --batch -N -u root rt2 \
  | perl -e 'my %days;while(<>){my ($a, $b) = split(/\s/); $days{$a}++;
}; for $k (keys (%days)){ print "$k:\t$days{$k}\n";}' \
  | sort


echo
echo
echo "Tickets resolved this week by Resolver:

Count   Name
=====   ====================="

echo "
  SELECT COUNT(Tickets.id) as resolvecount, Users.RealName
  FROM Transactions, Users, Tickets
  WHERE Tickets.id=Transactions.Ticket
   AND Users.id = Transactions.Creator
   AND Transactions.Created > \"$FIRST\"
   AND Transactions.Created < \"$LAST\"
   AND Tickets.Queue = $1
   AND NewValue='resolved'
  GROUP BY Users.Realname
  ORDER BY resolvecount DESC;" | mysql --batch -N -u root rt2

echo
echo
echo
echo "These statistics are for the period between"
echo "`date -d "12am 7 days ago" +"12am %d %B %Y"` and `date -d "12am
today" +"12am %d %B %Y"`"


-- 
 ,-_|\   Teo de Hesselle,
/     \  Unix Systems Administrator,
\_,-\_*
     v   University of Technology, Sydney.



UTS CRICOS Provider Code:  00099F

DISCLAIMER
========================================================================
This email message and any accompanying attachments may contain
confidential information.  If you are not the intended recipient, do not
read, use, disseminate, distribute or copy this message or attachments.
If you have received this message in error, please notify the sender
immediately and delete this message. Any views expressed in this message
are those of the individual sender, except where the sender expressly,
and with authority, states them to be the views the University of
Technology Sydney. Before opening any attachments, please check them for
viruses and defects.
========================================================================