Reporting Stats and a bug in date display in RT


#1

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:

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 ------


#2
        Created:Sat May 13 15:53:56 2000

(…)

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.

You’re right, it seems a bit messy. Anyway, if you are to make
statistics, it would be better to connect to the database by SQL.

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.

nod - I have been thinking of this, and I hope I can find some smart
solution to this.

Putting the age in days or hours would
work really well for graphing etc.

Then again, you can get it out in seconds easily from the database. I
don’t think statistical tools should go through the CLIs or the API. It’s
just no use.

The age field should also become a duration field showing the time between
ticket creation and resolution for resolved tickets.

Seems reasonable - though I can’t promise anything.

Is there a place in the database
where the resolution time is cached (or zero if the ticket isn’t resolved)?

No.

tobix@fsck.com


#3

Tobias said:

Then again, you can get it out in seconds easily from the database. I
don’t think statistical tools should go through the CLIs or
the API. It’s just no use.

Easily if I know SQL, what if I don’t, and don’t have the time to learn a
completely new language. The CLI provides a function that return the data
I need in a language I already know, be it shell, perl, tk/tcl, python etc.

I would expect the query tools provided with rt togo directly
to the database, but with potentially multiple database support,
I would think API abstraction would be a good thing ™.

As far as using the CLI’s, I think this must be supported. It took me
30 minutes to wip up the hack I posted rather than trying to learn SQL
and the table layouts etc. Text processing using shell or perl is a no
brainer for most sysadmins while having to get into the internals of
the RT database just to do some simple processing is “no use” in my opinion.

Now if I had a shell script that I could do something like:

getvals -format “%{ticket_number} %{created_date} %{resolved_date}”
-state=resolved -resolved_date > 05/01/00 -resolved_date < 05/15/00

going right to the database then that would be great.

Alternatively some documentation of the SQL and procedures necessary to do
the above (without having to real all of the MySQL documentaion) would also
suffice. But as I said using the CLI may be a hack, but it produces the data
in a form that any sysadmin with some shell programming experience
can use to get the needed reports in as little time as possible.

– rouilj


#4

getvals -format “%{ticket_number} %{created_date} %{resolved_date}”
-state=resolved -resolved_date > 05/01/00 -resolved_date < 05/15/00

with all due respect, if you’re smart enough to write a shell script, you
can learn enough SQL to do queries in about 15 minutes. it’s not in any
way a difficult language.

in the long run, knowing enough sql to get around is almost requisite for
anyone working with (modern) databases, i think.

    Blue Lang                              Unix Systems Admin
    QSP, Inc., 3200 Atlantic Ave, Ste 100, Raleigh, NC, 27604
    Home: 919 835 1540  Work: 919 875 6994  Fax: 919 872 4015

#5

Easily if I know SQL, what if I don’t, and don’t have the time to learn a
completely new language. The CLI provides a function that return the data
I need in a language I already know, be it shell, perl, tk/tcl, python etc.

SQL isn’t that hard, after all. Find some crash course at internet, and
I’d daresay you can get the grip of it in less than a day. I think the
tutorial I had when I was fresh in SQL was only some five-six pages long.

As far as using the CLI’s, I think this must be supported. It took me
30 minutes to wip up the hack I posted rather than trying to learn SQL
and the table layouts etc. Text processing using shell or perl is a no
brainer for most sysadmins while having to get into the internals of
the RT database just to do some simple processing is “no use” in my opinion.

Now if I had a shell script that I could do something like:

getvals -format “%{ticket_number} %{created_date} %{resolved_date}”
-state=resolved -resolved_date > 05/01/00 -resolved_date < 05/15/00

The reason why the SQL gets so much uglier than the suggestion above is
mostly that “resolved_date” is not stored in the database, so we have to
scan the transactions.

going right to the database then that would be great.

select each_req.serial_num, each_req.date_created, transactions.trans_date
from transactions,each_req where
transactions.serial_num=each_req.serial_num and transactions.type =
‘status’ and transactions.trans_data=‘resolved’ and
transactions.trans_date>957870772 and transactions.trans_date<958475588;

statistical queries:

select avg(transactions.trans_date-each_req.date_created) from
transactions,each_req where transactions.serial_num=each_req.serial_num
and transactions.type = ‘status’ and transactions.trans_data='resolved’
and transactions.trans_date>957870772 and
transactions.trans_date<958475588;

tobix@fsck.com