User:Steinsplitter/queries

From Wikimedia Commons, the free media repository
Jump to navigation Jump to search

A few querys... See also https://quarry.wmflabs.org/Steinsplitter

commonswiki_p[edit]

Edits using COM:VFC[edit]

select rc_user_text,
COUNT(rc_comment) AS vfc_count
from recentchanges 
where rc_comment like "%VisualFileChange%" 
group by rc_user_text 
order by vfc_count DESC;

Wrong delinks by commons delinker[edit]

SELECT rc_timestamp AS timestamp,
rc_user_text AS delinkerbot,
rc_comment AS delinkreason,
rc_namespace as namespace,
rc_title as pagename,
rc_this_oldid AS oldid
FROM recentchanges 
WHERE rc_comment LIKE "%Unused_and_implausible%" 
AND rc_user_text = "CommonsDelinker"
OR rc_user_text = "Dateientlinkerbot"
OR rc_user_text = "Filedelinkerbot"
ORDER BY timestamp DESC;

Edits on COM:QIC by IP's[edit]

SELECT rc_timestamp,
rc_user_text,
rc_title,
rc_this_oldid
FROM recentchanges
WHERE rc_title = "Quality_images_candidates/candidate_list"
AND rc_user  =  "0"
LIMIT 60;

All files with *all_rights_reserved* in exif[edit]

SELECT
CONCAT( 'File:', img_name) AS file,
img_user_text AS comuser,
user_editcount AS edits
FROM image
INNER JOIN user ON user_id = img_user
WHERE img_metadata LIKE "%all_rights_reserved%"
AND user_editcount < 88
ORDER BY comuser ASC;

BSicons protections[edit]

SELECT CONCAT( 'File:', page_title ) AS pageb
FROM page_restrictions
INNER JOIN page ON pr_page = page_id 
WHERE page_title LIKE '%BSicon%';

Upload log conter[edit]

SELECT user.user_name, COUNT(logging.log_user) FROM user
INNER JOIN logging
ON logging.log_user = user.user_id
WHERE logging.log_type = "upload"
GROUP BY logging.log_user
ORDER BY COUNT(logging.log_user) DESC
Limit 100;

Uploads by users not in a local usergroup[edit]

SELECT
DATE_FORMAT(rc_timestamp, "%b %d %Y %h:%i %p") AS timestamp,
CONCAT( 'File:', rc_title) AS file,
rc_user_text as user,
user_editcount as editcount
FROM recentchanges
LEFT JOIN user_groups
ON rc_user=ug_user
INNER JOIN user
ON rc_user=user_id
WHERE ug_group IS NULL
AND rc_type = "upload"
ORDER BY rc_timestamp DESC
LIMIT 50;

Possible autopatrolled candidates[edit]

SELECT DISTINCT rc_user_text,
 DATE_FORMAT(user_registration, "%Y-%m-%d") AS registration,
 user_editcount,
 IF(rc_user_text IN (
  SELECT log_title
  FROM logging
  WHERE log_type = "block"
 ), "Yes", "No") AS blocked_prev
FROM recentchanges
LEFT JOIN user_groups
 ON rc_user=ug_user
INNER JOIN user
 ON rc_user=user_id
LEFT JOIN ipblocks_ipindex
 ON rc_user=ipb_user
WHERE ug_group IS NULL
 AND user_editcount > 1800
 AND ipb_user IS NULL
ORDER BY user_editcount DESC;

Recent revision deletions[edit]

SELECT
DATE_FORMAT(rc_timestamp, "%b %d %Y %h:%i %p") AS tm,
rc_user_text,
rc_namespace AS ns,
rc_title,
rc_comment
FROM recentchanges
WHERE rc_log_type = "delete"
AND rc_log_action = "revision";

(C) finder using upload log & exif[edit]

