ALLCAPS (ALLCAPS)

This query is marked as a draft This query has been published by Pppery.

This was originally aggregated but is too slow to be part of that run. It only needs to be run every few months.

SQL

/* 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 1 from user_groups where ug_user=user_id and ug_group="confirmed")
and log_timestamp > "20190501010101" limit 100;
*/ 
/* Moves without redirect */
use enwiki_p;
set @by_date = "20240301010101";
select log_id, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging
join actor_logging on actor_id = log_actor join user on user_id = actor_user join comment_logging on comment_id = log_comment_id 
where log_type="move" and log_params like '%"5::noredir";s:1:"1%' and log_timestamp > @by_date  
and comment_text not like "Automatically moved page while renaming the user%" and log_namespace != 828
and not (log_namespace=10 and log_title like '%.css')
and not exists (select 1 from user_groups where ug_user = actor_user and (ug_group="extendedmover" || ug_group="sysop" || ug_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_logging 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" && log_action != "delete_redir2")
      || log_type="contentmodel" /* Blame Sophivorus for this */)
and not exists (select 1 from user_groups where ug_user = actor_user and ug_group="sysop") and log_timestamp > @by_date;
/* 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_revision on rev_actor=actor_id join user on user_id=actor_user
where pr_type="edit" and pr_level="extendedconfirmed" and rev_timestamp > @by_date
and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="extendedconfirmed" || ug_group="sysop" || ug_group="bot"))
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);
/* Template 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_revision on rev_actor=actor_id join user on user_id=actor_user
where pr_type="edit" and pr_level="templateeditor" and rev_timestamp > @by_date
and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="templateeditor" || ug_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);
/* Full 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_revision on rev_actor=actor_id join user on user_id=actor_user
where pr_type="edit" and pr_level="sysop" and rev_timestamp > @by_date
and not exists (select 1 from user_groups where ug_user=user_id and ug_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_revision on rev_actor=actor_id join user on user_id=actor_user
where page_namespace=8 and rev_timestamp > @by_date
and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop")
and page_title != "GrowthMentors.json";
/* 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_revision 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 > @by_date and not exists (select 1 from user_groups where ug_user=user_id and ug_group="interface-admin");
/* Autopatrolled */
select page_title, user_name from pagetriage_page  join page on page_id=ptrp_page_id join user on user_id=ptrp_last_reviewed_by
where ptrp_reviewed=3 and page_namespace=0 and page_is_redirect=0
and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="autoreviewer" or ug_group="bot"))
order by ptrp_last_reviewed_by desc;
/* Not at all related to global rights, but a paranoia check in a query I run sporadically */
select page_title, user_name from pagetriage_page  join page on page_id=ptrp_page_id join user on user_id=ptrp_last_reviewed_by
where ptrp_last_reviewed_by=(select user_id from user where user_name="KHarlan (WMF)");
/* Also not at all related to global rights, but fits in with the autopatrolled misuse check above */
select * from logging_userindex join page on page_namespace=log_namespace and page_title=log_title
where log_actor=(select actor_id from actor_logging where actor_name="'zinbot")
and page_is_redirect=0
and log_timestamp > @by_date
and not exists (select 1 from templatelinks join linktarget on lt_id=tl_target_id where 
                tl_from=page_id and ((lt_namespace=828 and lt_title="RfD") 
                or (lt_namespace=10 and lt_title in ( "Disambiguation", "Set_index_article","Surname") -- Technical a protocol violation,
                    -- but these are common enough and need no action
                   )))
and not exists (select 1 from logging_logindex  where logging_logindex.log_type="pagetriage-curation" 
                and logging_logindex.log_namespace=logging_userindex.log_namespace
                and logging_logindex.log_title = logging_userindex.log_title
                and logging_logindex.log_timestamp > logging_userindex.log_timestamp)
order by log_timestamp desc limit 10000;
/* And more random stuff to check sporadically */
select * from page where page_namespace = 0 and page_is_redirect=0 and page_title regexp "^[A-Z0-9_\\W]*_\\([^a-z]*\\)$" 
and page_title not regexp "\\(([0-9]+_)?AM|FM|EP|TV|[0-9]+\\)$" -- Exclude various broadcasting stations per WP:NCBC
and page_title not regexp "\\([0-9]+([–-][0-9]+)?" -- Date based or hull number disambiguators are fine
and page_title not like "%C/20%" -- Exclude comet names per [[WP:NCASTRO]]. I disagree with this convention
and page_title not like "%C/19%" -- Exclude comet names per [[WP:NCASTRO]]. I disagree with this convention
and page_title not regexp "^VK_[0-9]+" -- Exclude some German tanks
and page_title not regexp "\\(ZVV|TILO\\)" -- Exclude some train lines that could probably have better names but whatever
and not exists (select 1 from pagelinks where pl_from=74115810 and pl_namespace=0 and pl_title=page_title) 
 -- Exclude things I've manually judged to be OK
order by page_title desc limit 1000;
/* Rate-limit */
SELECT (SELECT COUNT(*) from logging_userindex l2 
	WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+86400) 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_logging on log_actor=actor_id 
where l1.log_timestamp > @by_date and l1.log_type="newusers" 
and not exists (select 1 from user_groups where ug_user=actor_user and (ug_group="sysop" || ug_group="accountcreator" || ug_group="eventcoordinator"))
and (SELECT COUNT(*) from logging_userindex l2 
	WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+86400) and l2.log_actor=l1.log_actor
        and l2.log_type="newusers") > 6;
/* 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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...