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

<source lang="SQL"> -- 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]'); </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 3 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 ('marquis') GROUP BY hour ORDER BY id DESC LIMIT 500; </source>