SELECT
CONCAT( 'File:', img_name) AS file,
img_user_text AS user
FROM recentchanges
INNER JOIN image ON  img_user = rc_user
WHERE img_metadata LIKE "%all_rights_reserved%"
AND rc_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -25 DAY), '%Y%m%d%H%i%s')
AND rc_log_type = "upload"
ORDER BY user ASC;

copyright related blocks (not indef)[edit]

SELECT
 ipb_id AS blockid,
 ipb_address AS user,
 user_editcount AS editcount,
 ipb_reason AS reason,
 DATE_FORMAT(ipb_expiry, "%b %d %Y") AS expry,
 ipb_by_text AS admin
FROM ipblocks
INNER JOIN user
 ON user_id=ipb_user
WHERE ipb_auto = "0"
AND ipb_expiry NOT LIKE "infinity"
AND (
      ipb_reason LIKE "%unfree files%"
      OR ipb_reason LIKE "%files without source%"
      OR ipb_reason LIKE "%files without licensing%"
      OR ipb_reason LIKE "%Incorrectly licensing files%"
     )
;

Declined filemove requests[edit]

SELECT
 DATE_FORMAT(rc_timestamp, "%b %d %Y %h:%i %p") AS timestamp,
 REPLACE(ug_group,'sysop','admin') AS usergroup,
 rc_user_text AS user,
 REPLACE( CONCAT( 'File:', rc_title),'_',' ' ) AS file,
 rc_comment AS comment,
 rc_this_oldid AS diff
FROM recentchanges
INNER JOIN
  (
   SELECT
   ug_user,
   ug_group
   FROM user_groups
   WHERE ug_group = "sysop"
   OR ug_group = "filemover"
   ) AS ugu
ON rc_user = ug_user
WHERE rc_namespace = "6"
AND rc_source = "mw.edit"
AND (ug_group="sysop" OR ug_group="filemover")
AND rc_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -3 DAY), '%Y%m%d%H%i%s')
AND (
      rc_comment LIKE "%No valid reason stated, see the [[COM:MOVE|rename guidelines]]%"
      OR rc_comment LIKE "%No valid reason stated, see the rename guidelines%"
      OR rc_comment LIKE "%see the [[COM:MOVE|rename guidelines]]%"
      OR rc_comment LIKE "%see the [[COM:RENAME|rename guidelines]]%"
      OR rc_comment LIKE "%see the [[COM:FR|rename guidelines]]%"
     )
ORDER BY rc_timestamp DESC
LIMIT 45;

Recently active license reviewers with lr activity[edit]

SELECT DISTINCT
 rc_user_text AS user,
 REPLACE(ug_group,'sysop','admin') AS usergroup,
 ug_bot AS bot
FROM recentchanges
INNER JOIN
  (
   SELECT
   ug_user,
   ug_group
   FROM user_groups
   WHERE ug_group = "sysop"
   OR ug_group = "Image-reviewer"
   ) AS lrgroup /* is the user a lr or sysop? */
ON rc_user = ug_user
LEFT JOIN
  (
   SELECT
   ug_user AS  ug_bot_user,
   ug_group AS ug_bot
   FROM user_groups
   WHERE ug_group = "bot"
   ) AS isbot /* is the user a bot? */
ON rc_user = ug_bot_user
WHERE rc_namespace = "6" /* only file namespace */ 
AND (ug_group="sysop" OR ug_group="Image-reviewer")
AND (
      rc_comment LIKE "%icense review%" /* used by lr gadget*/
      OR rc_comment LIKE "%LR passed%"
      OR rc_comment LIKE "%: passed%" /* for lr bots */
     )
ORDER BY rc_user_text ASC;

No TA action in the last 365 days[edit]

SELECT
  user_name
FROM user_groups
INNER JOIN user
ON ug_user=user_id
WHERE ug_group = "translationadmin"
AND user_id NOT IN
  (
   SELECT DISTINCT
   log_user AS q1
   FROM logging
   WHERE log_type = "pagetranslation"
   AND log_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -365 DAY), '%Y%m%d%H%i%s')
   )
