[rt-users] trying to calculate time worked per transaction / ticket using SQL

[Chris Herrmann]

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,

Chris


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.
>
>  —
> Tom
>
>  On Dec 1, 2013, at 11:18 PM, Chris Herrmann <chrisherrmann7 at 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 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:
>
>  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.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131204/141e6ae2/attachment.htm>