RCPatrol - Quarry

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

SET @startdate = '20171001000000';
SET @enddate = '20180401000000';
-- regex for exempt users
SET @exempt_username_pattern = ' \\(WMF\\)$|-WMF$';
-- Define for what is a sysop action
SET @sysoplogtype = CAST(
	"(
	    'abusefilter',
	    'block',
	    'contentmodel',
	    'delete',
	    'import',
	    'lock',
	    'managetags',
	    'massmessage',
	    'merge',
	    'protect',
	    'rights',
	    'tag',
	    'timedmediahandler'
	)" 
	AS CHAR
);
-- misc
SET @logwhere = CONCAT(
	"ug_group = 'sysop' ",
	"AND log_type IN ",@sysoplogtype," ",
	"AND log_user_text NOT REGEXP @exempt_username_pattern ",
	"AND log_timestamp BETWEEN @startdate AND @enddate "
);
SET @zeroaction = CONCAT(
	"SELECT ug_user as userid, user_name as username, 0 ",
	    "FROM user_groups JOIN user ON ug_user = user_id ",
	    "WHERE ug_group = 'sysop' ",
	    "AND user_name NOT REGEXP @exempt_username_pattern "
);
-- show count of sysop actions in the term
SET @logcountsql = CONCAT(
	"SELECT userid, username, SUM(sysoplogs) as SysopActionCount ",
	"FROM (",
	    "SELECT ug_user as userid, log_user_text as username, count(*) as sysoplogs FROM user_groups LEFT JOIN logging ON ug_user = log_user ",
	        "WHERE ",@logwhere,
	        "GROUP BY ug_user ",
	    "UNION ",
	        @zeroaction,
	") as t GROUP BY userid ",
	"ORDER BY SysopActionCount asc "
);
PREPARE stmt FROM @logcountsql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- show count of edits in the term
SELECT userid, username, SUM(edit) as edits 
	FROM (
	    SELECT ug_user as userid, rev_user_text as username, count(*) as edit FROM user_groups LEFT JOIN revision ON ug_user = rev_user 
	        WHERE ug_group = 'sysop' AND rev_user_text NOT REGEXP @exempt_username_pattern AND rev_timestamp BETWEEN @startdate AND @enddate 
	        GROUP BY ug_user 
	    UNION
	        SELECT ug_user as userid, user_name as username, 0 as edit 
	            FROM user_groups JOIN user ON ug_user = user_id 
	            WHERE ug_group = 'sysop' AND user_name NOT REGEXP @exempt_username_pattern
	) as t GROUP BY userid
	ORDER BY edits asc;
-- show all edits in the term
SELECT CONCAT('[[Special:Redirect/user/',rev_user,'|',rev_user,']]') as userid, 
	    rev_user_text as username, 
	    CONCAT('[[Special:Diff/',rev_id,'|',rev_id,']]') as diff, 
	    CONCAT('[[Special:Redirect/page/',rev_page,'|',rev_page,']]') AS pageid, 
	    rev_timestamp 
	FROM user_groups LEFT JOIN revision ON ug_user = rev_user 
	WHERE ug_group = 'sysop' AND rev_user_text NOT REGEXP @exempt_username_pattern AND rev_timestamp BETWEEN @startdate AND @enddate 
	ORDER BY rev_user asc, rev_timestamp asc;
-- show all sysop actions in the term
SET @logsql = CONCAT(
	"SELECT CONCAT('[[Special:Redirect/logid/',log_id,'|',log_id,']]') as logid, ",
	    "log_type, ",
	    "log_timestamp, ",
	    "CONCAT('[[Special:Redirect/user/',log_user,'|',log_user,']]') as loguserid, ",
	    "log_user_text ",
	"FROM user_groups LEFT JOIN logging ON ug_user = log_user ",
	"WHERE ",@logwhere
);
PREPARE stmt FROM @logsql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
sysop log action
SELECT log_type, log_action FROM logging GROUP BY log_action,log_type;

