SQL
AخA
/* Moves by non-autoconfirmed (Not worth querying, debatable whether it is actually a violation)
use enwiki_p;
select log_id, log_type, log_action, user_name, user_editcount, user_registration from logging
join actor on actor_id = log_actor join user on user_id=actor_user where log_type="move"
and (user_editcount < 10 || user_registration > (DATE_FORMAT((NOW() - INTERVAL 4 DAY),'%Y%m%d%H%i%S')))
and not exists (select * from user_groups where ug_user=user_id and ug_group="confirmed")
and log_timestamp > "20190501010101" limit 100;
*/
/* Moves without redirect */
use enwikinews_p;
select log_id, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging
join actor on actor_id = log_actor join user on user_id = actor_user join comment on comment_id = log_comment_id
where log_type="move" and log_params like '%"5::noredir";s:1:"1%' and log_timestamp > "20190101010101"
and comment_text not like "Automatically moved page while renaming the user%"
and log_namespace != 828
and not exists (select 1 from user_groups where ug_user = actor_user and (ug_group="sysop" || ug_group="bot"))
and not exists (select 1 from user_former_groups
where ufg_user = actor_user and (ufg_group="sysop" || ufg_group="bot"));
/* Other common admin actions */
select log_id, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging
join actor on actor_id = log_actor join user on user_id = actor_user
where (log_type = "block" || (log_type = "protect" and log_action != "move_prot") || (log_type="delete" && log_action != "delete_redir"))
and not exists (select 1 from user_groups where ug_user = actor_user and ug_group="sysop") and log_timestamp > "20190101010101"
and not exists (select 1 from user_former_groups where ufg_user = actor_user and ufg_group="sysop");
/* Edits to protected pages */
/* Semi-protection not worth querying and of debatable applicability */
/* Extended-confirmed protection */
select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision
join page_restrictions on pr_page=rev_page join page on page_id=rev_page join actor on rev_actor=actor_id join user on user_id=actor_user
where pr_type="edit" and pr_level="sysop" and rev_timestamp > "20190101010101"
and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop")
and not exists (select 1 from user_former_groups where ufg_user=user_id and ufg_group="sysop")
and not exists (select 1 from logging_logindex where log_namespace=page_namespace
and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);
/* MediaWiki namespace */
select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision
join page on page_id=rev_page join actor on rev_actor=actor_id join user on user_id=actor_user
where page_namespace=8 and rev_timestamp > "20190101010101"
and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop")
and not exists (select 1 from user_former_groups where ufg_user=user_id and ufg_group="sysop");
/* Edits to site CSS/JS */
select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision
join page on page_id=rev_page join actor on rev_actor=actor_id join user on user_id=actor_user
where page_namespace=8 and (page_content_model="css" || page_content_model="javascript")
and rev_timestamp > "20190101010101" and not exists (select 1 from user_groups where ug_user=user_id and ug_group="interface-admin")
and not exists (select 1 from user_former_groups where ufg_user=user_id and ufg_group="interface-admin");
/* Rate-limit */
SELECT (SELECT COUNT(*) from logging_userindex l2
WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+1000000) and l2.log_actor=l1.log_actor
and l2.log_type="newusers") as creation_count,
actor_user, actor_name, l1.log_timestamp from logging l1 join actor on log_actor=actor_id
where l1.log_timestamp > "20190101000000" and l1.log_type="newusers"
and not exists (select * from user_groups where ug_user=actor_user and (ug_group="sysop" || ug_group="accountcreator" || ug_group="eventcoordinator"))
and not exists (select * from user_former_groups
where ufg_user=actor_user and (ufg_group="sysop" || ufg_group="accountcreator" || ufg_group="eventcoordinator"))
and (SELECT COUNT(*) from logging_userindex l2
WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+1000000) and l2.log_actor=l1.log_actor
and l2.log_type="newusers") > 4;
/* XXX there are a few more ratelimits, but some are unlogged, and I don't consider it within scope of this project to
call out users for doing something as trivial as editing or moving slightly too fast, so only focusing on the
4 accounts/day limit" */
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.