Selecting data directly from RT db

RT Users
1

So, I’m doing a little python CGI that pulls data from the RT database,
but some things are a little off:

  • Sometimes counting the number of tickets ‘new’ and ‘open’ on a select
    gives a slightly different value than doing the same thing on RT query
    builder, like 3 or 4 tickets difference. Any tought why?

  • How I select custom fields from tickets? Are they associated with
    queue or in other database?

Thanks in advance for any help.

[]s
Jose P. E. Junior

2

Hello Jose,

Custom field values are associated with the ticket number.

Table ObjectCustomFieldValues has 5 fields that are likely to be of
interest to you:
CustomField: has the id of the custom field (cross-ref to CustomFields:Id)
ObjectType: you probably want this to be “RT::Ticket”
ObjectId: this is the ticket id (cross-ref to Tickets::Id)
Content: this has the value of the custom field if it is less than 256
characters
LargeContent: this has the value of the custom field if it’s too big to
fit in Content

There are other fields as well, but their exploration is left to the reader.

Regards,
Gene

At 01:57 PM 6/13/2007, José de Paula Eufrásio Júnior wrote:

So, I’m doing a little python CGI that pulls data from the RT database,
but some things are a little off:

  • Sometimes counting the number of tickets ‘new’ and ‘open’ on a select
    gives a slightly different value than doing the same thing on RT query
    builder, like 3 or 4 tickets difference. Any tought why?

  • How I select custom fields from tickets? Are they associated with queue
    or in other database?

Thanks in advance for any help.

s
Jose P. E. Junior

Gene LeDuc, GSEC
Security Analyst
San Diego State University

3

Gene LeDuc wrote:

Hello Jose,

Custom field values are associated with the ticket number.

I did the magic with custom fields and some joins, but there’s something
bugging me now: where RT stores the information about the ‘Requestor’ of
a ticket?

s
core

4

In the DB, Tickets.Creator should be the requestor. This is a reference to Users.id.

-SeanFrom: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of José de Paula Eufrásio Júnior
Sent: Monday, June 18, 2007 3:40 PM
To: RT-Users@lists.bestpractical.com
Subject: Re: [rt-users] Selecting data directly from RT db

Gene LeDuc wrote:

Hello Jose,

Custom field values are associated with the ticket number.

I did the magic with custom fields and some joins, but there’s something bugging me now: where RT stores the information about the ‘Requestor’ of a ticket?

s
core
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Community help: http://wiki.bestpractical.com Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

This email (including any attachments) is for its intended-recipient’s
use only. This email may contain information that is confidential or
privileged. If you received this email in error, please immediately
advise the sender by replying to this email and then delete this message
from your system. For further information and disclaimers that apply to
this email, see [http://www.viacom.com/email_disclaimer.jhtml].

5

At Monday 6/18/2007 03:47 PM, you wrote:

In the DB, Tickets.Creator should be the requestor. This is a
reference to Users.id.

-Sean

No, Creator is the person who created the ticket. If you create a
ticket in the web interface and specify someone else as the
requestor, you are the creator.

Requestors are stored in a group related to the ticket - as always,
it’s best to use the RT API to query the database, then you don’t
have to go through the pain of figuring out where everything is.

Steve

6

Jeff Platter wrote:

There should be a creator integer field on the tickets table. That integer
associates with a user id for the requestor.

Sometimes people from the helpdesk get a call and create the ticket,
making the helpdesk person the creator.
The strange thing is on RT interface the “Requestor” value shows whoever
I set as ‘requestor’, but the ‘creator’ field on the Database always
point to whoever created the ticket.

s