[rt-users] Reports

[Bob Goldstein]

>On Fri, Nov 04, 2005 at 09:37:21AM -0500, Hersker, Steve wrote:
>> Against my will and better judgment, I have to run weekly reports for
>> management to review (for regulatory compliance). A list of all open & new
>> tickets (with details and complete ticket history for each) and all tickets
>> closed the previous week (again with details and history) are two examples.
>> 
>>  
>> 
>> Before I can put RT in production, I have to find a way to generate these
>> reports. Has anyone done anything similar? If anyone just has the generic
>> SQL statement(s) that puts together the ticket, transactions and
>> attachments, I'd appreciate it.
>> 
>
>That sounds unpleasant. 
>
>You should never try to access RT data by going to the database
>directly. The perl API is the preferred method.
>
>One option you might want to consider is writing a program that
>can drive a web browser to run the queries and print each ticket.
>It would look nicer than text reports, and you wouldn't have to
>worry about the Perl API. Just a thought.
>

  I had a similar issue, and wound up using direct SQL.
  I wanted to find all open tickets in a few selected queues,
  where the requestor was the last to correspond, and in which
  that correspondance was more than 2 days ago.  (E.g. we're
  ignoring the client, so I need to rap some knuckles.)

  I thought, go to the query builder, have it tell me the
  correct TicketSQL, then use that with the CLI in a cron job.
  But I was just unable to figure out the TicketSQL for this.

  I would never update the db without using the perl API.
  But it seems to me that using direct SQL for reports
  is fairly benign, no?  Yes, the reports can fail if an
  upgrade changes the schema in a backward non-compatible
  way, or can become ineffecient if there are new
  ways to do things.  But overall, this is a reasonable
  risk, is it not?

     bobg