[rt-users] search by CommentedOnBy?

[Thomas Sibley]

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 <kfcrocker at lbl.gov
> <mailto:kfcrocker at 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 <payam at rasana.net
>     <mailto:payam at 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 <hiro24 at gmail.com <mailto:hiro24 at gmail.com>>
>         To: rt-users at lists.bestpractical.com
>         <mailto:rt-users at 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?
> 
> 
>