[rt-users] question on reporting

[Todd Chapman]

On Thu, Dec 15, 2005 at 01:40:41PM -0500, Hersker, Steve wrote:
> I am in a similar situation in that I need to generate reports out of RT
> (for SOX compliance). We're using a commercial helpdesk ticketing app now,
> but it's missing some key features that RT has (and RT is *much* easier to
> use and infinitely more flexible). Before I could begin to convert to RT, I
> had to make sure I could get the reports out of it that I needed. 
> 
> My SQL skills are minimal, so I opted to quickly "prototype" the reports
> using MS Access and the mySQL ODBC connector. Stephen is right in that the
> db schema takes some getting used to. (But definitely doable) I found the
> easiest method in Access was to create several (read: many) layered queries
> to assemble the ticket. Then, I just base the reports off queries with the
> appropriate criteria (e.g. All new/open/stalled tickets; All tickets closed
> in the last 7 days; All tickets for an Organization & date range, etc).
> 
> Stephen also brings up a good point about the export list feature in to
> Excel. But, in my situation, I need the ticket history included in the
> report, not just the header info/stats. 
> 
> (Now I'm using the API to import my users and, hopefully, the ticket history
> from the existing app - about 21,000 tickets).
> 
> If you think you're going to use Access, let me know. I'd be happy to share
> what I've learned, tripped over and worked-around.
> 
> Good Luck
> Steve
> 

All of this is a lot of work and potentially wasted effort if the
schema changes in future versions of RT. We do all our reporting
with perl scripts that use the RT API. The reports are run from
cron and even devlivered to interested parties as resolved RT
tickets, so that we have a convienient place to store the reports
for later reference.

-Todd