Suggestions for ways to get summary stats on Requestors-specific information

We populate the “Users.Organization” field with the internal Dept name
that Users belong to using a custom LDAP script via cron job.
I’d like to get summary counts of how many tickets were created &
resolved by each Dept by calendar month and eventually display it on a
web page. Any advice on making these techniques work is appreciated.

Sometimes the original “Tickets.Creator” is not the Requestor (as people
submit tickets on other people’s behalf and later change the Requestor).
This makes the query substantially more complicated (e.g. especially
outside of the API).
I’ll list some of the techniques I’ve experimented in hopes of getting
tips from this list to make some of them work: 1) write custom mysql/PHP
2) direct mysql query e.g. rt-batchstats, 3) use cli to dump info to
parse later 4) use Query builder to dump needed fields and parse later
5) Modify RT Statistics Template to group by Users.Organization 6) Write
code using the API from scratch (or better yet slightly modifying an
existing sample that comes close)

I’ve had some success listing tickets based on the Requestors Dept
(using the very helpful tips below on a PHP/Mysql syntax page), but when
I do summary stats they are sometimes a few higher then the overall
created or resolved count (e.g. even when using count(Distinct t1.id)
Useful Tips:
http://wiki.bestpractical.com/index.cgi?GetReqestorInfoByTicketId

I’ve used batch stats for some queries, but the mysql query difficulties
for summarizing requestor dept are above
http://wiki.bestpractical.com/index.cgi?RT3BatchStats

I can’t seem to get the command-line interface to work (e.g. so I can
setup a cron job to dump what I want and parse it to create web page),
This seems to be due to the fact that I use Apache auth-ldap
authentication and RT command-line doesn’t work so well with that (e.g.
I attempted .rtrc file and got below).
%/usr/local/rt3/bin/rt ls "Priority > 5 and Status=‘new’"
rt: Server error: Authorization Required (401)
http://wiki.bestpractical.com/index.cgi?UseRtTool

I can use Query builder and download fields and time period I want data
manually (to parse and summarize by Dept later) but I don’t know how to
setup a cron job to automatically drop off this data somewhere (e.g.
lynx a stored query)?

The RT Statistics package seems to use queue as the primary distinction
and not information about the user attributes of ticket Requestors.

I’ve had some success using the API information to make custom scrips
and other minor customizations to RT pages. When looking at the Wiki,
reading RT Essentials, looking at online API docs add-in and following
this newsgroup overtime I’m still confused about how to create a new
page with just the data I want. I wouldn’t know how to go about
generating a HTML page outside of the HTML-Mason environment (e.g. PHP
page using API calls?). I’m also unsure about how to create separate
page within the HTML Mason environment that summarizes ticket counts
grouped by the Ticket->Requestors User->Organization by Calendar Month.
If somebody has sample code that comes close to my objectives, I’d love
to see it, and modify as needed to make it work for me.

Thanks,
Mike

My RT Build:
FreeBSD 4.11, RT 3.2.2, Perl v5.8.4, apache+mod_ssl-1.3.33+2.8.22,
mod_perl v1.29, HTML::Mason v1.26, DBIx::SearchBuilder v1.15

Mike Patterson
Systems Manager
UC Berkeley Extension