How to get comments given to a ticket from database

1

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?

2

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.

4

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?

5

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

6

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

7

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?

8

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

9

Screenshot (13)
Screenshot (13)1920×1080 422 KB

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

10

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.

11

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
12

Thanks for the information!

13

Here’s a sample query that you can use to fetch comments for a specific ticket:

SELECT Content
FROM Attachments
WHERE TransactionId IN (
SELECT id
FROM Transactions
WHERE ObjectId = ‘your_ticket_id’
AND ObjectType = ‘RT::Ticket’
);


Replace `'your_ticket_id'` with the actual ID of the ticket you're interested in.

This query retrieves the content of the comments (`Content` field) from the `Attachments` table where the `TransactionId` matches the `id` from the `Transactions` table for the specified ticket ID.
14

Here’s a general SQL query you can use to retrieve comments for a specific ticket:

SELECT
Transactions.id AS TransactionID,
Users.RealName AS CommentedBy,
Transactions.Created AS CommentedAt,
Transactions.Content AS CommentContent
FROM
Transactions
JOIN Users ON Transactions.Creator = Users.id
WHERE
Transactions.Type = ‘Comment’
AND Transactions.ObjectId = ‘your_ticket_id’
ORDER BY
Transactions.Created;

Replace 'your_ticket_id' with the ID of the ticket you’re interested in. This query will return the ID of each comment transaction, the name of the user who commented, the timestamp when the comment was made, and the content of the comment.