Search by CommentedOnBy?

RT Users
1

Is there a way to do a search of tickets to see which tickets a given person
has commented on?

2

Under ‘Reports’, you can choose ‘Advance Ticket Search’ which should call on
freeside/rt/Search/Build.html depending on how you’ve got your RT set up.

After this (I find) it’s easier to click over ‘Advanced’, beside Edit Search
and Calendar; then just enter your search terms. If I wanted to search for
all tickets CommentedOnBy myself, I’d write:

CommentedOnBy = ‘kris’ AND LastUpdated = ‘today’ where ‘kris’ is the rtuser
id and LastUpdated is the date you wish to search. A bit hazy on the date
format though, I believe it’s unixtimestamp based.

Additionally, CommentedOnBy = ‘kris’, would pull up ALL comments by user
‘kris’ from the history of your RT installation until the second you clicked
‘Show Report’ J

Also, a wider search would be 'LastUpdatedBy in which would would replace
CommentedOnBy with this and it would pull up status changes, comments,
replies, etc. for a specific user, or for all users chronologically.

Thanks

Kris Germann
Supervisor, Sales & Technical Support
Fibernetics Corporation
freephoneline.ca
605 Boxwood Drive
Cambridge ON, N3E1A5From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Chris Hall
Sent: Monday, January 03, 2011 4:09 PM
To: rt-users
Subject: [rt-users] search by CommentedOnBy?

Is there a way to do a search of tickets to see which tickets a given person
has commented on?

3

That’s close I think, but not quite what I’m looking for. I couldn’t get
your line to work… although I could see how it’s not exactly what they’re
looking for. They want to search for everything a particular user has
commented on on the current day. That would potentially show things a user
had commented on in a previous day, and another person had commented on
today.On Mon, Jan 3, 2011 at 5:01 PM, Kris Germann kris_germann@295.ca wrote:

Under ‘Reports’, you can choose ‘Advance Ticket Search’ which should call
on freeside/rt/Search/Build.html depending on how you’ve got your RT set up…

After this (I find) it’s easier to click over ‘Advanced’, beside Edit
Search and Calendar; then just enter your search terms. If I wanted to
search for all tickets CommentedOnBy myself, I’d write:

CommentedOnBy = ‘kris’ AND LastUpdated = ‘today’ where ‘kris’ is the rtuser
id and LastUpdated is the date you wish to search… A bit hazy on the date
format though, I believe it’s unixtimestamp based…

Additionally, CommentedOnBy = ‘kris’, would pull up ALL comments by user
‘kris’ from the history of your RT installation until the second you clicked
‘Show Report’ J

Also, a wider search would be ‘LastUpdatedBy in which would would replace
CommentedOnBy with this and it would pull up status changes, comments,
replies, etc… for a specific user, or for all users chronologically…

Thanks

Kris Germann
Supervisor, Sales & Technical Support
Fibernetics Corporation
freephoneline.ca
605 Boxwood Drive
Cambridge ON, N3E1A5

From: rt-users-bounces@lists.bestpractical.com [mailto:
rt-users-bounces@lists.bestpractical.com] *On Behalf Of *Chris Hall
Sent: Monday, January 03, 2011 4:09 PM
To: rt-users
Subject: [rt-users] search by CommentedOnBy?

Is there a way to do a search of tickets to see which tickets a given
person has commented on?

4

That’s close I think, but not quite what I’m looking for. I couldn’t get your line to work…
although I could see how it’s not exactly what they’re looking for. They want to search for
everything a particular user has commented on on the current day. That would potentially show
things a user had commented on in a previous day, and another person had commented on today.

CommentedOnBy is not a valid RT 3.8 search term
The search you’re trying to build goes against Transactions and would
require code.

-kevin> On Mon, Jan 3, 2011 at 5:01 PM, Kris Germann <[1]kris_germann@295.ca> wrote:

 Under *Reports*, you can choose *Advance Ticket Search* which should call on
 freeside/rt/Search/Build.html depending on how you*ve got your RT set up

 After this (I find) it*s easier to click over *Advanced*, beside Edit Search and Calendar;
 then just enter your search terms. If I wanted to search for all tickets CommentedOnBy
 myself, I*d write:

 CommentedOnBy = *kris* AND LastUpdated = *today* where *kris* is the rtuser id and
 LastUpdated is the date you wish to search A bit hazy on the date format though, I believe
 it*s unixtimestamp based

 Additionally, CommentedOnBy = *kris*, would pull up ALL comments by user *kris* from the
 history of your RT installation until the second you clicked *Show Report* J

 Also, a wider search would be *LastUpdatedBy in which would would replace CommentedOnBy with
 this and it would pull up status changes, comments, replies, etc for a specific user, or for
 all users chronologically

 Thanks

 Kris Germann
 Supervisor, Sales & Technical Support
 Fibernetics Corporation
 [2]freephoneline.ca
 605 Boxwood Drive
 Cambridge ON, N3E1A5
5

That’s close I think, but not quite what I’m looking for. I couldn’t
get your line to work…
although I could see how it’s not exactly what they’re looking for.
They want to search for
everything a particular user has commented on on the current day.
That would potentially show
things a user had commented on in a previous day, and another person
had commented on today.

CommentedOnBy is not a valid RT 3.8 search term
The search you’re trying to build goes against Transactions and would
require code.

-kevin

Kevin,

