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
