Using HiveSQL and Python to find people

In my quest to find more people to add to the Brit List I have been making use of @hivesql as that allows searching of the account metadata. I was running some queries using DBeaver, but realised I could make life easier by scripting something smarter.

This is the core of my script. It loops through a series of queries that check for different locations (e.g. England, Great Britain) and then checks if those people are already in my list. I maintain an 'ignore' file for any I have already discounted so the results should only be accounts I have not looked at before. Note that both wirex accounts look to be fake. The real one for that crypto debit card company is @communitymanager, but it is inactive. The 'phuket' check is to avoid clashes with 'uk', but I could move that to the query list.

I also obtained a list of accounts known to have had their keys compromised and are voting for some spammers. Those get marked in the results. I still need to manually check each account to see if it will count as active and is actually a Brit, but this script saves me some time.

I am using the pyodbc Python library to access the SQL Server database. This required me to install the Microsoft library, but that was just a matter of running a few commands.

You can find my full script on Github. I was careful to not include my HiveSQL password, so that is loaded from a file that is not on there. I use Jupyter Notebook as it makes it easy to try variations of the script. Each query runs in a second or less.

Unfortunately people are not at all consistent in how they specify their location in their Hive profile, so I do get false positives. 'UK' can also mean Ukraine. Some do not specify the country and so I have tried various cities and counties.

I have noticed that for some accounts the metadata in the database does not match what is displayed. It may be that people have updated their location and the database has not picked that up.

It would be easy to use my scripts for other countries or regions, so let me know if you need help with that. If you know of Brits I may have missed then let me know.

Hive five!