Jump to content

User:Jamesday/queries

From Wikitech

Watchlists

Largest 100

select wl_user as id, user_name as user, count(*) as number
 from watchlist, user
 where wl_user=user_id
 group by wl_user
 order by number desc limit 100;

AOL

List blocks in place

select ipb_id as id, ipb_address, ipb_user as user, ipb_by,
 left(user_name,20) as by_name, ipb_timestamp, ipb_expiry,
 ipb_auto as auto, left(ipb_reason,100)
 from ipblocks, user
 where user_id = ipb_by
and
(
   ipb_address like '64.12.%'
or ipb_address like '149.174.%'
or ipb_address like '152.163%'
or ipb_address like '172.128.%'
or ipb_address like '172.192.%'
or ipb_address like '172.2%'
or ipb_address like '195.93.%'
or ipb_address like '198.81.%'
or ipb_address like '202.67.%'
or ipb_address like '205.188.%'
) limit 1000;

Set blocks to expire immediately

update ipblocks set ipb_expiry = ipb_timestamp
 where
(
   ipb_address like '64.12.%'
or ipb_address like '149.174.%'
or ipb_address like '152.163%'
or ipb_address like '172.128.%'
or ipb_address like '172.192.%'
or ipb_address like '172.2%'
or ipb_address like '195.93.%'
or ipb_address like '198.81.%'
or ipb_address like '202.67.%'
or ipb_address like '205.188.%'
);