Cross-wiki upload from *.wikipedia.org [190716—270716] (living & tagged & deleted & from where)

https://phabricator.wikimedia.org/T120867 (Large amounts of unwanted files (mostly copyvios) uploaded via cross-wiki upload tool)
To check how AbuseFilter_for_cross-wiki_uploads (introduced 14.07/15.07.2016) is working...
See also at Commons: User:Gunnex/Cross-wiki uploads 19.07.2016–27.07.2016
* 1st run on 28.07.2016: 4.690 rows. 4.025 living (428 pending deletion), 665 deleted
* 2nd run on 28.07.2016: 4.716 rows**, 4.043 living (489 pending deletion), 673 deleted
* 3rd run on 08.08.2016: 4.775 rows**, 3.447 living (557 pending deletion) , 1.328 deleted
* 4th run on 13.08.2016: 4.772 rows**, 3.166 living (533 pending deletion), 1.606 deleted
* 5th run on 20.08.2016: 4.723 row**,  2.659 living (345 pending deletion),  2.064 deleted
**) including some double entries due to bug [[phabricator:T140522]]

SQL

USE commonswiki_p;
SELECT CONCAT('[[:File:', img_name, ']]') AS Name, img_timestamp AS UploadDate, user_registration AS RegistrationDate, img_description AS Origin, 'Not deleted' AS Status, tl_title AS Template, '' AS DeletionDate, '' AS DeletionReason FROM image
JOIN page ON page_title = img_name AND page_namespace = 6
JOIN user ON img_user = user_id
LEFT JOIN templatelinks ON page_id = tl_from AND tl_namespace = 10 AND tl_title IN ('Deletion_template_tag', 'No_permission_since', 'No_source_since', 'Copyvio')
WHERE img_timestamp > 20160719000000 AND img_timestamp < 20160727240000
AND img_description LIKE 'Cross-wiki upload from %'
UNION ALL
SELECT CONCAT('[[:File:', oi_name, ']]') AS Name, oi_timestamp AS Uploaddate, user_registration AS RegistrationDate, oi_description AS Origin, 'Overwritten' AS Status, tl_title AS Template, '' AS DeletionDate, '' AS DeletionReason FROM oldimage
JOIN page ON page_title = oi_name AND page_namespace = 6
JOIN user ON oi_user = user_id
LEFT JOIN templatelinks ON page_id = tl_from AND tl_namespace = 10 AND tl_title IN ('Deletion_template_tag', 'No_permission_since', 'No_source_since', 'Copyvio')
WHERE oi_timestamp > 20160719000000 AND oi_timestamp < 20160727240000
AND oi_description LIKE 'Cross-wiki upload from %'
UNION ALL
SELECT CONCAT('[[:File:', fa_name, ']]') AS Name, fa_timestamp AS Uploaddate, user_registration AS RegistrationDate, fa_description AS Origin, 'Deleted' AS Status, '' AS Template, fa_deleted_timestamp AS DeletionDate, fa_deleted_reason AS DeletionReason FROM filearchive
JOIN user ON fa_user = user_id
WHERE fa_timestamp > 20160719000000 AND fa_timestamp < 20160727240000
AND fa_description LIKE 'Cross-wiki upload from %'
GROUP BY Uploaddate

SQL

Checking query status...