[rt-users] Reporting on TIcket "Requestors"

[Chris Haag]

RT 3.2.1

We run our reports directly off of the RT database. I'm having trouble 
determining the reference key to associate a Ticket with its 
Requester(s). I have searched through every table and through the wiki. 
I understand that these values can be reached via some custom perl code:

TicketObj->Requestors.value


However, we already have an extensive, SQL based dashboard, I'd prefer 
to figure out the correct query.


This sql grabs the Ticket creator. I need to JOIN to whatever table 
holds the "watchers" and specify the watchers of type "Requestor" .

Any advice or pointers will be appreciated.

many thanks, Chris

SQL BELOW


my $query = '
SELECT u.name,
  c.Content AS customer,
  t.Id
FROM Tickets t
  LEFT JOIN TicketCustomFieldValues c
  ON t.Id = c.Ticket AND c.CustomField = 4, Users u
WHERE t.Creator = u.id
AND t.Queue  in (1,8)  
AND DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= t.Created';

Here are the tables in our schema:
mysql> show tables;
+-------------------------+
| Tables_in_rt3           |
+-------------------------+
| ACL                     |
| Attachments             |
| Attributes              |
| CachedGroupMembers      |
| CustomFieldValues       |
| CustomFields            |
| FM_ArticleCFValues      |
| FM_Articles             |
| FM_ClassCustomFields    |
| FM_Classes              |
| FM_CustomFieldValues    |
| FM_CustomFields         |
| FM_Transactions         |
| GroupMembers            |
| Groups                  |
| Links                   |
| Principals              |
| Queues                  |
| ScripActions            |
| ScripConditions         |
| Scrips                  |
| Templates               |
| TicketCustomFieldValues |
| Tickets                 |
| Transactions            |
| Users                   |
| sessions                |
+-------------------------+
27 rows in set (0.00 sec)