Location of requestors in the database


I’m in the process of writing a few SQL queries to generate reports from
the RT 3.0.8 database. I’ve got queries to do everything except this:

I want to list resolved and open tickets from the past month, grouped by
their Requestors. I’ve looked in the Transactions table, and in the
Tickets table, and pretty much everywhere, and I can’t find where the
Requestor IDs are being stored. I know that they’re recorded in the
Transactions table if you add them after the fact, but they must be
stored somewhere else, because when a user submits something via e-mail
they become the Requestor and there isn’t a corresponding AddWatcher
record in the Transactions table. I could use the Creator field in the
Tickets table, but it’s not always reliable since we manually enter
tickets for people from time to time.

Has anyone figured this out?

Benjamin Ortega Minnesota Population Center
Applications Programmer University of Minnesota
ortega@hist.umn.edu http://www.pop.umn.edu


A ticket’s requestors are represented by a group:

select * from Groups where Domain = 'RT::Ticket-Role’
and Type = 'Requestor’
and Instance =

[similarly for Cc, Owner and AdminCc by varying the Type spec]

So the members of this group will give you the requestors.

It might be better to use a perl script to get this info from the RT API.


At Monday 4/5/2004 12:53 PM, Benjamin Ortega wrote: