On 21 Feb 2011 11:40, Chris Hall wrote: > ..so I've been racking my brain on this one for awhile. Any guidance is > appreciated. The aforementioned query works great, I just need to > integrate it into RT. However, the RT queries seem to (I'm sure for > good reason) strip off alot of that query to the point that it doesn't > work. I've tried manually setting it as the query in a custom > Results.html, still to no avail. I'm not quite sure how to proceed. > Does anyone have any suggestions?
RT's user-exposed queries are written in TicketSQL, not SQL. TicketSQL is a SQL-like DSL to describe tickets you want to find. TicketSQL doesn't search for transactions, only tickets, so you can't express your SQL query in TicketSQL. http://requesttracker.wikia.com/wiki/TicketSQL To show the results of your SQL below in RT, you'd need to modify the query to use the DBIx::SearchBuilder API and then display the results. Thomas > On Fri, Feb 11, 2011 at 11:48 AM, Kenneth Crocker <kfcroc...@lbl.gov > <mailto:kfcroc...@lbl.gov>> wrote: > > Chris, > > We do something similar to Payam. We have an Oracle DataBase so we > created a "view" of the RT data to allow us to use COGNOS (or other > reporting software) to create reports from RT data. Works great. > > Kenn > LBNL > > On Fri, Feb 11, 2011 at 1:32 AM, Payam Poursaied <pa...@rasana.net > <mailto:pa...@rasana.net>> wrote: > > You could start by database > > select > > Transactions.id,ObjectType,ObjectId,Type,Transactions.Created,Name,EmailAddr > ess from Transactions > inner join Users on Users.id=Transactions.Creator > where ObjectType='RT::Ticket' and Type='Comment' and > Transactins.Created>='YYYY-MM-DD HH:MM:SS' and > Transactions.Created<='YYYY-MM-DD HH:MM:SS'; > > But be aware of 2 things: > First: the time is not your localtime, it is GMT0 > second: if someone rather than your support team is allowed to > comment on > tickets, you would have them in the report > > > > > Date: Thu, 10 Feb 2011 16:25:10 -0500 > From: Chris Hall <hir...@gmail.com <mailto:hir...@gmail.com>> > To: rt-users@lists.bestpractical.com > <mailto:rt-users@lists.bestpractical.com> > Subject: Re: [rt-users] search by CommentedOnBy? > > I hate to necro an old, dead thread but.. this seems to have > come up again.. > from two separate people. I did figure out if I edited the > advanced section > and added __CommentedOnBy__ I could at least see who the last > person was to > comment on a ticket, but here's my current problem: > > Helpdesk manager person has some software that spits out how > many calls a > given worker does, and wants to compare this with the RT > records. For > example, Worker A takes 54 calls in a night. Helpdesk manager > person wants > to check the RT records to see if he has made 54 comments for > that given > night. Kind of a making sure ppl are doing what they're > supposed to be > doing. > > So sometime before the end of the month, I need to figure out a > way to form > a query to show all comments in a given month. Maybe not "show" > them, but > just a list to say... Worker A commented on the following > tickets this > month. > > Does anybody have any suggestions where I could start with > something like > this? > > >