AND user_id NOT IN
  (
   SELECT
   ug_user AS ugsysop
   FROM user_groups
   WHERE ug_group = "sysop"
   );

Most deleted files (exactly same, sha1)[edit]

SELECT COUNT(filearchive.fa_sha1) AS COUNT,
fa_sha1
FROM filearchive
GROUP BY filearchive.fa_name
ORDER BY COUNT DESC
LIMIT 50;

Uploads per month[edit]

SELECT
 DATE_FORMAT(log_timestamp, "%Y-%M") AS raw,
 DATE_FORMAT(log_timestamp, "%Y") AS y,
 DATE_FORMAT(log_timestamp, "%M") AS m,
 COUNT(log_id) AS uploads
FROM logging
WHERE log_type = 'upload'
GROUP BY raw;

MediaWiki edits by user[edit]

SELECT
rev_user_text AS user,
COUNT(rev_user_text) as count
FROM revision
INNER JOIN page
ON rev_page  = page_id
WHERE page_namespace = "8"
GROUP BY user
ORDER BY count DESC
LIMIT 40;

DR edits by user[edit]

SELECT
rev_user_text AS user,
COUNT(rev_user_text) as ucount
FROM revision
INNER JOIN page
ON rev_page  = page_id
WHERE page_namespace = "4"
AND page_title LIKE "Deletion_requests/%"
GROUP BY user
ORDER BY ucount DESC
LIMIT 40;

Categorymoves by users with low editcount[edit]

SELECT
DATE_FORMAT(rc_timestamp, "%b %d %Y %h:%i %p") AS ts,
REPLACE(rc_title,'_',' ') AS oldcatname,
REPLACE( SUBSTRING_INDEX(SUBSTRING_INDEX(rc_params,';',2),':',-2) ,'"',' ') AS newcatname, /* unserialize */
rc_user_text AS user,
user_editcount AS editcount
FROM recentchanges
INNER JOIN user
ON rc_user=user_id
WHERE rc_log_action = "move"
AND rc_namespace = "14"
AND user_editcount < 50
ORDER BY rc_timestamp DESC;

Uploader with the most uplaods in the last ~30 days (40)[edit]

SELECT
rc_user_text,
ug_group as ug,
COUNT(rc_id) AS lcount
FROM recentchanges
LEFT JOIN
  (
   SELECT
   ug_user,
   ug_group
   FROM user_groups
   WHERE ug_group = "bot"
   OR ug_group = "gwtoolset"
   ) AS ugu
ON rc_user = ug_user
WHERE rc_log_type = "upload"
GROUP BY rc_user_text 
ORDER BY lcount DESC
LIMIT 40;

Possible broken files[edit]

SELECT
CONCAT( '* [[:File:',img_name, ']]') AS file
FROM image
where img_height = "0"
AND img_minor_mime NOT IN ('ogg', 'x-flac', 'midi', 'wav', 'webm')
LIMIT 1;

View gadget OTRS taggings[edit]

SELECT
DATE_FORMAT(rc_timestamp, "%b %d %Y %h:%i %p") AS timestamp,
rc_user_text AS user,
REPLACE(rc_comment,'([[MediaWiki:Gadget-PermissionOTRS.js|Script]]) ','') AS esum
FROM recentchanges
WHERE rc_comment LIKE "%[[MediaWiki:Gadget-PermissionOTRS.js|Script]]%"
ORDER BY rc_timestamp DESC;

Count of open (not archived) deletion requests sorted by month[edit]

SELECT
page_title AS DR,
COUNT(tl_from) AS count
FROM templatelinks
INNER JOIN page
ON tl_from = page_id 
WHERE tl_title LIKE "Deletion_requests/%"
AND tl_namespace = "4"
AND page_namespace = "4"
AND page_title REGEXP "^Deletion_requests\/[0-9]{4}\/[0-9]{2}\/[0-9]{2}$"
GROUP BY  DR
ORDER BY count;

