Support:Queries

Characters and accounts

Account belonging to a character

<source lang="SQL"> SELECT account.* FROM account, characters WHERE characters.player_id=account.id AND charname='[character]'; </source>

Characters belonging to an account

<source lang="SQL"> SELECT characters.* FROM account, characters WHERE characters.player_id=account.id AND username='[account]' </source>

Merge all characters into an account

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

SELECT account.username, account.status
FROM characters, account
WHERE account.id=player_id AND charname='[charname]';

2. As user marauroa execute the following script. [oldaccount] 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; </source>

Items

Item history of all items touched by suspicious characters

<source lang="SQL"> SELECT * FROM itemlog,

 (SELECT DISTINCT itemid 
  FROM itemlog
  WHERE source IN ('[charname1]', '[charname2]')
 ) As x

WHERE x.itemid=itemlog.itemid ORDER BY itemlog.itemid, itemlog.id </source>

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

Bots

Finding Bots

<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>

Checking if someone is a bot

The following queries lists the number of "use"-actions per player and hour. If there are high number of uses for several hours in a row, it is likely a bot.

<source lang="SQL"> SELECT source, left(timedate, 13) As day, count(*) As cnt FROM gameEvents 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)

<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:

| 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 |

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>