[Savannah-cvs] [SavaneTasks] (edit) spam stats

[Beuc]

++added:

Spam analysis on Savannah: I (Beuc) added a table called 'spam_stats' that logs 
all posted items and comments. See the patch and table structure at 
``/vservers/frontend/usr/src/savane-spam_stats.*``.

Check for an analysis after ~1 day: 
https://savannah.gnu.org/support/index.php?106304#comment9

Queries::

 -- * Approximation for anonymous spam + anonymous users who mistyped the 
captcha *
 -- User was not logged and check_value is wrong
 SELECT COUNT(*) FROM spam_stats WHERE (user_id IS NULL AND check_value != 
'421');
 -- Idem, and in addition the posted text contains an HTTP link
 SELECT COUNT(*) FROM spam_stats WHERE (user_id IS NULL AND check_value != 
'421') AND details LIKE "%http://%";;
 
 -- Validated post (captcha or login)
 SELECT count(*) FROM spam_stats WHERE user_id IS NOT NULL OR (user_id IS NULL 
AND check_value=421);
 
 -- Posts per suspicious IP
 SELECT ip, count(*) AS sum FROM spam_stats WHERE (check_value IS NOT NULL OR 
check_value != '421') AND details LIKE "%http%" GROUP BY ip ORDER BY sum;
 -- Average posts by suspicious IP
 SELECT AVG(sum) FROM (SELECT ip, count(*) AS sum FROM spam_stats WHERE 
(check_value IS NOT NULL OR check_value != '421') AND details LIKE "%http%" 
GROUP BY ip ORDER BY sum) AS average



--
forwarded from 
https://savannah.gnu.org/maintenance/address@hidden://savannah.gnu.org/maintenance