User:Rxy/Meta talk:Administrators/Removal/April 2018/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')