Query on Ticket-Transactions Custom Fields

Hello All:
In trying to extract values from Ticket-Transactions custom fields I have the following query: (using a ODBC connection to RT’s mysql database)

SELECT T.Id, OCFV.ObjectId, T.queue, T.subject, T.Status, T.Resolved, CF.Name, OCFV.Content
FROM ((Tickets T
LEFT JOIN ObjectCustomFieldValues OCFV ON ((OCFV.Disabled = ‘0’))AND(OCFV.ObjectId = T.id))
LEFT JOIN CustomFields CF ON (CF.id = OCFV.CustomField))
WHERE ((T.Status = ‘resolved’))
with the following results:

313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor Description Solved313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Work Status Complete313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Materials Used Solved313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor Cost 0313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Materials Cost 0313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Issues None
But when I look on RT’s web interface for the values of ticket 313 I get different values (Which are the correct values by the way, so my query is missing something or I am not using the right tables or I don’t know)

Sat Jan 13 14:12:23 2007
jruzinsky - Comments added
15 min
[Reply] [Comment]

Labor Description:

already done

Materials Used:

(no value)

Issues:

(no value)

Labor Cost:

10.25

Work Status:

Complete

Materials Cost:

(no value)

What I am finding out is that is not matching for any of the tickets that I have…
Any suggestions on improving the query will be greatly appreciated or ideas in using RT’s API would be welcome too, I am not a perl programmer though…

Carlos

Hotmail to go? Get your Hotmail, news, sports and much more! Check out the New MSN Mobile!

Hi Carlos,

This doesn’t solve your problem, but I ran your query (using Toad) against
our MySQL database and it worked as expected. Puzzling. Maybe you can
figure out where the “window crank missing” data came from by snooping the
database and work backwards from there.

Regards,
Gene

At 11:51 AM 6/22/2007, Carlos Ramon Lopez Midence wrote:

Hello All:
In trying to extract values from Ticket-Transactions custom fields I have
the following query: (using a ODBC connection to RT’s mysql database)

SELECT
T.Id,
OCFV.ObjectId,
T.queue,
T.subject,
T.Status,
T.Resolved,
CF.Name,
OCFV.Content

FROM ((Tickets T
LEFT JOIN ObjectCustomFieldValues OCFV ON ((OCFV.Disabled = ‘0’))
AND(OCFV.ObjectId = T.id))
LEFT JOIN CustomFields CF ON (CF.id = OCFV.CustomField))
WHERE ((T.Status = ‘resolved’))

with the following results:

313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor
Description Solved
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Work
Status Complete
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
Materials Used Solved
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor
Cost 0
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
Materials Cost 0
313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000
Issues None

But when I look on RT’s web interface for the values of ticket 313 I get
different values (Which are the correct values by the way, so my query is
missing something or I am not using the right tables or I don’t know)

What I am finding out is that is not matching for any of the tickets that
I have…
Any suggestions on improving the query will be greatly appreciated or
ideas in using RT’s API would be welcome too, I am not a perl programmer
though…

Gene LeDuc, GSEC
Security Analyst
San Diego State University

Having tried the direct database connection myself, I would actually
recommend against doing it even if the data comes back as expected. Use
the API. Makes things so much simpler. Trust me.

Mathew
Keep up with my goings on at http://theillien.blogspot.com

Gene LeDuc wrote: