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

[Tobias Brox]

> 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 at fsck.com