Per Jessen wrote:
So how do we differenciate spammers from good users?
Precisely the same question I have been wondering about.
IMHO it isn't too complex and can be answered with two questions:
a) Does the _username_ contain a @ sign?
No ("example_user") -> someone with an openSUSE account -> most likely not a spammer.
Yes ("user@example.com") -> account was created by sending a mail to progress -> continue with b)
b) Does the user profile (like https://progress.opensuse.org/users/76 ) (or the underlaying tables) show activity for this user? Note: checking for "created tickets" _or_ "activity" should be enough.
Yes, at least one ticket created -> most likely it's a valid user who created one or more tickets by mail
But, any email to admin@o.o will create a ticket ? I played with this a bit this morning - "get me email users that are not blocked with just one ticket attached excluding certain email addresses". select u.login,u.status,count(*) from users u,issues i \ where i.project_id=13 and \ /* opensuse admin */ u.status!=3 and \ /* not already blocked */ login regexp '@' and \ /* email addr */ login not regexp '@(suse\.com|suse\...|opensuse\.org)$' and \ u.id=i.author_id group by u.id having count(*)<2; That gave me 654 logins, some I recognise from current issue spam, but also many I recognise as long time openSUSE users or SUSE staff with their private addresses. Some mirror operators, and even a 'per@jessen.ch' ... This one might be better: "get me email users that are not blocked, but have no tickets attached". (no ticket attached = it was deleted). select u.login from users u left join issues i on u.id=i.author_id where i.author_id is null and u.status!=3 and login regexp '@'; 2186 such users - at a quick glance, many have a certain "spammy" look. There are some postmasters (from backscatter) etc. However, also some suse and opensuse addresses. https://files.jessen.ch/users-with-zero-tickets There are also some of our list addresses, mirrors, known projects, but it looks like they are typically older, from before 2015 for instance. https://files.jessen.ch/users-with-zero-tickets-last365 -- Per Jessen, Zürich (10.6°C) Member, openSUSE Heroes