Fork of Meta-Wiki Inactivity Check April 2022 by MarcoAurelio
This query is marked as a draft This query has been published by MarcoAurelio.

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.

Checking query status...