Redirect autopatrol monitoring

Fork of Redirect autopatrol monitoring by DannyS712
This query is marked as a draft This query has been published by DreamRimmer.
SQL

/*
SELECT *
FROM actor
WHERE actor_name = 'DannyS712 bot III';

Result: 192619287
*/
SET @whitelistPage = 62534307;
SET @botActorId = 192619287;
SET @userNamespace = 2;
/* Days for the creation query not against recent changes */
SET @days = 30;

/* Pages created by whitelisted users and patrolled recently by bot that don't currently exist */
/*
SELECT
    patrol.rc_timestamp,
    patrol.rc_title,
    creator.actor_name,
    deletionreason.comment_text
FROM
	recentchanges_userindex patrol
JOIN
	logging_logindex creation
	ON
		patrol.rc_namespace = creation.log_namespace
    	AND patrol.rc_title = creation.log_title
    	AND creation.log_type = 'create'
JOIN
	actor_user creator
	ON creation.log_actor = creator.actor_id
JOIN
	(
		SELECT REPLACE(links.pl_title, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_namespace = @userNamespace
	) AS listed
	ON listed.username = creator.actor_name
LEFT JOIN
	page currentPages
	ON
		patrol.rc_namespace = currentPages.page_namespace
    	AND patrol.rc_title = currentPages.page_title
JOIN
	logging_logindex deletion
	ON
		patrol.rc_namespace = deletion.log_namespace
		AND patrol.rc_title = deletion.log_title
		AND deletion.log_type = 'delete'
JOIN
	comment deletionreason
	ON deletionreason.comment_id = deletion.log_comment_id
WHERE patrol.rc_actor = @botActorId
	AND patrol.rc_log_type = 'pagetriage-curation'
	AND currentPages.page_id IS NULL
ORDER BY patrol.rc_timestamp DESC
;
*/

/* Pages created by whitelisted users and patrolled ever by bot that don't currently exist */
SELECT
    patrol.log_timestamp,
    patrol.log_title,
    creator.actor_name,
    deletionreason.comment_text
FROM
	logging_userindex patrol
JOIN
	logging_logindex creation
	ON
		patrol.log_namespace = creation.log_namespace
    	AND patrol.log_title = creation.log_title
    	AND creation.log_type = 'create'
JOIN
	actor_user creator
	ON creation.log_actor = creator.actor_id
JOIN
	(
		SELECT REPLACE(links.pl_from, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_from_namespace = @userNamespace
	) AS listed
	ON listed.username = creator.actor_name
LEFT JOIN
	page currentPages
	ON
		patrol.log_namespace = currentPages.page_namespace
    	AND patrol.log_title = currentPages.page_title
JOIN
	logging_logindex deletion
	ON
		patrol.log_namespace = deletion.log_namespace
		AND patrol.log_title = deletion.log_title
		AND deletion.log_type = 'delete'
JOIN
	comment deletionreason
	ON deletionreason.comment_id = deletion.log_comment_id
WHERE patrol.log_actor = @botActorId
	AND patrol.log_type = 'pagetriage-curation'
	AND currentPages.page_id IS NULL
    AND creation.log_timestamp > (DATE_FORMAT((NOW() - INTERVAL @days DAY),'%Y%m%d%H%i%S'))
ORDER BY patrol.log_timestamp DESC
;

/* Pages created by whitelisted users and patrolled ever by bot that don't currently exist, COUNT by user */
SELECT
	COUNT(creation.log_id),
	creator.actor_name
FROM
	logging_userindex patrol
JOIN
	logging_logindex creation
	ON
		patrol.log_namespace = creation.log_namespace
    	AND patrol.log_title = creation.log_title
    	AND creation.log_type = 'create'
JOIN
	actor_user creator
	ON creation.log_actor = creator.actor_id
JOIN
	(
		SELECT REPLACE(links.pl_from, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_from_namespace = @userNamespace
	) AS listed
	ON listed.username = creator.actor_name
LEFT JOIN
	page currentPages
	ON
		patrol.log_namespace = currentPages.page_namespace
    	AND patrol.log_title = currentPages.page_title
JOIN
	logging_logindex deletion
	ON
		patrol.log_namespace = deletion.log_namespace
		AND patrol.log_title = deletion.log_title
		AND deletion.log_type = 'delete'
WHERE patrol.log_actor = @botActorId
	AND patrol.log_type = 'pagetriage-curation'
	AND currentPages.page_id IS NULL
    AND creation.log_timestamp > (DATE_FORMAT((NOW() - INTERVAL @days DAY),'%Y%m%d%H%i%S'))
GROUP BY creator.actor_name
ORDER BY COUNT(creation.log_id) DESC
;

/* Users on the whitelist that are blocked */
SELECT
	u.user_name,
    block.ipb_expiry,
	blockreason.comment_text,
	CASE
		WHEN block.ipb_sitewide = 1 THEN 'sitewide'
		ELSE (
			SELECT GROUP_CONCAT(
				CASE
					WHEN restriction.ir_type = 1 THEN (
						SELECT CONCAT( 'ns', page_namespace, '-', page_title ) FROM page WHERE page_id = restriction.ir_value
                    )
					WHEN restriction.ir_type = 2 THEN CONCAT( 'ns', restriction.ir_value, '-all' )
					ELSE 'Something went wrong'
				END
            )
			FROM ipblocks_restrictions restriction
			WHERE restriction.ir_ipb_id = block.ipb_id
        )
	END AS 'Restrictions'
FROM
	user u
JOIN
	(
		SELECT REPLACE(links.pl_from, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_from_namespace = @userNamespace
	) AS listed
	ON listed.username = u.user_name
JOIN
	ipblocks block
	ON block.ipb_user = u.user_id
JOIN
	comment blockreason
	ON blockreason.comment_id = block.ipb_reason_id
;

/* Locked accounts */
/* FIXME doesn't work with new multi-instance replicas...
SELECT
	u.user_name,
	gu.gu_locked
FROM
	user u
JOIN
	(
		SELECT REPLACE(links.pl_title, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_namespace = @userNamespace
	) AS listed
	ON listed.username = u.user_name
JOIN
	centralauth_p.globaluser gu
	ON gu.gu_name = u.user_name
WHERE gu.gu_locked > 0
;
*/

/* Already autopatrolled, no longer includes sysop */
SELECT
	u.user_name,
    uginfo.groups
FROM
	user u
JOIN
	(
		SELECT REPLACE(links.pl_from, '_', ' ') AS username
		FROM pagelinks links
		WHERE
			links.pl_from = @whitelistPage
		    AND links.pl_namespace = @userNamespace
	) AS listed
	ON listed.username = u.user_name
JOIN
	(
		SELECT
			ug.ug_user AS ugi_user,
		    GROUP_CONCAT(
				ug.ug_group,
		      		WHEN ug.ug_expiry IS NOT NULL THEN CONCAT('-', ug.ug_expiry)
      				ELSE ''
				END
		    ) AS groups
		FROM
			user_groups ug
		WHERE
			ug.ug_group IN ('autoreviewer', 'bot')
		GROUP BY
			ug.ug_user
    ) AS uginfo
	ON uginfo.ugi_user = u.user_id;
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...
