MySQL Question (joins and stuff)

RT Users
1

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. :smiley:

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

2

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.

KennFrom: Landon Stewart lstewart@iweb.com
Sent: 3/3/2014 3:02 PM
To: RT Users rt-users@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. :smiley:

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932

3

Hi Landon, below is some sql I wrote (I’m not too good at sql so some of it
could probably be optimized). It pulls a bunch of fields both system and
custom as well as the first comment on the ticket and names the columns
something friendly. Also it compensates for timezones since the db is in
UTC.

Hopefully it helps.

The custom fields I am pulling are “Priority” (not the built-in one) and
“Category”. I have two different “Category” custom fields from two
different queues but I am showing them both in one column, hence the OR in
the first custom field join statement below.

Let me know

–begin SQL–

SELECT T.id, T.EffectiveId, DATE(CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’))
as ‘Day Created’,
CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’) as Created, CONVERT_TZ(T.Resolved,
‘+00:00’, ‘-04:00’) as Resolved,
WEEK(CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’)) as week,
MONTH(CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’)) as Month,
U3.Name as ‘Requestor’, U6.EmailAddress as ‘Requestor Email’, U5.RealName as
‘Requestor RealName’,
U4.City as ‘Requestor City’, U.name as ‘Owner’, U2.RealName as ‘Owner
RealName’, Q.id as QueueID,
Q.Name as ‘Queue Name’, T.Status, OOCF.Content as ‘Priority’,
OCF.Content as ‘Category’, OCF.ObjectId, OCF.CustomField, T.Subject,
TO_DAYS(IF(Resolved != ‘0000-00-00 00:00:00’ AND
Resolved != ‘1970-01-01 00:00:00’,
DATE(Resolved), CURDATE())) -
TO_DAYS(DATE(T.Created)) as days_open
FROM Tickets T
LEFT JOIN Users U on U.id = T.Owner
LEFT JOIN Users U2 on U2.id = T.Owner
LEFT JOIN Queues Q on Q.id = T.Queue
LEFT JOIN Users U3 on U3.id = T.Creator
LEFT JOIN Users U4 on U4.id = T.Creator
LEFT JOIN Users U5 on U5.id = T.Creator
LEFT JOIN Users U6 on U6.id = T.Creator
LEFT JOIN (ObjectCustomFieldValues OCF) on (OCF.ObjectId = T.id AND
(OCF.CustomField = ‘3’ OR OCF.CustomField = ‘5’))
LEFT JOIN (ObjectCustomFieldValues OOCF) on (OOCF.ObjectId = T.id AND
OOCF.CustomField = ‘4’)
WHERE Type = ‘ticket’
AND
(
Status IN (‘open’,‘stalled’,‘new’,‘autoclose’,‘resolved’,‘rejected’)
OR
(
Status = ‘resolved’
AND Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
)
)
ORDER BY T.id

–end SQL–

View this message in context: http://requesttracker.8502.n7.nabble.com/MySQL-Question-joins-and-stuff-tp56822p56850.html

4

Jason this is brilliant! Thank you!On 4 March 2014 13:57, j.hubbard jason.hubbard@circles.com wrote:

Hi Landon, below is some sql I wrote (I’m not too good at sql so some of it
could probably be optimized). It pulls a bunch of fields both system and
custom as well as the first comment on the ticket and names the columns
something friendly. Also it compensates for timezones since the db is in
UTC.

Hopefully it helps.

The custom fields I am pulling are “Priority” (not the built-in one) and
“Category”. I have two different “Category” custom fields from two
different queues but I am showing them both in one column, hence the OR in
the first custom field join statement below.

Let me know

–begin SQL–

SELECT T.id, T.EffectiveId, DATE(CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’))
as ‘Day Created’,
CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’) as Created,
CONVERT_TZ(T.Resolved,
‘+00:00’, ‘-04:00’) as Resolved,
WEEK(CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’)) as week,
MONTH(CONVERT_TZ(T.Created, ‘+00:00’, ‘-04:00’)) as Month,
U3.Name as ‘Requestor’, U6.EmailAddress as ‘Requestor Email’, U5.RealName
as
‘Requestor RealName’,
U4.City as ‘Requestor City’, U.name as ‘Owner’, U2.RealName as ‘Owner
RealName’, Q.id as QueueID,
Q.Name as ‘Queue Name’, T.Status, OOCF.Content as ‘Priority’,
OCF.Content as ‘Category’, OCF.ObjectId, OCF.CustomField, T.Subject,
TO_DAYS(IF(Resolved != ‘0000-00-00 00:00:00’ AND
Resolved != ‘1970-01-01 00:00:00’,
DATE(Resolved), CURDATE())) -
TO_DAYS(DATE(T.Created)) as days_open
FROM Tickets T
LEFT JOIN Users U on U.id = T.Owner
LEFT JOIN Users U2 on U2.id = T.Owner
LEFT JOIN Queues Q on Q.id = T.Queue
LEFT JOIN Users U3 on U3.id = T.Creator
LEFT JOIN Users U4 on U4.id = T.Creator
LEFT JOIN Users U5 on U5.id = T.Creator
LEFT JOIN Users U6 on U6.id = T.Creator
LEFT JOIN (ObjectCustomFieldValues OCF) on (OCF.ObjectId = T.id AND
(OCF.CustomField = ‘3’ OR OCF.CustomField = ‘5’))
LEFT JOIN (ObjectCustomFieldValues OOCF) on (OOCF.ObjectId = T.id AND
OOCF.CustomField = ‘4’)
WHERE Type = ‘ticket’
AND
(
Status IN (‘open’,‘stalled’,‘new’,‘autoclose’,‘resolved’,‘rejected’)
OR
(
Status = ‘resolved’
AND Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY)
)
)
ORDER BY T.id

–end SQL–

–
View this message in context:
http://requesttracker.8502.n7.nabble.com/MySQL-Question-joins-and-stuff-tp56822p56850.html
Sent from the Request Tracker - User mailing list archive at Nabble.com.

RT Training London, March 19-20 and Dallas May 20-21
http://bestpractical.com/training

Landon Stewart :: lstewart@iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932