Custom Fields and Tickets in RT DB Schema

RT Users
1

I’ve been trying to understand how custom fields relate to tickets in the
RT3 schema, and I can’t make that connection. I see transactions related
to the ticket and the setting of custom fields, but it doesn’t seem to
have the custom field value in the transaction. I need some help making
this db-level connection.

Thanks!
Christian Gilmore

smime.p7s (7.73 KB)

2

Christian Gilmore wrote:

I’ve been trying to understand how custom fields relate to tickets in the
RT3 schema, and I can’t make that connection. I see transactions related
to the ticket and the setting of custom fields, but it doesn’t seem to
have the custom field value in the transaction. I need some help making
this db-level connection.

My advice: don’t. Use the REST interface. It’s guaranteed* not to change,
even in RT 4.0 where the schema will most assuredly be completely different.

  • Based on previous emails on the list from Jesse.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

3

Thanks, Tom, for the reply. Based on your recommendation, I searched for
the REST documentation and found REST - Request Tracker Wiki.
I probably should have mentioned what I want to do. I’d like to be able to
get a report on the breakdown of tickets by a particular custom field’s
values. I don’t see an obvious way to do that through the REST interface.
I don’t see a way through the standard web search interface to do it
wholesale, just one-by-one. Any ideas on how best to get these metrics?

Thanks,
Christian

From:
Tom Lahti toml@bitstatement.net
To:
Christian Gilmore/Austin/IBM@IBMUS
Cc:
rt-users@lists.bestpractical.com
Date:
06/01/2009 04:40 PM
Subject:
Re: [rt-users] Custom Fields and Tickets in RT DB Schema

Christian Gilmore wrote:

I’ve been trying to understand how custom fields relate to tickets in
the
RT3 schema, and I can’t make that connection. I see transactions related

to the ticket and the setting of custom fields, but it doesn’t seem to
have the custom field value in the transaction. I need some help making
this db-level connection.

My advice: don’t. Use the REST interface. It’s guaranteed* not to
change,
even in RT 4.0 where the schema will most assuredly be completely
different.

  • Based on previous emails on the list from Jesse.

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

smime.p7s (7.73 KB)

4

Christian Gilmore wrote:

Thanks, Tom, for the reply. Based on your recommendation, I searched for
the REST documentation and found REST - Request Tracker Wiki.
I probably should have mentioned what I want to do. I’d like to be able to
get a report on the breakdown of tickets by a particular custom field’s
values. I don’t see an obvious way to do that through the REST interface.
I don’t see a way through the standard web search interface to do it
wholesale, just one-by-one. Any ideas on how best to get these metrics?

If you can do the queries in TicketSQL, then you can do it with REST.

/REST/1.0/search/ticket/?query=<TicketSQL_query>&format=s

This will give you a list of tickets, which you can count.

The TicketSQL can be “CF.{Fieldname} = ‘something’” or “CF.{Fieldname} > 0
and CF.{FieldName} < 100”, or whatever.

Or, if using my ruby library rt-client:

rt = RT_Client.new
count = rt.list(:query => “CF.{Fieldname} = ‘something’”).size

– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

5

There is Chart fnctionality in RT that allows you to do this.

For example use guest/guest credentials and the following link:
http://rt3.fsck.com/Search/Chart.html?Order=ASC&Query=Queue+%3D+'rt3'+AND+(Status+%3D+'new'+OR+Status+%3D+'open'+OR+Status+%3D+'stalled')&Rows=50&OrderBy=id&Format= +++'<B><A+HREF%3D"__WebPath__%2FTicket%2FDisplay.html%3Fid%3D__id__">__id__<%2Fa><%2FB>%2FTITLE%3A%23'%2C +++'<B><A+HREF%3D"__WebPath__%2FTicket%2FDisplay.html%3Fid%3D__id__">__Subject__<%2Fa><%2FB>%2FTITLE%3ASubject'%2C +++Status%2C +++QueueName%2C+ +++OwnerName%2C+ +++Priority%2C+ +++'__NEWLINE__'%2C +++''%2C+ +++'<small>__Requestors__<%2Fsmall>'%2C +++'<small>__CreatedRelative__<%2Fsmall>'%2C +++'<small>__ToldRelative__<%2Fsmall>'%2C +++'<small>__LastUpdatedRelative__<%2Fsmall>'%2C +++'<small>__TimeLeft__<%2Fsmall>'&ChartStyle=bar&PrimaryGroupBy=CF.{49}

If you enable the query logging then you can steal SQL and figure out relations.On Tue, Jun 2, 2009 at 2:00 AM, Christian Gilmore cag@us.ibm.com wrote:

