SQL
AخA
SET @startdate = '20221001000000';
SET @enddate = '20230331235959';
-- regex for exempt users
SET @exempt_username_pattern = ' \\(WMF\\)$|-WMF$|WMFOffice';
-- Define for what is a sysop action
SET @sysoplogtype = CAST(
"(
'abusefilter',
'block',
'contentmodel',
'delete',
'import',
'managetags',
'massmessage',
'merge',
'protect',
'tag',
'timedmediahandler'
)"
AS CHAR
);
-- misc
SET @logwhere = CONCAT(
"ug_group = 'sysop' ",
"AND log_type IN ",@sysoplogtype," ",
"AND actor_name 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, actor_name as username, count(*) as sysoplogs FROM user_groups LEFT JOIN actor ON ug_user=actor_user JOIN logging ON actor_id = log_actor ",
"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, actor_name as username, count(*) as edit FROM user_groups LEFT JOIN actor ON ug_user=actor_user JOIN revision ON actor_id = rev_actor
WHERE ug_group = 'sysop' AND actor_name 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/',actor_user,'|',actor_user,']]') as userid,
actor_name 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 actor ON ug_user=actor_user JOIN revision ON actor_id = rev_actor
WHERE ug_group = 'sysop' AND actor_name NOT REGEXP @exempt_username_pattern AND rev_timestamp BETWEEN @startdate AND @enddate
ORDER BY actor_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/',actor_user,'|',actor_user,']]') as loguserid, ",
"actor_name ",
"FROM user_groups LEFT JOIN actor ON ug_user=actor_user LEFT JOIN logging ON actor_id=log_actor ",
"WHERE ",@logwhere
);
PREPARE stmt FROM @logsql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
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.