[rt-devel] Reporting Stats and a bug in date display in RT

[Rouillard, John]

I was just trying to get some stats for my boss concerning how quickly
I close tickets.

In looking through the data, I came up with this output from rt.

            Created:Sat May 13 15:53:56 2000
   Last User Contact:Wed Dec 31 19:00:00 1969
        Last Contact:never
                 Due:Wed Dec 31 19:00:00 1969

Hmm, dates look wierd to me since standard format elsewhere is:

   Date: Sat, May 13 2000 17:33:58
   Taken by rouilj

For 2.0 the dates should really be standardized in the second format, it
makes
life a bit easier.

Also it would be nice is to have the age field parameter in rtq unit
settable,
so you could get it in days, weeks, hours etc. There is a big difference
between
2 months and 2 months and 3 weeks. Putting the age in days or hours would
work
really well for graphing etc.

The age field should also become a duration field showing the time between
ticket
creation and resolution for resolved tickets. Currently it just seems to do:

  TodayDate - CreationDate

which is kind of silly for resolved tickets. Is there a place in the
database
where the resolution time is cached (or zero if the ticket isn't resolved)?
This would be faster than scanning all the transactions for their date
stamp.

In any case this script might be of use for those who want to generate time
to resolution reports on tickets. Its a fast hack, but it seems to work. It
requires
gnu date's %s parameter to convert dates into seconds from 1/1/1970 and a
modern getopt.


The script has two flags. 
   -u which sets output times for 
	d - days and quarters of days,
      h - hours and quarter of hours,
      m - minutes
and
   -n - which lists the ticket number with the times.

I use it like:

 ~/bin/servicetime -n -u d `rtq -format '%n' -prio \> 49 -resolved | tail
+4`

and get:

72 1.25
84 0.0
88 4.50
100 1.0

Without the -n, I just get the times and not the ticket numbers in the left
column.

Here is the script:

----- snip snip -----
#! /bin/sh

# given a series of rt ticket numbers, loop over them and find the time
# it took to finally resolve them.

prog=`basename $0`

#define constants

# define variables
output_units=h
show_ticket_numbers=0

ticketnum=

if [ $# -eq 0 ]; then
   echo "Usage: $prog [-u m|h|s] [-n] rt_ticket_numbers"
fi


GETOPTCMD=`getopt -o u:n \
	--long units:number \
	-n $prog -- "$@"`

if [ $? != 0 ] ; then echo "$prog: Terminating..." >&2 ; exit 1 ; fi

eval set -- "$GETOPTCMD"

while true ; do
 case "$1" in
  -u|--units) output_units=$2; shift 2 ;;
  -n|--number) show_ticket_numbers=1; shift;;
  --) shift; break;;
 esac
done


for i in $@
do
DATES=`rt -show $i | \
	egrep -B1 "Created:|Status changed to resolved" | \
	 egrep 'Date:|Created:'`

CreateDate=`echo $DATES | sed -ne
's/.*Created:....\([^:]*\)\(..:..:..\).\(....\).*/\2 \1\3/' -e '1p'`

ResolveDate=`echo $DATES | tail -1 | sed -e 's/.*,
\([^:]*\)\(..:..:..\).*/\2 \1/'`

CDSec=`date +%s -d "$CreateDate"`
RDSec=`date +%s -d "$ResolveDate"`

SecDiff=`expr  $RDSec - $CDSec`  # some expr's don't handle ()'s.
MinDiff=`expr $SecDiff / 60`

if [ $show_ticket_numbers -ne 0 ]; then
  ticketnum="$i "
fi

case $output_units in
m) echo $ticketnum $MinDiff # report in minutes
   ;;

h) fullhour=`expr $MinDiff / 60` # report in hours and .25 of hour
   remainMin=`expr $MinDiff - $fullhour \* 60`

   for j in 0 1 2 3
     do
       MaxVal=`expr 15 \* $j + 7`  # round up/down to quarter hour
       if [ $remainMin -lt $MaxVal ]; then
	  echo $ticketnum $fullhour.`expr 25 \* $j`
	  break
       fi
     done
   ;;
d) HourDiff=`expr $MinDiff / 60` # report in days and .25 of days
   fulldays=`expr $HourDiff / 24`
   remainHour=`expr $HourDiff - $fulldays \* 24`

   for j in 0 1 2 3
     do
       MaxVal=`expr 6 \* $j + 3`  # round up/down to quarter day
       if [ $remainHour -lt $MaxVal ]; then
	  echo $ticketnum $fulldays.`expr 25 \* $j`
	  break
       fi
     done
   ;;

*) echo "Invalid unit parameter."
   echo "Usage: $prog [-u m|h|s] [-n] rt_ticket_numbers"

esac
done

---- end snip ------