[rt-users] search by CommentedOnBy?

[Kevin Falcone]

On Tue, Feb 22, 2011 at 01:10:00PM -0500, Chris Hall wrote:
>    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.

This seems the wrong way to go about it.  Also, DBIx::SearchBuilder is
required for RT, so I'd assume you have it installed already.

I'd build a portlet in local/html/Elements that makes an RT::Tickets
object (which is a DBIx::SearchBuilder object) and calls Limit to
build the queries you want and then passes the result to
CollectionList.

You can add that portlet to your HomepageComponents and add it to your
front page like any other search once you get there.

You may want to take apart the other portlets in HomepageCOmponents to
get an idea of what is going on.

-kevin


>    On Mon, Feb 21, 2011 at 11:59 AM, Thomas Sibley <[1]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. [2]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 <[3]kfcrocker at lbl.gov
>      > <mailto:[4]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 <[5]payam at rasana.net
>      > <mailto:[6]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 <[7]hiro24 at gmail.com <mailto:[8]hiro24 at gmail.com>>
>      > To: [9]rt-users at lists.bestpractical.com
>      > <mailto:[10]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?
>      >
>      >
>      >
> 
> References
> 
>    Visible links
>    1. mailto:trs at bestpractical.com
>    2. http://requesttracker.wikia.com/wiki/TicketSQL
>    3. mailto:kfcrocker at lbl.gov
>    4. mailto:kfcrocker at lbl.gov
>    5. mailto:payam at rasana.net
>    6. mailto:payam at rasana.net
>    7. mailto:hiro24 at gmail.com
>    8. mailto:hiro24 at gmail.com
>    9. mailto:rt-users at lists.bestpractical.com
>   10. mailto:rt-users at lists.bestpractical.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 195 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110222/6720b62d/attachment.sig>