PD textlogos nominated for deletion[edit]

SELECT page_title AS file
FROM page
JOIN templatelinks
ON page_id = tl_from
AND tl_title="PD-shape"
AND tl_namespace = 10
AND page_namespace = 6
AND page_title IN (SELECT page_title
FROM page
JOIN templatelinks
ON page_id = tl_from
AND tl_title="Delete"
AND tl_namespace = 10
AND page_namespace = 6)
ORDER by page_title ASC;

Scan new uploads exifbased for possible copyvios[edit]

SELECT rc_title AS file,
 DATE_FORMAT(rc_timestamp, "%b %d %Y %h:%i %p") AS timestamp,
 rc_user_text AS user
FROM recentchanges
LEFT JOIN user_groups
 ON rc_user=ug_user
LEFT JOIN image
 ON rc_title=img_name
INNER JOIN user
 ON rc_user=user_id
WHERE ug_group IS NULL
AND rc_log_action = "upload"
AND (
     img_metadata LIKE "%Photoshop%"
     OR img_metadata LIKE "%all_rights_reserved%"
     OR img_metadata LIKE "%Alle_Rechte_vorbehalten%"
     )
AND user_editcount <= 25
AND rc_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -7 DAY), '%Y%m%d%H%i%s')
AND rc_title NOT IN (SELECT
                         page_title
                       FROM page
                       JOIN templatelinks
                       ON page_id = tl_from
                       WHERE tl_title="PermissionOTRS"
                       AND tl_namespace = 10)
ORDER BY rc_timestamp DESC;

OTRS template added by non members not in local usergroup[edit]

SELECT
 DATE_FORMAT(afl_timestamp, "%Y-%m-%d %H:%i:%s") AS timestamp,
 afl_namespace AS ans,
 REPLACE(afl_title,'_',' ' ) AS page,
 afl_user_text AS user,
 user_editcount AS editcount,
 DATE_FORMAT(user_registration, "%Y-%m-%d") AS reg,
 afl_rev_id AS rev
FROM abuse_filter_log
LEFT JOIN user
 ON user_id = afl_user
LEFT JOIN user_groups
 ON afl_user=ug_user
WHERE afl_filter = "69"
AND ug_group IS NULL
AND afl_rev_id IS NOT NULL
ORDER BY afl_timestamp DESC
limit 200;

Admin actions by admins in the last ~30 days[edit]

SELECT 
 rc_user_text AS sysop,
 count(rc_id) AS admin_actions,
 edits_user_count AS sysopedits
FROM recentchanges
LEFT JOIN
  (
   SELECT
   rc_user AS edits_user,
   count(rc_id) AS edits_user_count
   FROM recentchanges
   WHERE rc_source = "mw.edit"
   OR rc_source = "mw.new"
   GROUP BY rc_user
   ) AS isbot
ON rc_user = edits_user
WHERE rc_log_type IN ("block","delete","import","protect","rights")
AND rc_user IN
  (
   SELECT
   ug_user AS rc_user
   FROM user_groups
   WHERE ug_group = "sysop"
   )
AND rc_type != "5"
GROUP BY rc_user_text
ORDER BY rc_user_text ASC;

Automated edits by year[edit]

COM:VFC 

SELECT 
 DATE_FORMAT(rev_timestamp,"%Y") AS y,
 COUNT(rev_id) AS count
FROM revision
WHERE rev_comment LIKE "%Using%COM:VFC%"
OR rev_comment LIKE "%Using%MediaWiki:VisualFileChange.js%"
group by y;

Cat-a-lot 

SELECT 
 DATE_FORMAT(rev_timestamp,"%Y") AS y,
 COUNT(rev_id) AS count
FROM revision
WHERE rev_comment LIKE "%[[Help:Cat-a-lot|Cat-a-lot]]%"
group by y;

HotCat 

SELECT 
 DATE_FORMAT(rev_timestamp,"%Y") AS y,
 COUNT(rev_id) AS count
