Support:Queries: Difference between revisions
Content deleted Content added
imported>Kymara activity |
imported>Kymara |
||
Line 144:
| 93715333 | logout | | 1 |
</pre>
A variation with one session per line, identifying only the sessions which seem suspicious:
<source lang="SQL">
SELECT login_id, min(timedate) as login_time, group_concat(distinct event) events, count(*) num_events, timediff(max(timedate),min(timedate)) time_online
FROM gameEvents m,
(SELECT source, id AS login_id, (SELECT id FROM gameEvents g2 WHERE g2.source = '[username]' AND g2.id > g1.id AND event = 'logout' ORDER BY id LIMIT 1) logout_id
FROM gameEvents g1
-- for query optimisation, specify username by hand in each subquery
WHERE source = '[username]'
AND event = 'login'
AND timedate > '[date]') temp
-- exclude the login and logout events from count
WHERE m.id BETWEEN login_id+1 AND logout_id-1
AND m.source = '[username]'
-- id check covers this off already but this optimises the query
AND timedate > '[date]'
GROUP BY login_id
-- 2 or less events (i.e. only attack and use say)
having count(distinct event) < 3
-- but more than 5 events in total
and count(*) > 5;
</source>
| |||