[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