TicketSQL: search for count of Requestors on a ticket?

I’ll ask my primary question first, then later explain the problem in case the solution I’m pursuing is not the best one.

What I was looking for was the ability to do the following in TicketSQL via the Advanced Query Builder:

Requestor.EmailAddress LIKE ‘someone’ and count(Requestors) > 1

This “someone” is often a Requestor on their own tickets, but I want to find where “someone” is a Redundant requestor along with another party, and remove that someone from only those tickets. I’d like to leave someone as Requestor on tickets where the someone is the ONLY requestor.

I don’t necessarily need to use TicketSQL for this if there’s another suggestion on how to remove this Requestor only when they are a redundant requestor.

Suggestions?

I believe that you can create a custom action and use the rt-crontool to accomplish this, you can do something like this:

rt-crontool --search RT::Search::FromSQL --search-arg "Queue='something' AND Requestor='Someone' --action MyCustomAction

Then for the custom action you can have the normal action boiler plate and some logic like:

if ( $self->ticket->Requestor->MembersObj->Count > 1 ) {
   #  if "someone" is redundant reqestor remove them
   RT::Logger->debug($ticket->Id." has redundant requestor, removing.");
}
else {
   RT::Logger->debug($ticket->Id." is okay, next");
}

Ah, that’s very helpful. That’s definitely an approach that will work. Another thought is that since this happens only on ticket merge time, maybe I could use a Scrip triggered on Merge. Is it possible to trigger a Scrip on Merge?

(This would work best because the “someone” should be a Requestor on tickets by themselves, but once they are merged into another ticket, the “someone” is no longer relevant and the Requestor on the destination-merge ticket is the right person. Catching it at the merge moment would be ideal because that means the Requestor being removed won’t have a chance of getting correspondence they shouldn’t get. I’d worry with the rt-crontool solution that correspondence could happen on the ticket before the cron job runs.)

A scrip to fire on merge is possible as well, and may be a little easier to keep track of as opposed to a cronjon running. You will need a custom condition and action for the scrip and in the condition you need to check for 3 things:

  1. That the transaction type is “AddLink”
  2. The transaction field is “MergedInto”
  3. The requestors role has the redundant user