Untitled query #14489

This query is marked as a draft This query has been published by ԱշոտՏՆՂ.
SQL

SELECT DISTINCT log_type, log_action FROM logging ORDER BY log_type, log_action;

WITH Admins AS
  (SELECT user_name,
          user_id
   FROM user
   JOIN user_groups ON user_id = ug_user
   WHERE ug_group = 'sysop'),
     AdminLogs AS
  (SELECT log_type,
          log_action,
          actor_user
   FROM logging
   JOIN actor ON actor_id = log_actor
   WHERE actor_user IN
       (SELECT admins.user_id
        FROM Admins AS admins)
   AND log_timestamp >= DATE_FORMAT(CURDATE() - INTERVAL 6 MONTH, '%Y%m%d%H%i%S'))
SELECT user_name,
(SELECT count(*) FROM AdminLogs WHERE log_type = 'block' AND log_action = 'NULL' AND actor_user = user_id) 'block\nNULL',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'create' AND log_action = 'NULL' AND actor_user = user_id) 'create\nNULL',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'delete' AND log_action = 'NULL' AND actor_user = user_id) 'delete\nNULL',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'newusers' AND log_action = 'NULL' AND actor_user = user_id) 'newusers\nNULL',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'protect' AND log_action = 'NULL' AND actor_user = user_id) 'protect\nNULL',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'managetags' AND log_action = 'activate' AND actor_user = user_id) 'managetags\nactivate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'growthexperiments' AND log_action = 'addlink' AND actor_user = user_id) 'growthexperiments\naddlink',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'newusers' AND log_action = 'autocreate' AND actor_user = user_id) 'newusers\nautocreate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'block' AND log_action = 'block' AND actor_user = user_id) 'block\nblock',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'newusers' AND log_action = 'byemail' AND actor_user = user_id) 'newusers\nbyemail',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'contentmodel' AND log_action = 'change' AND actor_user = user_id) 'contentmodel\nchange',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'abusefilter' AND log_action = 'create' AND actor_user = user_id) 'abusefilter\ncreate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'create' AND log_action = 'create' AND actor_user = user_id) 'create\ncreate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'managetags' AND log_action = 'create' AND actor_user = user_id) 'managetags\ncreate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'newusers' AND log_action = 'create' AND actor_user = user_id) 'newusers\ncreate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'newusers' AND log_action = 'create2' AND actor_user = user_id) 'newusers\ncreate2',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'delete' AND log_action = 'delete' AND actor_user = user_id) 'delete\ndelete',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'managetags' AND log_action = 'delete' AND actor_user = user_id) 'managetags\ndelete',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'delete' AND log_action = 'delete_redir' AND actor_user = user_id) 'delete\ndelete_redir',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'delete' AND log_action = 'event' AND actor_user = user_id) 'delete\nevent',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'massmessage' AND log_action = 'failure' AND actor_user = user_id) 'massmessage\nfailure',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'gblblock' AND log_action = 'gblock2' AND actor_user = user_id) 'gblblock\ngblock2',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'spamblacklist' AND log_action = 'hit' AND actor_user = user_id) 'spamblacklist\nhit',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'merge' AND log_action = 'merge' AND actor_user = user_id) 'merge\nmerge',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'abusefilter' AND log_action = 'modify' AND actor_user = user_id) 'abusefilter\nmodify',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'protect' AND log_action = 'modify' AND actor_user = user_id) 'protect\nmodify',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'move' AND log_action = 'move' AND actor_user = user_id) 'move\nmove',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'protect' AND log_action = 'move_prot' AND actor_user = user_id) 'protect\nmove_prot',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'move' AND log_action = 'move_redir' AND actor_user = user_id) 'move\nmove_redir',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'contentmodel' AND log_action = 'new' AND actor_user = user_id) 'contentmodel\nnew',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'newusers' AND log_action = 'newusers' AND actor_user = user_id) 'newusers\nnewusers',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'upload' AND log_action = 'overwrite' AND actor_user = user_id) 'upload\noverwrite',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'patrol' AND log_action = 'patrol' AND actor_user = user_id) 'patrol\npatrol',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'protect' AND log_action = 'protect' AND actor_user = user_id) 'protect\nprotect',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'block' AND log_action = 'reblock' AND actor_user = user_id) 'block\nreblock',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'renameuser' AND log_action = 'renameuser' AND actor_user = user_id) 'renameuser\nrenameuser',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'timedmediahandler' AND log_action = 'resettranscode' AND actor_user = user_id) 'timedmediahandler\nresettranscode',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'delete' AND log_action = 'restore' AND actor_user = user_id) 'delete\nrestore',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'upload' AND log_action = 'revert' AND actor_user = user_id) 'upload\nrevert',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'delete' AND log_action = 'revision' AND actor_user = user_id) 'delete\nrevision',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'rights' AND log_action = 'rights' AND actor_user = user_id) 'rights\nrights',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'growthexperiments' AND log_action = 'setmentor' AND actor_user = user_id) 'growthexperiments\nsetmentor',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'massmessage' AND log_action = 'skipbadns' AND actor_user = user_id) 'massmessage\nskipbadns',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'massmessage' AND log_action = 'skipnouser' AND actor_user = user_id) 'massmessage\nskipnouser',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'thanks' AND log_action = 'thank' AND actor_user = user_id) 'thanks\nthank',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'block' AND log_action = 'unblock' AND actor_user = user_id) 'block\nunblock',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'protect' AND log_action = 'unprotect' AND actor_user = user_id) 'protect\nunprotect',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'tag' AND log_action = 'update' AND actor_user = user_id) 'tag\nupdate',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'upload' AND log_action = 'upload' AND actor_user = user_id) 'upload\nupload',
(SELECT count(*) FROM AdminLogs WHERE log_type = 'gblblock' AND log_action = 'whitelist' AND actor_user = user_id) 'gblblock\nwhitelist'
FROM Admins;

SELECT user_name,
          user_id
   FROM user
   JOIN user_groups ON user_id = ug_user
   WHERE ug_group = 'sysop'
#SELECT DISTINCT log_action
#FROM hywiki_p.logging
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...
