[rt-users] MySQL Question (joins and stuff)

[Kenneth Crocker]

  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 <lstewart at iweb.com>
Sent: 3/3/2014 3:02 PM
To: RT Users <rt-users at lists.bestpractical.com>
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 :: lstewart at iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140304/1773d30c/attachment.htm>