Landon, I wrote some SQL to create reporting views for another system. Our RT was on Oracle, but the SQL might be similar enough to use as an example. I create a flattened view of Various Custom Fields, including Dates.
Let me know if you're interested. Kenn Sent from my Windows Phone ------------------------------ From: Landon Stewart <[email protected]> Sent: 3/3/2014 3:02 PM To: RT Users <[email protected]> Subject: [rt-users] MySQL Question (joins and stuff) Hello, I'd never modify the database without the API but I'd like a flattened version of the data as a snapshot every so often for statistical purposes. If I run the following MySQL query I basically get a line for every CustomField Value and it duplicates all the T.* fields while writing new data for the OCFV.* values on each line of course. SELECT T.id,Q.Name,T.Subject,T.Status,T.Created,T.Resolved,CF.Name,OCFV.Content FROM Tickets T, ObjectCustomFieldValues OCFV,CustomFields CF,Queues Q WHERE T.id = OCFV.ObjectID AND OCFV.CustomField = CF.id AND T.Queue = Q.id AND Q.Name = "Incidents" AND T.Status != "abandoned" AND OCFV.Disabled = 0 LIMIT 1000; OUTPUT: id Name Subject Status Created Resolved Name Content 16478020 Incidents open resolver - This host is most likely running an open DNS resolver. open 2013-09-19 19:19:41 2013-10-04 16:25:04 Constituency EDUNET 16478020 Incidents open resolver - This host is most likely running an open DNS resolver. open 2013-09-19 19:19:41 2013-10-04 16:25:04 IP 10.0.0.220 16478020 Incidents open resolver - This host is most likely running an open DNS resolver. open 2013-09-19 19:19:41 2013-10-04 16:25:04 CCName XXXX 16478020 Incidents open resolver - This host is most likely running an open DNS resolver. open 2013-09-19 19:19:41 2013-10-04 16:25:04 ClientName Johnny Appleseed 16478020 Incidents open resolver - This host is most likely running an open DNS resolver. open 2013-09-19 19:19:41 2013-10-04 16:25:04 Customer 9877659 16478020 Incidents open resolver - This host is most likely running an open DNS resolver. open 2013-09-19 19:19:41 2013-10-04 16:25:04 PreferredLanguage English What I'd like to do is have the output with the T.* columns like normal and each CF.Name as a column name with the value from OCFV.Content would be desired. I think I know this involves using the right INNER or OUTER or FULL JOIN or sub-queries or something but I'm afraid that's over my head here. I'm familiar with JOINs but not turning a table on it's side. It's either this or have a ridiculous amount of output feed into some ridiculous kludgy script to reformat it. If anyone knows how I could flatten this data so a snapshot of each ticket (within a date range based on Tickets.Created or Tickets.Resolved) on one line with CF names as columns and CF values as values can be achieved I would really really appreciate it. Failing that if anyone knows of any tips to figure this out (like a primer on turning tables on their side) I'd appreciate any advice you can give me. :-D -- Landon Stewart :: [email protected] Lead Specialist, Abuse and Security Management Spécialiste principal, gestion des abus et sécurité http://iweb.com :: +1 (888) 909-4932
-- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training