+-------------------+--------------------+
| log_type          | log_action         |
+-------------------+--------------------+
			| lock              | flow-lock-topic    | -
			| review            | approve            |  outdated
			| review            | approve-a          |  |
			| review            | approve-i          |  |
			| review            | approve-ia         |  |
			| review            | unapprove          | /
		| gblblock          | NULL               | ---
		| gblblock          | gblock             | stewards or global renamers 
		| gblblock          | gblock2            |  |
		| gblblock          | gunblock           |  |
		| gblblock          | modify             |  |
		| gblrename         | NULL               |  |
		| gblrename         | merge              |  |
		| gblrename         | promote            |  |
		| gblrename         | rename             |  |
		| gblrights         | deleteset          |  |
		| gblrights         | groupperms         |  |
		| gblrights         | groupprms2         |  |
		| gblrights         | groupprms3         |  |
		| gblrights         | grouprename        |  |
		| gblrights         | newset             |  |
		| gblrights         | setchange          |  |
		| gblrights         | setnewtype         |  |
		| gblrights         | setrename          |  |
		| gblrights         | usergroups         |  |
		| globalauth        | NULL               |  |
		| globalauth        | delete             |  |
		| globalauth        | hide               |  |
		| globalauth        | lock               |  |
		| globalauth        | lockandhid         |  |
		| globalauth        | setstatus          |  |
		| globalauth        | unhide             |  |
		| globalauth        | unlock             |  |
		| mwoauthconsumer   | approve            |  |
		| mwoauthconsumer   | create-owner-only  |  |
		| mwoauthconsumer   | disable            |  |
		| mwoauthconsumer   | reenable           |  |
		| mwoauthconsumer   | reject             |  |
		| renameuser        | NULL               |  |
		| renameuser        | renameuser         |  |
		| usermerge         | deleteuser         |  |
		| usermerge         | mergeuser          | /
	| move              | NULL               |---
	| move              | move               | autoconfirmed / anon *
	| move              | move_redir         |  |
	| mwoauthconsumer   | propose            |  |
	| mwoauthconsumer   | update             |  |
	| newusers          | NULL               |  |
	| newusers          | autocreate         |  |
	| newusers          | byemail            |  |
	| newusers          | create             |  |
	| newusers          | create2            |  |
	| newusers          | newusers           | /
	| notifytranslators | sent               |---
	| pagelang          | pagelang           |  translation admins
	| pagetranslation   | associate          |  |
	| pagetranslation   | deletefnok         |  |
	| pagetranslation   | deletefok          |  |
	| pagetranslation   | deletelnok         |  |
	| pagetranslation   | deletelok          |  |
	| pagetranslation   | discourage         |  |
	| pagetranslation   | dissociate         |  |
	| pagetranslation   | encourage          |  |
	| pagetranslation   | mark               |  |
	| pagetranslation   | moveok             |  |
	| pagetranslation   | prioritylanguages  | /
	| pagetranslation   | unmark             |-
	| patrol            | NULL               |---
	| patrol            | autopatrol         |  | autoconfirmed / autopatrol / patroller
	| patrol            | patrol             |  |
	| spamblacklist     | hit                |  |
	| thanks            | thank              |  |
	| translationreview | group              |  |
	| translationreview | message            |  |
	| upload            | NULL               |  |
	| upload            | overwrite          | /
	| upload            | upload             |-
| abusefilter       | NULL               |------------
| abusefilter       | create             |  sysop or bureaucrat
| abusefilter       | modify             |   |
| block             | NULL               |   |
| block             | block              |   |
| block             | reblock            |   |
| block             | unblock            |   |
| contentmodel      | change             |   |
| contentmodel      | new                |   |
| delete            | NULL               |   |
| delete            | delete             |   |
| delete            | delete_redir       |   |
| delete            | event              |   |
| delete            | flow-delete-post   |   |
| delete            | flow-delete-topic  |   |
| delete            | flow-restore-post  |   |
| delete            | flow-restore-topic |   |
| delete            | restore            |   |
| delete            | revision           |   |
| import            | interwiki          |   |
| import            | upload             |   |
| lock              | flow-restore-topic |   |
| managetags        | create             |   |
| managetags        | delete             |   |
| massmessage       | failure            |   |
| massmessage       | send               |   |
| massmessage       | skipbadns          |   |
| massmessage       | skipnouser         |   |
| massmessage       | skipoptout         |   |
| merge             | merge              |   |
| protect           | modify             |   |
| protect           | move_prot          |   |
| protect           | protect            |   |
| protect           | unprotect          |   |
| rights            | NULL               |   |
| rights            | rights             |<--+--- sysop / bureaucrat / steward
| tag               | update             |  /
| timedmediahandler | resettranscode     |-


('abusefilter','block','contentmodel','delete','import','lock','managetags','massmessage','merge','protect','rights','tag','timedmediahandler')
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...
