[rt-users] trying to calculate time worked per transaction / ticket using SQL
chrisherrmann7 at gmail.com
Wed Dec 4 02:09:15 EST 2013
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
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?
On 3 December 2013 11:07, Tom Lahti <tlahti at 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.
> On Dec 1, 2013, at 11:18 PM, Chris Herrmann <chrisherrmann7 at gmail.com>
> 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.
> ---------- Forwarded message ----------
> From: Chris Herrmann <chrisherrmann7 at gmail.com>
> Date: 24 November 2013 22:11
> Subject: trying to calculate time worked per transaction / ticket using SQL
> To: rt-users at 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:
> So i can easily point various reports at it and work from there.
> the sql I'm using is...
> 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
> 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:
> sum(Transactions.TimeTaken/60) AS TimeInHours,
> Month(Transactions.Created) AS TransactionMonth,
> Year(Transactions.Created) AS TransactionYear
> 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
> 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
> 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.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the rt-users