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 <[email protected]> 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 <[email protected]> > 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 <[email protected]> > Date: 24 November 2013 22:11 > Subject: trying to calculate time worked per transaction / ticket using SQL > To: [email protected] > > > 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. >
