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 character into an account

-- get ids as we will need them later
select id as account_id from account where username = '[account]';
select id as character_account_id from account where username = '[character]';
update account set status = 'merged' where username ='[character]';
update characters set player_id = '[account_id]' where charname = '[character]';
insert into gameEvents (source, event, param1, param2) 
values ('[character]','accountmerge','[character_account_id]', '[character]-->[account]');

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