SQL Reports - Keywords

RT Users
1

I’m just starting to write SQL reports for an RT system that will become
LIVE soon.

I’m stumped. I know a query can be done on Keywords (it’s available via
the SEARCH function),but I’m failing to see the connection between the
tickets and keywords to create a monthly report via SQL by reviewing the
tables.

Please point me in the right direction of help or documenation.

Thanks,
Michele

mhershey.vcf (237 Bytes)

2

Hi Michele,

I am trying to write a query that will count the total number of tickets, in
a given queue that are OPEN/STALLED/RESOLVED and have a specific keyword.

I have the following query so far and the numbers are off. The total count
I get for the query below does not tally up with the number I have on the
WebUI for that queue (e.g results for OPEN count below for this queue-does
not match what is on the WebUI as OPEN for that queue) Any ideas:

SELECT COUNT(T.NewValue) FROM Transactions T, Queues Q WHERE Q.id=‘18’ AND
T.NewValue=‘new’;

Thanks for all the help

Hillary-----Original Message-----
From: Michele Hershey [mailto:mhershey@mhpcc.edu]
Sent: Wednesday, October 09, 2002 8:42 PM
To: Gororo, Hillary
Subject: Re: [rt-users] SQL Reports - Keywords

Check this out…it may do it!

mysql> select * from ObjectKeywords where objectid =288;
| id | Keyword | KeywordSelect | ObjectType | ObjectId |
| 568 | 15 | 4 | Ticket | 288 |
| 569 | 21 | 5 | Ticket | 288 |
| 570 | 47 | 6 | Ticket | 288 |
3 rows in set (0.00 sec)

mysql> select * from Keywords where id = 15;
| id | Name | Description | Parent | Disabled |
| 15 | 0 - Low | NULL | 14 | 0 |
1 row in set (0.00 sec)

mysql>

“Gororo, Hillary” wrote:

Michele,

Any luck on this. I am trying to run a report that will count the total
number of tickets in a queue by Keyword name. I can’t seem to see the
connection btw tickets and keywords in mysql. Do you have any ideas on
this.

Thanks
Hillary

-----Original Message-----
From: Michele Hershey [ mailto:mhershey@mhpcc.edu
mailto:mhershey@mhpcc.edu ]
Sent: Thursday, September 05, 2002 9:56 PM
To: RT
Subject: [rt-users] SQL Reports - Keywords

I’m just starting to write SQL reports for an RT system that will become
LIVE soon.

I’m stumped. I know a query can be done on Keywords (it’s available via
the SEARCH function),but I’m failing to see the connection between the
tickets and keywords to create a monthly report via SQL by reviewing the
tables.

Please point me in the right direction of help or documenation.

Thanks,
Michele

“The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from all
computers.”

“The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of, or
taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited. If you received
this in error, please contact the sender and delete the material from all
computers.”