Support:Queries: Difference between revisions

=== Summarising activity per session ===

Summarises activity per login session by first identifying the id of login and id of the next logout. Then gathers the events between those ids, and groups by event type.

<source lang="SQL">

select login_id, event, param1, count(*)

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

where m.id between login_id and logout_id

and m.source = '[username]'

-- id check covers this off already but this optimises the query

and timedate > '[date]'

group by login_id, event, param1

order by login_id, min(id);

</source>

An example of suspicious activity might be:

<pre>

| 93691875 | login | | 1 |

| 93691875 | attack | playername | 2 |

| 93691875 | use | meat | 181 |

| 93691875 | atk | 77 | 1 |

| 93691875 | logout | | 1 |

| 93715333 | login | | 1 |

| 93715333 | attack | playername | 1 |

| 93715333 | use | meat | 47 |

| 93715333 | logout | | 1 |

</pre>