Support:Queries: Difference between revisions

</source>

=== Merge all characters into an account ===

1. Learn the names of the original account and new account:

<pre>

SELECT account.username, account.status

FROM characters, account

WHERE account.id=player_id AND charname='[charname]';

</pre>

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>

=== Did a password get changed? ===

<source lang="SQL">

SELECT account.username, address, service, passwordChange.timedate, oldpassword

FROM account, passwordChange

WHERE account.id = passwordChange.player_id AND username ='[account]';

</source>

=== Banning an IP ===

<source lang="SQL">

-- this bans a single IP address - you can vary the mask to ban a range

insert into banlist(address,mask,reason)

values ("[ip-address]","255.255.255.255","[reason]");

</source>

=== Summarise activity from one IP address ===

<source lang="SQL">

-- may wish to add 'service' into the FROM and GROUP BY

select username, result, count(*)

from loginEvent, account

where account.id=player_id and address='[ip-address]'

group by username, result

order by username, result;

=== Item history of all items touched by suspicious characters ===

SELECT *

FROM itemlog,

(SELECT DISTINCT itemid

FROM itemlog

WHERE source IN ('[charname1]', '[charname2]')

) As x

=== Item history of all items touched by suspicious characters and someone else ===

<source lang="SQL">

SELECT *

FROM itemlog,

(SELECT DISTINCT i1.itemid

FROM itemlog i1, itemlog i2

WHERE i1.source IN ('[charname1]', '[charname2]')

AND i1.itemid=i2.itemid

AND i2.source NOT IN ('null', '[charname1]', '[charname2]')

) As x

WHERE x.itemid=itemlog.itemid

ORDER BY itemlog.itemid, itemlog.id

</source>

The string 'null' is required in the NOT IN clause because of register events

<source lang="sql">

SELECT left(timedate, 10) As day, source, param1, count(*) As cnt

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>

</source>

=== Checking if someone is a bot (alternative) ===

<source lang="SQL">

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;