Searching for merged tickets

RT Users
1

Hi, is there a way of searching for tickets that contain merged tickets? for example:

#1 - standalone ticket, no merge actions
#2 - merged into #3
#3 - contains #2

I’m trying to find a way of searching and only returning #3 (but not #1 or #2)

I can see that there’s an attribute Tickets.MergedInto but if I add this to a custom query I get:
Unknown field: MergeInto
Similarly Ticket.MergeInto and Tickets.MergeInto also fail

I can write a SQL query to get the results but was really hoping to use the RT Search for this.

Thankyou!

2

Is there a merge type transaction? Maybe you can search via the transaction query builder

3

My guess is that yes there is, because that’s how most changes in the system are handled, but it’s not available via the builder. You don’t need to look at transactions in sql to identify (there is a field Tickets.IsMerged). But… Even if it is a transaction in guessing not much use if the builder doesn’t support it?

4

And you are saying you want to search by the old ID ie #2 in your example?

5

No, I am looking for :

Vs tickets that contain no merged tickets or all tickets.

I don’t need original id. Effective id is fine.

6

Answering my own question… you can get this with the following sql, which is easy to turn into an emailed report / etc:

select Tickets.id, Tickets.EffectiveId, Queues.Name,Tickets.Subject,Tickets.Status,Tickets.LastUpdated 
from Tickets Left join Queues on Tickets.Queue = Queues.id  
where Tickets.Status = 'Resolved' and 
Tickets.LastUpdated > date_add(current_date(), Interval -2 month) 
and Tickets.id >< Tickets.EffectiveId

This example returns tickets where tickets.EffectiveID doesn’t match Tickets.id for the last 2 months. Has the advantage of only hitting the Tickets table and Queues so is very fast to run.

7

Unfortunately, the built-in search functionality of RT does not provide a direct way to search for merged tickets or their parents. To achieve this, you would need to create a custom SQL query that joins the Tickets and Links tables, filtering for links of type Mergedinto. By selecting distinct ticket records, you can obtain the merged tickets.

8 
select Tickets.EffectiveId, Queues.Name,Tickets.Subject,Tickets.Status,Tickets.LastUpdated
from Tickets Left join Queues on Tickets.Queue = Queues.id
where Tickets.Status = 'Resolved'
and  Tickets.LastUpdated > date_add(current_date(), Interval -32 day)
and Tickets.id <> Tickets.EffectiveId

A real shame that this isn’t available but the SQL is simple. This one shows tickets updated recently that were merged. We generate a CSV from this and email it to the users who need the info.