Constructing a CB query to find SPAM users

For a couple of our sites, we end up with SPAM bot registrations. You can spot them, when you look at the users, as they are always of the form where the firstname is a subset of the lastname. For example:
Firstname: KeithGum
Lastname: KeithGumEA
and Confirmed is always false (i.e. the bot never responded to the email confirmation)

If I was doing a SQL query, to find all these users, it would be: SELECT * FROM jml_comprofiler a WHERE a.lastname LIKE CONCAT(a.firstname,'%') and a.confirmed = 0

My question is: How can I select these from the Community Builder administrative back end so I can cleanly delete them? I don't seem to be able to use field substitutions in the advanced search. Is there some way in the Joomla backend to construct this query?

Using the latest Joomla and CB versions.

First step I recommend is ensuring you've CB AntiSpam installed and its captcha configured. This will catch most if not all automated bots. You're then left with human bots who register then had off the account to an automated process. Typically the automated bots will try to spam forums, which you can use the below usage in CB Auto Actions to try and protect against.

www.joomlapolis.com/documentation/291-cb-auto-actions/tutorials/18810-blocking-kunena-forum-spammers

As for your issue no substitutions are not supported in the searching. Joomla search is even less powerful than ours so it won't work there either. You could maybe create a frontend userlist though and have an Advanced Filter which directly adds to the database query to try and match your spam criteria to at least help find them better.

If there's a common element to their email address though you could use the auto blocking feature in CB AntiSpam to try and block similar email addresses from even being allowed to registered, but a lot of the time they'll use a compromised email account to register.

Thank you. I just wanted to know whether I could construct the query I was looking in CB. Not being able to, I'll approach removing the accounts in a different way..