How to get comments given to a ticket from database

I need comments given to a ticket in RT at the time of resolving, from th RT database. I gone through the tables in the RT database , I couldn’t get it. Can somebody help me to do this?

You’ll probably need to search through the Transactions table, first looking for records where Type is Status and NewValue is resolved. Take the ObjectId and Created entries from these matches and then do a second search of the Transactions table for matching ObjectIds, similar Created timestamps (say within a second or two) and Type of Comment.

Thank you for the response. I tried as you mentioned. I got the columns where Type = comment. But the matter of the comment is not available in any of the columns. How can i get it?

You need to take query the Attachments table for the transaction ID

I queried the attachment table using the transaction id . But didn’t get the column of comments.

i created a ticket in the RT and added some comments to it. The comments can be seen in the UI side. Now i need to fetch this comment from the RT database . For that first I query the transaction table by giving the Type = “comment” and object id. from the results I took the transaction id of the ticket and query the attachment table using this transaction id. But still I did not get the column with comments. Can someone please help me to find the comments column?

The comment contents will be in the Content field of the Attachment table.

This is the screenshot of my attachment table. The Content field is showing some values named “BLOB”. Not the exact comment content.

The content is a BLOB data type because an attachment can be pretty much anything and quite large.

Try using the normal mysql command line client rather than a GUI and you should see the content of your query OK.

One thing I have done to understand the RT DB Schema is a mysqldump to a sql text file, and then edit with your favorite editor. You can look for known strings associated with a ticket, comment and attachments, to map out the relationship between the transactions, tickets and attchments tables. That’s helped me immensley in writing Perl code to work through the object relationships. Hope that helps :slight_smile:

1 Like