FROM revision
WHERE rev_comment LIKE "%|HotCat%"
group by y;

By hand patrollings counter[edit]

SELECT 
 log_user_text AS user,
 COUNT(log_id) AS count
FROM logging
WHERE log_action  = 'patrol'
AND log_params LIKE '%"6::auto";i:0%'
GROUP by log_user_text
ORDER BY count DESC
LIMIT 200;


Admins with less than five adminactions in the last six months[edit]

SELECT
 user_name AS sysop,
 admin_actions
FROM user_groups
LEFT JOIN
  (
SELECT log_user_text,
log_user,
count(log_id) AS admin_actions
FROM logging
WHERE log_type IN ("block","delete","import","protect","rights", "merge", "massmessage", "abusefilter")
AND log_type != "5"
AND log_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -6 MONTH), "%Y%m%d%H%i%s")
GROUP BY log_user_text
 ) AS sysc
ON log_user = ug_user
INNER JOIN user
ON user_id = ug_user
WHERE ug_group = "sysop"
AND ( admin_actions < 5 OR admin_actions IS NULL)
ORDER BY admin_actions DESC;

cdc command example[edit]

SELECT REPLACE( CONCAT( "{{move cat|", cat_title, "|", REPLACE(cat_title, "in_countries", "by_country")  ,  "|reason=ASPERCFD|user=username}}" ) , "_", " ") AS movecommand
FROM category
WHERE cat_title LIKE "%in_countries%"
LIMIT 700;

metawiki_p[edit]

No global rename action in the last 356 days[edit]

SELECT DISTINCT user_name AS inactive_renamer
FROM user_groups
INNER JOIN user ON user_id = ug_user
WHERE ug_group = "global-renamer"
AND ug_user NOT IN (
SELECT DISTINCT log_user as ug_user
FROM logging
WHERE log_type = "gblrename"
AND log_timestamp > DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -365 DAY), '%Y%m%d%H%i%s')
);

centralauth_p[edit]

CA accounts containing *(WMF) or -*WMF[edit]

SELECT
  gu_locked as locked,
  gu_name as user,
  gu_registration as ca_created
FROM globaluser
WHERE gu_name LIKE "%(WMF)"
OR gu_name LIKE "%-WMF";

Locked accounts in local user groups on commons[edit]

SELECT gu_name
FROM globaluser
JOIN commonswiki_p.user
ON gu_name = user_name
JOIN commonswiki_p.user_groups
ON user_id = ug_user
WHERE gu_locked = "1";

foundationswiki_p[edit]

Possible volunteer account[edit]

SELECT
 user_name AS user,
 user_editcount
FROM user
WHERE user_id NOT IN
  (SELECT ipb_id
   FROM ipblocks
  )
AND user_name NOT LIKE "%-WMF"
AND user_name NOT LIKE "%(WM%)"
ORDER BY user_name ASC;

Possible role accounts[edit]

SELECT
 user_name AS user,
 user_editcount
FROM user
WHERE user_id NOT IN
  (SELECT ipb_id
   FROM ipblocks
  )
AND (
     user_name LIKE "%-WMF"
     OR user_name LIKE "%(WM%)" 
     )
ORDER BY user_name ASC;

crosswiki[edit]

Get number of active users & pages from all wikis[edit]

#!/bin/bash
wikis=$(mysql --defaults-file=~/replica.my.cnf -h s1.labsdb meta_p -e "SELECT dbname FROM wiki;") # get all databasenames

for dbname in $wikis;
do
        echo ------ $dbname ------
	date -u # get timestamp
        echo "SELECT ss_active_users AS active_users,
	      ss_total_pages AS total_pages
	      FROM site_stats
	      LIMIT 1;" | mysql --defaults-file=~/replica.my.cnf -h s1.labsdb ${dbname}_p -t # perform querys on db's
done;
Retrieved from "https://commons.wikimedia.org/w/index.php?title=User:Steinsplitter/queries&oldid=409014252"