Support:Queries

Characters and accounts

Account belonging to a character

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

Characters belonging to an account

SELECT characters.*
FROM account, characters
WHERE characters.player_id=account.id AND username='[account]'

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.

cd /var/www/stendhal/scripts/cmd
php merge.php [oldaccount] [newaccount]

Did a password get changed?

SELECT account.username, address, service, passwordChange.timedate, oldpassword 
FROM account, passwordChange 
WHERE account.id = passwordChange.player_id AND username ='[account]';

Banning an IP

-- 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]");

Summarise activity from one IP address

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

Items

Item history of all items touched by suspicious characters

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

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

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

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

Bots

Finding Bots

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;

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.

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;

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;

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.

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

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:

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;