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)
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
