[rt-users] SQL Reports - Keywords

[Gororo, Hillary]

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 at 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 at mhpcc.edu
<mailto:mhershey at 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." 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20021010/ec7f80ec/attachment.htm>