A script to find certain information in the rt3 database

RT Users
1

I am wanting to write a script to directly query the mysql backend of my
rt3.4.1 installation. I need to pull out the meat of the tickets that
meet a certain requirement. For instance, I want to get ticket IDs for
any tickets with THISPHRASE in the title or anywhere in the history. I
looked in the wiki and the FAQ, and was surprised not to have seen this
mentioned. Is there another place I should have looked? Thanks in advance,

Vicki

2

This should n’t be difficult , write a perl script …the sql you’ll need is:
select Tickets.id from Tickets left Join Transactions on Tickets.Id =
Transactions.TicketId left join attachments on Transaction.id =
Attachments.TransactionId where Tickets.Subject like ‘%THISPHRASE%’ OR
Attachments.Content like ‘%THISPHRASE%’;

Roy----- Original Message -----
From: “Vicki Stanfield” vicki@progeny.com
To: rt-users@lists.bestpractical.com
Sent: Thursday, July 21, 2005 8:28 PM
Subject: [rt-users] A script to find certain information in the rt3 database

I am wanting to write a script to directly query the mysql backend of my
rt3.4.1 installation. I need to pull out the meat of the tickets that
meet a certain requirement. For instance, I want to get ticket IDs for
any tickets with THISPHRASE in the title or anywhere in the history. I
looked in the wiki and the FAQ, and was surprised not to have seen this
mentioned. Is there another place I should have looked? Thanks in advance,

Vicki

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

3

Raed El - Hames wrote:

This should n’t be difficult , write a perl script …the sql you’ll need is:
select Tickets.id from Tickets left Join Transactions on Tickets.Id =
Transactions.TicketId left join attachments on Transaction.id =
Attachments.TransactionId where Tickets.Subject like ‘%THISPHRASE%’ OR
Attachments.Content like ‘%THISPHRASE%’;

Roy
----- Original Message -----
From: “Vicki Stanfield” vicki@progeny.com
To: rt-users@lists.bestpractical.com
Sent: Thursday, July 21, 2005 8:28 PM
Subject: [rt-users] A script to find certain information in the rt3 database

I am wanting to write a script to directly query the mysql backend of my
rt3.4.1 installation. I need to pull out the meat of the tickets that
meet a certain requirement. For instance, I want to get ticket IDs for
any tickets with THISPHRASE in the title or anywhere in the history. I
looked in the wiki and the FAQ, and was surprised not to have seen this
mentioned. Is there another place I should have looked? Thanks in advance,

Vicki

The rt-users Archives

Be sure to check out the RT Wiki at http://wiki.bestpractical.com

I had to make a couple of changes, but basically I think that is it. It
hasn’t returned anything yet though, so I need to keep working with it.
I never was good with JOINS. :wink:

Vicki

4

Raed El - Hames wrote:

This should n’t be difficult , write a perl script …the sql you’ll need is:
select Tickets.id from Tickets left Join Transactions on Tickets.Id =
Transactions.TicketId left join attachments on Transaction.id =
Attachments.TransactionId where Tickets.Subject like ‘%THISPHRASE%’ OR
Attachments.Content like ‘%THISPHRASE%’;

Roy

Okay, I have a question now. I have modified the query to be this:

select Tickets.id from Tickets left Join Transactions on Tickets.Id =
Transactions.ObjectId left join Attachments on Transactions.id =
Attachments.TransactionId where Tickets.Subject like ‘%Platform Group%’
OR Attachments.Content like ‘%Platform Group%’;

I know there are tickets with Platform Group in the subject line, but I
have waited a long time (maybe 2 hours) and nothing has returned yet
(although the process is still running). Does this look right to you?

Vicki