Here’s a very specific SQL query for a very simple case of a one-journal OJS 3.0.0.2 installation:
SELECT username, first_name, last_name, email
FROM users
-- exclude authors and editors associated with a submission
WHERE user_id NOT IN (SELECT user_id FROM stage_assignments)
-- exclude all "recently" registered users
AND date_registered < CAST("2025-09-13" AS datetime)
-- exclude legitimate reviewers with valid assignments
AND user_id NOT IN (SELECT reviewer_id AS 'user_id' FROM review_assignments)
-- exclude VIPs (admins, managers, editors, ... reviewers) by roles
AND user_id NOT IN (SELECT user_id FROM user_user_groups WHERE user_group_id IN (1,2,3,5,6,16));
-- for this particular OJS 3.0.0.2
--
-- SELECT user_group_id, setting_value FROM user_group_settings WHERE setting_name='name';
-- +---------------+---------------------------------+
-- | user_group_id | setting_value |
-- +---------------+---------------------------------+
-- | 1 | Site Admin |
-- | 2 | Journal manager |
-- | 3 | Journal editor |
-- | 4 | Production editor |
-- | 5 | Section editor |
-- | 6 | Executive editor |
-- | 7 | Copyeditor |
-- | 8 | Designer |
-- | 9 | Funding coordinator |
-- | 10 | Indexer |
-- | 11 | Layout Editor |
-- | 12 | Marketing and sales coordinator |
-- | 13 | Proofreader |
-- | 14 | Author |
-- | 15 | Translator |
-- | 16 | Reviewer |
-- | 17 | Reader |
-- | 18 | Subscription Manager |
-- +---------------+---------------------------------+
--
-- SELECT DISTINCT user_group_id FROM user_user_groups;
-- +---------------+
-- | user_group_id |
-- +---------------+
-- | 1 |
-- | 2 |
-- | 3 |
-- | 5 |
-- | 6 |
-- | 14 |
-- | 15 |
-- | 16 |
-- | 17 |
-- +---------------+
Basically, whoever has registered some time ago but is neither a VIP nor associated with any submission is a bot. I’ve tried filtering on the “registration date equal to last login date” but it has proven surprisingly ineffective - as if a lot of bots do login from time to time (or at least one time after registering). I hope someone will find this useful. Please note that the roles and role numbers are likely different for different OJS versions.
For merging the bot users I run a modified script based on @Alfredo_Cosco’s one from this post