[rt-users] search by CommentedOnBy?

Chris Hall hiro24 at gmail.com
Tue Feb 22 14:19:07 EST 2011


The problem is, I'm not strong enough in sql and perl to whip that out.  I
understand the HomepageComponents, I've added a couple, and I can edit
usually to suit my needs, but I can't just whip out a portlet.  Not sure how
to proceed.

On Tue, Feb 22, 2011 at 1:17 PM, Kevin Falcone <falcone at bestpractical.com>wrote:

> 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 --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20110222/680cd908/attachment.htm>


More information about the rt-users mailing list