[rt-users] RT2 Stats

[Matthew D. Stock]

Jesse Vincent writes:
 > I've actually started on one of these too ;)
 > 
 > ftp://ftp.fsck.com/pub/rt/contrib/2.0/rt-addons/HeadsUp
 > 
 > This is the basis of the managment reporting tools that will be included
 > in 2.2.  If folks want to put their energy into extending it, I'd be thrilled.

I'm working on some report queries, and I'd love to be able to use the
existing HeadsUp-style reports.  The problem that I'm facing is that to get
the kinds of reports I want, it seems to be much more efficient to write
queries direct to the database.  Skipping the official interfaces isn't
ideal though, and so I'm hoping that I'm just missing something.

Here's one simple example, though I have other reports that use similar
methods:

Instead of iterating over the set of all tickets (or even a large subset)
and counting up the number of tickets created and resolved in each month, I
just have the database do it, returning an answer in seconds even on a
fairly large database:

select count(*), year(Created), month(Created) from Tickets where Status
!= 'dead' and Queue = ? and id = EffectiveId group by year(Created),
month(Created)

select count(*), year(LastUpdated), month(LastUpdated) from Tickets where
Status = 'resolved' and Queue = ? and id = EffectiveId group by
year(LastUpdated), month(LastUpdated)



Jesse (or others), if you think that the existing search API can do this
sort of thing, please let me know.  What method do you recommend?
Thanks,
  -Matt