Detect data spikes (loops) in MySQL database and ignore these

Question

i must create some statistics out of a WWW access log database, but i found out that there are some redirect loops after a login if the user has disabled cookies. So there are 10-1000 mostly identical rows like the following:

...
/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00
/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:00
/login?redirect=/app - 200 - 123.123.123.123 - 2014-01-01 10:00:00
/app                 - 403 - 123.123.123.123 - 2014-01-01 10:00:01
...

Is there a way to detect unusal count grouped by the ip/url in an interval? I think i need to select all counts for each minute an detect a spike and ignore that... Does anyone had the same difficulties?

Answer 1

Acoording to your pattern, you could do something like this (change your column names and url pattern accordingly):

mysql> SET @max_redirects := 10;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) as `# redirects`, ip, min(url) as url, 
              time_event - INTERVAL SECOND(time_event) SECOND AS minute 
       FROM log WHERE url like '/login?redirect=%' 
       GROUP BY ip, minute 
       HAVING `# redirects` > @max_redirects;
+-------------+-----------------+----------------------+---------------------+
| # redirects | ip              | url                  | minute              |
+-------------+-----------------+----------------------+---------------------+
|          53 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:03:00 |
|          21 | 1.1.1.1         | /login?redirect=/    | 2014-08-05 08:05:00 |
|         128 | 123.123.123.123 | /login?redirect=/app | 2014-01-01 10:00:00 |
+-------------+-----------------+----------------------+---------------------+
3 rows in set (0.01 sec)

Expect this to be slow on a tall table, so add an index on your timestamp column and filter by date to avoid huge sorts.