Support:Queries: Difference between revisions
Jump to navigation
Jump to search
Content deleted Content added
imported>Kymara |
imported>Hendrik Brummermann |
||
| (50 intermediate revisions by 2 users not shown) | |||
| Line 17: | Line 17: | ||
</source> |
</source> |
||
=== Merge |
=== Merge all characters into an account === |
||
| ⚫ | |||
1. Learn the names of the original account and new account: |
|||
-- get ids as we will need them later |
|||
select id as account_id from account where username = '[account]'; |
|||
<pre> |
|||
select id as character_account_id from account where username = '[character]'; |
|||
SELECT account.username, account.status |
|||
update account set status = 'merged' where username ='[character]'; |
|||
FROM characters, account |
|||
update characters set player_id = '[account_id]' where charname = '[character]'; |
|||
WHERE account.id=player_id AND charname='[charname]'; |
|||
insert into gameEvents (source, event, param1, param2) |
|||
</pre> |
|||
values ('[character]','accountmerge','[character_account_id]', '[character]-->[account]'); |
|||
2. As user marauroa execute the following script. <nowiki>[oldaccount]</nowiki> is the account name, from which the characters are taken away. |
|||
<source lang="bash"> |
|||
cd /var/www/stendhal/scripts/cmd |
|||
php merge.php [oldaccount] [newaccount] |
|||
</source> |
</source> |
||
| Line 87: | Line 93: | ||
=== Finding Bots === |
=== Finding Bots === |
||
<source lang="sql"> |
|||
SELECT left(timedate, 10) As day, source, param1, count(*) As cnt |
|||
{{TODO}} |
|||
FROM gameEvents |
|||
WHERE event='use' AND timedate>date_sub(CURDATE(), INTERVAL 30 day) |
|||
GROUP BY day, param1, source ORDER BY cnt DESC |
|||
LIMIT 50; |
|||
</source> |
|||
=== Checking if someone is a bot === |
=== Checking if someone is a bot === |
||
| Line 98: | Line 109: | ||
FROM gameEvents |
FROM gameEvents |
||
WHERE (source='[character1]' OR source='[character2]') AND event='use' GROUP BY source, event, day ORDER BY day; |
WHERE (source='[character1]' OR source='[character2]') AND event='use' GROUP BY source, event, day ORDER BY day; |
||
</source> |
|||
=== Checking if someone is a bot (alternative) === |
|||
| ⚫ | |||
SELECT source, left(timedate, 13) As hour, count(*) As cnt |
|||
FROM gameEvents |
|||
WHERE source in ('[username]') GROUP BY hour ORDER BY id DESC LIMIT 500; |
|||
</source> |
|||
=== 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> |
|||
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> |
</source> |
||