Thanks, Tom, for the reply. Based on your recommendation, I searched for
the REST documentation and found REST - Request Tracker Wiki. I
probably should have mentioned what I want to do. I’d like to be able to get
a report on the breakdown of tickets by a particular custom field’s values.
I don’t see an obvious way to do that through the REST interface. I don’t
see a way through the standard web search interface to do it wholesale, just
one-by-one. Any ideas on how best to get these metrics?

Thanks,
Christian

From: Tom Lahti toml@bitstatement.net
To: Christian Gilmore/Austin/IBM@IBMUS
Cc: rt-users@lists.bestpractical.com
Date: 06/01/2009 04:40 PM
Subject: Re: [rt-users] Custom Fields and Tickets in RT DB Schema

Christian Gilmore wrote:

I’ve been trying to understand how custom fields relate to tickets in the
RT3 schema, and I can’t make that connection. I see transactions related
to the ticket and the setting of custom fields, but it doesn’t seem to
have the custom field value in the transaction. I need some help making
this db-level connection.

My advice: don’t. Use the REST interface. It’s guaranteed* not to change,
even in RT 4.0 where the schema will most assuredly be completely different.

  • Based on previous emails on the list from Jesse.

–
– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

The rt-users Archives

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

Best regards, Ruslan.

6

Thanks! After tweaking your example, I think I have what I need for this
particular exercise. I couldn’t find much documentation on the built-in
Chart feature. Is there any available?

Thanks,
Christian Gilmore

From:
Ruslan Zakirov ruslan.zakirov@gmail.com
To:
Christian Gilmore/Austin/IBM@IBMUS
Cc:
rt-users@lists.bestpractical.com
Date:
06/01/2009 05:29 PM
Subject:
Re: [rt-users] Custom Fields and Tickets in RT DB Schema

There is Chart fnctionality in RT that allows you to do this.

For example use guest/guest credentials and the following link:
http://rt3.fsck.com/Search/Chart.html?Order=ASC&Query=Queue+%3D+'rt3'+AND+(Status+%3D+'new'+OR+Status+%3D+'open'+OR+Status+%3D+'stalled')&Rows=50&OrderBy=id&Format= +++'<B><A+HREF%3D"__WebPath__%2FTicket%2FDisplay.html%3Fid%3D__id__">__id__<%2Fa><%2FB>%2FTITLE%3A%23'%2C +++'<B><A+HREF%3D"__WebPath__%2FTicket%2FDisplay.html%3Fid%3D__id__">__Subject__<%2Fa><%2FB>%2FTITLE%3ASubject'%2C +++Status%2C +++QueueName%2C+ +++OwnerName%2C+ +++Priority%2C+ +++'__NEWLINE__'%2C +++''%2C+ +++'<small>__Requestors__<%2Fsmall>'%2C +++'<small>__CreatedRelative__<%2Fsmall>'%2C +++'<small>__ToldRelative__<%2Fsmall>'%2C +++'<small>__LastUpdatedRelative__<%2Fsmall>'%2C +++'<small>__TimeLeft__<%2Fsmall>'&ChartStyle=bar&PrimaryGroupBy=CF.{49}

If you enable the query logging then you can steal SQL and figure out
relations.

Thanks, Tom, for the reply. Based on your recommendation, I searched
for
the REST documentation and found REST - Request Tracker Wiki
. I
probably should have mentioned what I want to do. I’d like to be able to
get
a report on the breakdown of tickets by a particular custom field’s
values.
I don’t see an obvious way to do that through the REST interface. I
don’t
see a way through the standard web search interface to do it wholesale,
just
one-by-one. Any ideas on how best to get these metrics?

Thanks,
Christian

From: Tom Lahti toml@bitstatement.net
To: Christian Gilmore/Austin/IBM@IBMUS
Cc: rt-users@lists.bestpractical.com
Date: 06/01/2009 04:40 PM
Subject: Re: [rt-users] Custom Fields and Tickets in RT DB Schema

Christian Gilmore wrote:

I’ve been trying to understand how custom fields relate to tickets in
the
RT3 schema, and I can’t make that connection. I see transactions
related
to the ticket and the setting of custom fields, but it doesn’t seem to
have the custom field value in the transaction. I need some help making
this db-level connection.

My advice: don’t. Use the REST interface. It’s guaranteed* not to
change,
even in RT 4.0 where the schema will most assuredly be completely
different.

  • Based on previous emails on the list from Jesse.

–
– ============================
Tom Lahti
BIT Statement LLC

(425)251-0833 x 117
http://www.bitstatement.net/
– ============================

The rt-users Archives

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

Best regards, Ruslan.

smime.p7s (7.73 KB)