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;
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')
