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 <[email protected]>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] > [email protected]> 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] > [email protected] > > > <mailto:[4][email protected]>> 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] > [email protected] > > > <mailto:[6][email protected]>> 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][email protected] <mailto:[8][email protected] > >> > > > To: [9][email protected] > > > <mailto:[10][email protected]> > > > 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:[email protected] > > 2. http://requesttracker.wikia.com/wiki/TicketSQL > > 3. mailto:[email protected] > > 4. mailto:[email protected] > > 5. mailto:[email protected] > > 6. mailto:[email protected] > > 7. mailto:[email protected] > > 8. mailto:[email protected] > > 9. mailto:[email protected] > > 10. mailto:[email protected] >