Which installation is this specific to? I am running RT Ver 3.8.8 (On
Freeside 2.1.2cvs) and my terms of CommentedOnBy = ‘user1’ LastUpdatedOn =
‘today’ appear to work fine… Or is it something more specific.

Thanks

Kris

6

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Kevin Falcone
Sent: Tuesday, January 04, 2011 8:24 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] search by CommentedOnBy?

That’s close I think, but not quite what I’m looking for. I couldn’t
get your line to work…
although I could see how it’s not exactly what they’re looking for.
They want to search for
everything a particular user has commented on on the current day.
That would potentially show
things a user had commented on in a previous day, and another person
had commented on today.

CommentedOnBy is not a valid RT 3.8 search term
The search you’re trying to build goes against Transactions and would
require code.
Which installation is this specific to? I am running RT Ver 3.8.8 (On
Freeside 2.1.2cvs) and my terms of CommentedOnBy = ‘user1’ LastUpdatedOn =
‘today’ appear to work fine… Or is it something more specific.

Sounds like you have custom code

$ rt/3.8 (3.8-trunk)$ grep -r CommentedOnBy lib/

-kevin

7

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?On Tue, Jan 4, 2011 at 9:56 AM, Kevin Falcone falcone@bestpractical.comwrote:

On Tue, Jan 04, 2011 at 08:32:21AM -0500, Kris Germann wrote:

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Kevin
Falcone
Sent: Tuesday, January 04, 2011 8:24 AM
To: rt-users@lists.bestpractical.com
Subject: Re: [rt-users] search by CommentedOnBy?

On Mon, Jan 03, 2011 at 06:16:42PM -0500, Chris Hall wrote:
That’s close I think, but not quite what I’m looking for. I
couldn’t
get your line to work…
although I could see how it’s not exactly what they’re looking
for.
They want to search for
everything a particular user has commented on on the current day.
That would potentially show
things a user had commented on in a previous day, and another
person
had commented on today.

CommentedOnBy is not a valid RT 3.8 search term
The search you’re trying to build goes against Transactions and would
require code.
Which installation is this specific to? I am running RT Ver 3.8.8 (On
Freeside 2.1.2cvs) and my terms of CommentedOnBy = ‘user1’ LastUpdatedOn
=
‘today’ appear to work fine… Or is it something more specific.

Sounds like you have custom code

$ rt/3.8 (3.8-trunk)$ grep -r CommentedOnBy lib/

-kevin

8

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 reportDate: Thu, 10 Feb 2011 16:25:10 -0500
From: Chris Hall hiro24@gmail.com
To: rt-users@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?

smime.p7s (5.06 KB)

9

Thanks! That looks like it’s giving me what I’m looking for… now I just
need to find a way to integrate it into RT.On Fri, Feb 11, 2011 at 4:32 AM, Payam Poursaied payam@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 hiro24@gmail.com
To: rt-users@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?

10

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
LBNLOn Fri, Feb 11, 2011 at 1:32 AM, Payam Poursaied payam@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 hiro24@gmail.com
To: rt-users@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?

11

…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?On Fri, Feb 11, 2011 at 11:48 AM, Kenneth Crocker kfcrocker@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 payam@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 hiro24@gmail.com
To: rt-users@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?

12

…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. 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 <kfcrocker@lbl.gov mailto:kfcrocker@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 <payam@rasana.net <mailto:payam@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 <hiro24@gmail.com <mailto:hiro24@gmail.com>>
    To: rt-users@lists.bestpractical.com
    <mailto:rt-users@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?
13

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.On Mon, Feb 21, 2011 at 11:59 AM, Thomas Sibley trs@bestpractical.comwrote:

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. 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 <kfcrocker@lbl.gov mailto:kfcrocker@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 <payam@rasana.net <mailto:payam@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 <hiro24@gmail.com <mailto:hiro24@gmail.com>>
    To: rt-users@lists.bestpractical.com
    <mailto:rt-users@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?
14

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@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@lbl.gov <mailto:[4]kfcrocker@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@rasana.net <mailto:[6]payam@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@gmail.com <mailto:[8]hiro24@gmail.com>>
 > To: [9]rt-users@lists.bestpractical.com
 > <mailto:[10]rt-users@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@bestpractical.com
  2. http://requesttracker.wikia.com/wiki/TicketSQL
  3. mailto:kfcrocker@lbl.gov
  4. mailto:kfcrocker@lbl.gov
  5. mailto:payam@rasana.net
  6. mailto:payam@rasana.net
  7. mailto:hiro24@gmail.com
  8. mailto:hiro24@gmail.com
  9. mailto:rt-users@lists.bestpractical.com
  10. mailto:rt-users@lists.bestpractical.com
15

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@bestpractical.comwrote:

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@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@lbl.gov <mailto:[4]kfcrocker@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@rasana.net <mailto:[6]payam@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@gmail.com <mailto:[8]hiro24@gmail.com

To: [9]rt-users@lists.bestpractical.com
mailto:[10]rt-users@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@bestpractical.com
  2. http://requesttracker.wikia.com/wiki/TicketSQL
  3. mailto:kfcrocker@lbl.gov
  4. mailto:kfcrocker@lbl.gov
  5. mailto:payam@rasana.net
  6. mailto:payam@rasana.net
  7. mailto:hiro24@gmail.com
  8. mailto:hiro24@gmail.com
  9. mailto:rt-users@lists.bestpractical.com
  10. mailto:rt-users@lists.bestpractical.com