DB Query

RT Users
1

Hi All,

I am running a script with accesses the rtdb directly (for speed purposes).
One of the requirements is that I have to
find any ticket what was resolved with a custom field entry selected.

Does anybody know off hand how to link a ticket id to the custom field entry
id in the db?

Thanks in advance,
JP

“If you make people think they’re thinking, they’ll love you; but if you
really make them think they’ll hate you.”

— Don Marquis

2

ObjectCustomFieldValues (ObjectID column is the Ticket Id)

amit

John Paul Hayes wrote:

3

I assume you’re looking at the ObjectCustomFieldValues table in the db?

This table contains both the ticket # (ObjectID), the custom field
(CustomField) and value (Content). You’ll have to get the ID of the
custom field you’re looking for from the CustomFields table.

Entries are only added to this table when CustomFieldValues have been
selected. Also make sure to look at the Disabled value for entries that
have been de-selected.

We use MySQL and RT 3.4.5, but I doubt this aspect of the schema has
changed.

John Paul Hayes wrote:

4

Hi,

In our RT we have a ticket-custom field holding the Customer Id. The
following SQL shows the number of tickets per customer id:

select
objcf1.content CustomerId, count(*) TicketCount
from
tickets, customfields cf1, objectcustomfieldvalues objcf1
where
objcf1.objectid=tickets.id
and objcf1.objecttype=‘RT::Ticket’
and objcf1.customfield=cf1.id
and cf1.name=‘Customer ID’
and tickets.status in (‘new’,‘open’,‘stalled’,‘offer’)
group by
objcf1.content

The syntax for the SQL may vary depending on the database (we’re running
Oracle).

/SteenFrom: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of John Paul
Hayes
Sent: Tuesday, May 08, 2007 6:25 PM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] DB Query

Hi All,

I am running a script with accesses the rtdb directly (for speed

purposes). One of the requirements is that I have to
find any ticket what was resolved with a custom field entry
selected.

Does anybody know off hand how to link a ticket id to the custom

field entry id in the db?

Thanks in advance,
JP





"If you make people think they're thinking, they'll love you;

but if you really make them think they’ll hate you."

- Don Marquis
5

Hi All,

Thank you for the help, very much appreciated.On 5/9/07, Steen Olesen SO@schilling.dk wrote:

Hi,

In our RT we have a ticket-custom field holding the Customer Id. The
following SQL shows the number of tickets per customer id:

select
objcf1.content CustomerId, count(*) TicketCount
from
tickets, customfields cf1, objectcustomfieldvalues objcf1
where
objcf1.objectid=tickets.id
and objcf1.objecttype=‘RT::Ticket’
and objcf1.customfield=cf1.id
and cf1.name=‘Customer ID’
and tickets.status in (‘new’,‘open’,‘stalled’,‘offer’)
group by
objcf1.content
The syntax for the SQL may vary depending on the database (we’re running
Oracle).

/Steen

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *John Paul Hayes
Sent: Tuesday, May 08, 2007 6:25 PM
To: rt-users@lists.bestpractical.com
Subject: [rt-users] DB Query

Hi All,

I am running a script with accesses the rtdb directly (for speed
purposes). One of the requirements is that I have to
find any ticket what was resolved with a custom field entry selected.

Does anybody know off hand how to link a ticket id to the custom field
entry id in the db?

Thanks in advance,
JP

–

“If you make people think they’re thinking, they’ll love you; but if you
really make them think they’ll hate you.”

— Don Marquis

“If you make people think they’re thinking, they’ll love you; but if you
really make them think they’ll hate you.”

— Don Marquis