[rt-users] search by CommentedOnBy?

[Chris Hall]

Sorry for the ignorance here, but I'm trying to muddle my way through this.
 I've installed DBIx::SearchBuilder via cpan and I've made a copy of the
Results.html found in /opt/rt3/share/html/Search, and called it
Results_custom.html.  I've put the following code in:

use DBIx::SearchBuilder::Handle;

my $handle = DBIx::SearchBuilder::Handle->new();

$handle->Connect(
        Driver          =>      'mysql',
        Database        =>      'rt3',
        Host            =>      'localhost',
        User            =>      'rt_user',
        Password        =>      'abcd1234'
);

my ($title, $ticketcount);
$session{'i'}++;
$session{'tickets'} = RT::Tickets->new($session{'CurrentUser'}) ;
$session{'tickets'}->$handle->FetchResult("select
Transactions.id,ObjectType,ObjectId,Type,Transactions.Created,Name,EmailAddress
from Transactions inner join Users on Users.id=Transactions.Creator where
ObjectType='RT::Ticket' and Type='Comment' and
Transactions.Created>='2011-02-04 01:00:00' and
Transactions.Created<='2011-02-07 23:59:00';");

in place of:


my ($title, $ticketcount);
$session{'i'}++;
$session{'tickets'} = RT::Tickets->new($session{'CurrentUser'}) ;
$session{'tickets'}->FromSQL($Query) if ($Query);



to try to force a query, but it seems to return no results.  I can't seem to
find a place to troubleshoot this from, so it's kinda like shooting in the
dark.  I was hoping maybe somebody could give me a little insight as to
where I'm mis-stepping.  Again, sorry about all the trouble and questions
about this.

On Mon, Feb 21, 2011 at 11:59 AM, Thomas Sibley <trs at bestpractical.com>wrote:

> 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?
> >
> >
> >
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110222/5c003699/attachment.htm>