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:<div>
<br></div><div><div>use DBIx::SearchBuilder::Handle;</div><div><br></div><div>my $handle = DBIx::SearchBuilder::Handle->new();</div><div><br></div><div>$handle->Connect(</div><div> Driver => 'mysql',</div>
<div> Database => 'rt3',</div><div> Host => 'localhost',</div><div> User => 'rt_user',</div><div> Password => 'abcd1234'</div>
<div>);</div><div><br></div><div>my ($title, $ticketcount);</div><div>$session{'i'}++;</div><div>$session{'tickets'} = RT::Tickets->new($session{'CurrentUser'}) ;</div><div>$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';");</div>
<div><br></div><div>in place of:</div><div><br></div><div><br></div><div><div>my ($title, $ticketcount);</div><div>$session{'i'}++;</div><div>$session{'tickets'} = RT::Tickets->new($session{'CurrentUser'}) ;</div>
<div>$session{'tickets'}->FromSQL($Query) if ($Query);</div></div><div><br></div><div><br></div><div><br></div><div>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.</div>
<br><div class="gmail_quote">On Mon, Feb 21, 2011 at 11:59 AM, Thomas Sibley <span dir="ltr"><<a href="mailto:trs@bestpractical.com">trs@bestpractical.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class="im">On 21 Feb 2011 11:40, Chris Hall wrote:<br>
> ..so I've been racking my brain on this one for awhile. Any guidance is<br>
> appreciated. The aforementioned query works great, I just need to<br>
> integrate it into RT. However, the RT queries seem to (I'm sure for<br>
> good reason) strip off alot of that query to the point that it doesn't<br>
> work. I've tried manually setting it as the query in a custom<br>
> Results.html, still to no avail. I'm not quite sure how to proceed.<br>
> Does anyone have any suggestions?<br>
<br>
</div>RT's user-exposed queries are written in TicketSQL, not SQL. TicketSQL<br>
is a SQL-like DSL to describe tickets you want to find. TicketSQL<br>
doesn't search for transactions, only tickets, so you can't express your<br>
SQL query in TicketSQL. <a href="http://requesttracker.wikia.com/wiki/TicketSQL" target="_blank">http://requesttracker.wikia.com/wiki/TicketSQL</a><br>
<br>
To show the results of your SQL below in RT, you'd need to modify the<br>
query to use the DBIx::SearchBuilder API and then display the results.<br>
<br>
Thomas<br>
<div class="im"><br>
> On Fri, Feb 11, 2011 at 11:48 AM, Kenneth Crocker <<a href="mailto:kfcrocker@lbl.gov">kfcrocker@lbl.gov</a><br>
</div><div class="im">> <mailto:<a href="mailto:kfcrocker@lbl.gov">kfcrocker@lbl.gov</a>>> wrote:<br>
><br>
> Chris,<br>
><br>
> We do something similar to Payam. We have an Oracle DataBase so we<br>
> created a "view" of the RT data to allow us to use COGNOS (or other<br>
> reporting software) to create reports from RT data. Works great.<br>
><br>
> Kenn<br>
> LBNL<br>
><br>
> On Fri, Feb 11, 2011 at 1:32 AM, Payam Poursaied <<a href="mailto:payam@rasana.net">payam@rasana.net</a><br>
</div><div class="im">> <mailto:<a href="mailto:payam@rasana.net">payam@rasana.net</a>>> wrote:<br>
><br>
> You could start by database<br>
><br>
> select<br>
> Transactions.id,ObjectType,ObjectId,Type,Transactions.Created,Name,EmailAddr<br>
> ess from Transactions<br>
> inner join Users on Users.id=Transactions.Creator<br>
> where ObjectType='RT::Ticket' and Type='Comment' and<br>
> Transactins.Created>='YYYY-MM-DD HH:MM:SS' and<br>
> Transactions.Created<='YYYY-MM-DD HH:MM:SS';<br>
><br>
> But be aware of 2 things:<br>
> First: the time is not your localtime, it is GMT0<br>
> second: if someone rather than your support team is allowed to<br>
> comment on<br>
> tickets, you would have them in the report<br>
><br>
><br>
><br>
><br>
> Date: Thu, 10 Feb 2011 16:25:10 -0500<br>
</div>> From: Chris Hall <<a href="mailto:hiro24@gmail.com">hiro24@gmail.com</a> <mailto:<a href="mailto:hiro24@gmail.com">hiro24@gmail.com</a>>><br>
<div class="im">> To: <a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a><br>
</div>> <mailto:<a href="mailto:rt-users@lists.bestpractical.com">rt-users@lists.bestpractical.com</a>><br>
<div><div></div><div class="h5">> Subject: Re: [rt-users] search by CommentedOnBy?<br>
><br>
> I hate to necro an old, dead thread but.. this seems to have<br>
> come up again..<br>
> from two separate people. I did figure out if I edited the<br>
> advanced section<br>
> and added __CommentedOnBy__ I could at least see who the last<br>
> person was to<br>
> comment on a ticket, but here's my current problem:<br>
><br>
> Helpdesk manager person has some software that spits out how<br>
> many calls a<br>
> given worker does, and wants to compare this with the RT<br>
> records. For<br>
> example, Worker A takes 54 calls in a night. Helpdesk manager<br>
> person wants<br>
> to check the RT records to see if he has made 54 comments for<br>
> that given<br>
> night. Kind of a making sure ppl are doing what they're<br>
> supposed to be<br>
> doing.<br>
><br>
> So sometime before the end of the month, I need to figure out a<br>
> way to form<br>
> a query to show all comments in a given month. Maybe not "show"<br>
> them, but<br>
> just a list to say... Worker A commented on the following<br>
> tickets this<br>
> month.<br>
><br>
> Does anybody have any suggestions where I could start with<br>
> something like<br>
> this?<br>
><br>
><br>
><br>
<br>
</div></div></blockquote></div><br></div>