[rt-users] Suggestions for ways to get summary stats on Requestors-specific information

[Mike Patterson]

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)

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

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

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

4)
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)?

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

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