Trying to calculate time worked per transaction / ticket using SQL

Chris_Herrmann2 · November 24, 2013, 11:11am

Hi all,

I’m trying to put together a SQL query to present a view, that allows
summaries of data to be performed in reporting tools. I have a feeling that
this question has been asked before and answered, but my google-fu is
failing me, so apologies in advance… I’ve only found questions about
using RT-REST, for example (which I don’t want to do).

I’m using RT 3.8.7 (yes I know it’s old and it’s in the pipeline to upgrade
but we have a bunch of other systems that are integrated with RT and so
it’s not a simple “just upgrade RT” project for us.

Anyway, what I want to end up with is the following fields:

Tickets.EffectiveID
Queues.Name
Tickets.Owner
Transactions.Creator
Transactions.TimeTaken
Transactions.Created
Tickets.Status
Tickets.Started
Tickets.Resolved
Tickets.Created
Transactions.Type

So i can easily point various reports at it and work from there.

the sql I’m using is…
SELECT
Tickets.EffectiveId AS TicketID,
Queues.Name AS Queue,
Tickets.Owner AS OwnerID,
Transactions.Creator AS TransactionCreatorID,
Transactions.TimeTaken AS TimeTaken,
Transactions.Created AS TransactionCreated,
Tickets.Status AS TicketStatus,
Tickets.Started AS TicketStarted,
Tickets.Resolved AS TicketResolved,
Tickets.Created AS TicketCreated,
Transactions.Type AS TransactionType
FROM
((((Tickets
JOIN Transactions ON ((Transactions.ObjectId = Tickets.id)))
JOIN Queues ON ((Queues.id = Tickets.Queue)))))

but I’m not getting the results I expect…

or even something far simpler like:
SELECT
Transactions.Creator,
sum(Transactions.TimeTaken/60) AS TimeInHours,
Month(Transactions.Created) AS TransactionMonth,
Year(Transactions.Created) AS TransactionYear
FROM
Tickets JOIN Transactions ON Transactions.ObjectId = Tickets.id
where Transactions.Created > “2013-10-01”
group by Creator, Month(Transactions.Created), Year(Transactions.Created)

just to try and compare the numbers… that I’m seeing with timeworked.pl…

Now my problem is that the numbers I’m getting don’t match those returned
by REST - for example using the timeworked.pl script…

Is there a definitive SQL somewhere that I should use to return these?..
and any pointers on what transaction types I should be avoiding or how to
avoid double counting merged transactions I would be very grateful…

Thankyou!

Chris

Chris_Herrmann2 · December 2, 2013, 7:18am

Hi all,

Just bumping this one - does anyone have any suggestions? Am I approaching
this the wrong way? Using MySQL btw if that makes a difference.

Thanks,

Chris---------- Forwarded message ----------
From: Chris Herrmann chrisherrmann7@gmail.com
Date: 24 November 2013 22:11
Subject: trying to calculate time worked per transaction / ticket using SQL
To: rt-users@lists.bestpractical.com

Hi all,

I’m trying to put together a SQL query to present a view, that allows
summaries of data to be performed in reporting tools. I have a feeling that
this question has been asked before and answered, but my google-fu is
failing me, so apologies in advance… I’ve only found questions about
using RT-REST, for example (which I don’t want to do).

I’m using RT 3.8.7 (yes I know it’s old and it’s in the pipeline to upgrade
but we have a bunch of other systems that are integrated with RT and so
it’s not a simple “just upgrade RT” project for us.

Anyway, what I want to end up with is the following fields:

Tickets.EffectiveID
Queues.Name
Tickets.Owner
Transactions.Creator
Transactions.TimeTaken
Transactions.Created
Tickets.Status
Tickets.Started
Tickets.Resolved
Tickets.Created
Transactions.Type

So i can easily point various reports at it and work from there.

the sql I’m using is…
SELECT
Tickets.EffectiveId AS TicketID,
Queues.Name AS Queue,
Tickets.Owner AS OwnerID,
Transactions.Creator AS TransactionCreatorID,
Transactions.TimeTaken AS TimeTaken,
Transactions.Created AS TransactionCreated,
Tickets.Status AS TicketStatus,
Tickets.Started AS TicketStarted,
Tickets.Resolved AS TicketResolved,
Tickets.Created AS TicketCreated,
Transactions.Type AS TransactionType
FROM
((((Tickets
JOIN Transactions ON ((Transactions.ObjectId = Tickets.id)))
JOIN Queues ON ((Queues.id = Tickets.Queue)))))

but I’m not getting the results I expect…

or even something far simpler like:
SELECT
Transactions.Creator,
sum(Transactions.TimeTaken/60) AS TimeInHours,
Month(Transactions.Created) AS TransactionMonth,
Year(Transactions.Created) AS TransactionYear
FROM
Tickets JOIN Transactions ON Transactions.ObjectId = Tickets.id
where Transactions.Created > “2013-10-01”
group by Creator, Month(Transactions.Created), Year(Transactions.Created)

just to try and compare the numbers… that I’m seeing with timeworked.pl…

Now my problem is that the numbers I’m getting don’t match those returned
by REST - for example using the timeworked.pl script…

Is there a definitive SQL somewhere that I should use to return these?..
and any pointers on what transaction types I should be avoiding or how to
avoid double counting merged transactions I would be very grateful…

Thankyou!

Chris

Tom_Lahti1 · December 3, 2013, 12:07am

Why don’t use you want to use REST? You’re shooting yourself in the foot; when you upgrade RT, your queries will no longer work and will have to be updated. If you use the REST interface, you can upgrade with impunity and not have to worry about your queries not working anymore.

—
TomOn Dec 1, 2013, at 11:18 PM, Chris Herrmann <chrisherrmann7@gmail.com mailto:chrisherrmann7@gmail.com> wrote:

Hi all,

Just bumping this one - does anyone have any suggestions? Am I approaching this the wrong way? Using MySQL btw if that makes a difference.

Thanks,

Chris

---------- Forwarded message ----------
From: Chris Herrmann <chrisherrmann7@gmail.com mailto:chrisherrmann7@gmail.com>
Date: 24 November 2013 22:11
Subject: trying to calculate time worked per transaction / ticket using SQL
To: rt-users@lists.bestpractical.com mailto:rt-users@lists.bestpractical.com

Hi all,

I’m trying to put together a SQL query to present a view, that allows summaries of data to be performed in reporting tools. I have a feeling that this question has been asked before and answered, but my google-fu is failing me, so apologies in advance… I’ve only found questions about using RT-REST, for example (which I don’t want to do).

I’m using RT 3.8.7 (yes I know it’s old and it’s in the pipeline to upgrade but we have a bunch of other systems that are integrated with RT and so it’s not a simple “just upgrade RT” project for us.

Anyway, what I want to end up with is the following fields:

Tickets.EffectiveID
Queues.Name
Tickets.Owner
Transactions.Creator
Transactions.TimeTaken
Transactions.Created
Tickets.Status
Tickets.Started
Tickets.Resolved
Tickets.Created
Transactions.Type

So i can easily point various reports at it and work from there.

the sql I’m using is…
SELECT
Tickets.EffectiveId AS TicketID,
Queues.Name AS Queue,
Tickets.Owner AS OwnerID,
Transactions.Creator AS TransactionCreatorID,
Transactions.TimeTaken AS TimeTaken,
Transactions.Created AS TransactionCreated,
Tickets.Status AS TicketStatus,
Tickets.Started AS TicketStarted,
Tickets.Resolved AS TicketResolved,
Tickets.Created AS TicketCreated,
Transactions.Type AS TransactionType
FROM
((((Tickets
JOIN Transactions ON ((Transactions.ObjectId = Tickets.id)))
JOIN Queues ON ((Queues.id = Tickets.Queue)))))

but I’m not getting the results I expect…

or even something far simpler like:
SELECT
Transactions.Creator,
sum(Transactions.TimeTaken/60) AS TimeInHours,
Month(Transactions.Created) AS TransactionMonth,
Year(Transactions.Created) AS TransactionYear
FROM
Tickets JOIN Transactions ON Transactions.ObjectId = Tickets.id
where Transactions.Created > “2013-10-01”
group by Creator, Month(Transactions.Created), Year(Transactions.Created)

just to try and compare the numbers… that I’m seeing with timeworked.pl…

Now my problem is that the numbers I’m getting don’t match those returned by REST - for example using the timeworked.plhttp://timeworked.pl/ script…

Is there a definitive SQL somewhere that I should use to return these?.. and any pointers on what transaction types I should be avoiding or how to avoid double counting merged transactions I would be very grateful…

Thankyou!

Chris

This e-mail message is confidential and is intended solely for the use of the addressee(s) named above. If you are not the intended recipient, or the person responsible to deliver it to the recipient, you are hereby advised that any dissemination, distribution or copying of this communication is prohibited. If you have received this e-mail in error, please notify the sender by return e-mail. Thank you.

Chris_Herrmann2 · December 4, 2013, 7:09am

Hi Tom,

Thanks for your reply. A couple of reasons why I’ve started this way -
primarily probably familiarity. But also:

Speed - database access is significantly faster than REST
I can run queries against a replica of the database, thereby not
interfering with the production system
It fits better with how we access and aggregate data from other systems
(i.e. SQL is the only common language that they all share)
We already have several systems pulling data from RT via SQL since 2002
My SQL skills are significantly better than my (non-existent) Perl skills
so trying to access data as data makes more sense to me - I’ve been stymied
before by Perl when trying to query using RTs built-in query engine for
example. I don’t know but I would guess that REST will also use perl syntax
for querying?

If using REST is it possible to make use of query caches etc? I realise
that “under the hood” the sql engine will still have it’s own indexes /
query caches / etc, but curious if REST also has any of this?

Regards,

ChrisOn 3 December 2013 11:07, Tom Lahti tlahti@dmsolutions.com wrote:

Why don’t use you want to use REST? You’re shooting yourself in the
foot; when you upgrade RT, your queries will no longer work and will have
to be updated. If you use the REST interface, you can upgrade with
impunity and not have to worry about your queries not working anymore.

—
Tom

On Dec 1, 2013, at 11:18 PM, Chris Herrmann chrisherrmann7@gmail.com wrote:

Hi all,

Just bumping this one - does anyone have any suggestions? Am I
approaching this the wrong way? Using MySQL btw if that makes a difference.

Thanks,

Chris

---------- Forwarded message ----------
From: Chris Herrmann chrisherrmann7@gmail.com
Date: 24 November 2013 22:11
Subject: trying to calculate time worked per transaction / ticket using SQL
To: rt-users@lists.bestpractical.com

Hi all,

I’m trying to put together a SQL query to present a view, that allows
summaries of data to be performed in reporting tools. I have a feeling that
this question has been asked before and answered, but my google-fu is
failing me, so apologies in advance… I’ve only found questions about
using RT-REST, for example (which I don’t want to do).

I’m using RT 3.8.7 (yes I know it’s old and it’s in the pipeline to
upgrade but we have a bunch of other systems that are integrated with RT
and so it’s not a simple “just upgrade RT” project for us.

Anyway, what I want to end up with is the following fields:

Tickets.EffectiveID
Queues.Name
Tickets.Owner
Transactions.Creator
Transactions.TimeTaken
Transactions.Created
Tickets.Status
Tickets.Started
Tickets.Resolved
Tickets.Created
Transactions.Type

So i can easily point various reports at it and work from there.

the sql I’m using is…
SELECT
Tickets.EffectiveId AS TicketID,
Queues.Name AS Queue,
Tickets.Owner AS OwnerID,
Transactions.Creator AS TransactionCreatorID,
Transactions.TimeTaken AS TimeTaken,
Transactions.Created AS TransactionCreated,
Tickets.Status AS TicketStatus,
Tickets.Started AS TicketStarted,
Tickets.Resolved AS TicketResolved,
Tickets.Created AS TicketCreated,
Transactions.Type AS TransactionType
FROM
((((Tickets
JOIN Transactions ON ((Transactions.ObjectId = Tickets.id)))
JOIN Queues ON ((Queues.id = Tickets.Queue)))))

but I’m not getting the results I expect…

or even something far simpler like:
SELECT
Transactions.Creator,
sum(Transactions.TimeTaken/60) AS TimeInHours,
Month(Transactions.Created) AS TransactionMonth,
Year(Transactions.Created) AS TransactionYear
FROM
Tickets JOIN Transactions ON Transactions.ObjectId = Tickets.id
where Transactions.Created > “2013-10-01”
group by Creator, Month(Transactions.Created), Year(Transactions.Created)

just to try and compare the numbers… that I’m seeing with
timeworked.pl…

Now my problem is that the numbers I’m getting don’t match those
returned by REST - for example using the timeworked.pl script…

Is there a definitive SQL somewhere that I should use to return
these?.. and any pointers on what transaction types I should be avoiding
or how to avoid double counting merged transactions I would be very
grateful…

Thankyou!

Chris

This e-mail message is confidential and is intended solely for the use of
the addressee(s) named above. If you are not the intended recipient, or the
person responsible to deliver it to the recipient, you are hereby advised
that any dissemination, distribution or copying of this communication is
prohibited. If you have received this e-mail in error, please notify the
sender by return e-mail. Thank you.