Wikipedia:Database reports/Users by log action/Configuration

logactions.py

edit
#!/usr/bin/env python2.5

# Copyright 2008 bjweeks, MZMcBride

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import datetime
import operator
import MySQLdb
import wikitools
import settings

report_title = settings.rootpage + 'Users by log action'

wiki = wikitools.Wiki(settings.apiurl)
wiki.login(settings.username, settings.password)

conn = MySQLdb.connect(host=settings.host, db=settings.dbname, read_default_file='~/.my.cnf', use_unicode=True)
cursor = conn.cursor()

def get_stats(type, action):
    cursor.execute(u'''
        /* logactions.py SLOW_OK */
        SELECT
          user_name,
          COUNT(log_timestamp)
        FROM logging
        JOIN user_ids
        ON user_id = log_user
        WHERE log_type = '%s'
        AND log_action = '%s'
        GROUP BY log_user;
    ''' % (type, action))
    return cursor.fetchall()

query_list = [
    {'name': 'Deletions',                 'short_name': 'DL', 'type': 'delete',     'action': 'delete'},
    {'name': 'Undeletions',               'short_name': 'UD', 'type': 'delete',     'action': 'restore'},
    {'name': 'Revision deletions',        'short_name': 'RD', 'type': 'delete',     'action': 'revision'},
    {'name': 'Event deletions',           'short_name': 'ED', 'type': 'delete',     'action': 'event'},
    {'name': 'Deletion suppressions',     'short_name': 'DS', 'type': 'suppress',   'action': 'delete'},
    {'name': 'Revision suppressions',     'short_name': 'RS', 'type': 'suppress',   'action': 'revision'},
    {'name': 'Event suppressions',        'short_name': 'ES', 'type': 'suppress',   'action': 'event'},
    {'name': 'Username suppressions',     'short_name': 'US', 'type': 'suppress',   'action': 'reblock'},
    {'name': 'Protections',               'short_name': 'PT', 'type': 'protect',    'action': 'protect'},
    {'name': 'Unprotections',             'short_name': 'UP', 'type': 'protect',    'action': 'unprotect'},
    {'name': 'Protection modifications',  'short_name': 'PM', 'type': 'protect',    'action': 'modify'},
    {'name': 'Blocks',                    'short_name': 'BL', 'type': 'block',      'action': 'block'},
    {'name': 'Unblocks',                  'short_name': 'UB', 'type': 'block',      'action': 'unblock'},
    {'name': 'Block modifications',       'short_name': 'BM', 'type': 'block',      'action': 'reblock'},
    {'name': 'User renames',              'short_name': 'UR', 'type': 'renameuser', 'action': 'renameuser'},
    {'name': 'User rights modifications', 'short_name': 'RM', 'type': 'rights',     'action': 'rights'},
    {'name': 'Whitelistings',             'short_name': 'WL', 'type': 'gblblock',   'action': 'whitelist'},
    {'name': 'De-whitelistings',          'short_name': 'DW', 'type': 'gblblock',   'action': 'dwhitelist'},
#    {'name': 'AbuseFilter modifications', 'short_name': 'AM', 'type': 'abusefilte', 'action': 'modify'}
]
user_stats = {}

for query in query_list:
    stats_query = get_stats(query['type'], query['action'])
    query['len'] = len(stats_query)
    for row in stats_query:
        user = unicode(row[0], 'utf-8')
        count = row[1]
        if user not in user_stats:
            user_stats[user] = {query['name']: count}
        else:
            user_stats[user][query['name']] = count

output = u''

report_template = u'{{shortcut|WP:LOGACTIONS}}\nUsers by log action; data as of <onlyinclude>%s</onlyinclude>.\n%s'

table_template = u'''
== %s ==
{| class="wikitable sortable" style="width:23em;"
|- style="white-space:nowrap;"
! No.
! User
! Count
|-
%s
|}
'''

for query in query_list:
    stat_dict = {}
    for user,stats in user_stats.iteritems():
        if query['name'] in stats:
            stat_dict[user] = stats[query['name']]
    stats = sorted(stat_dict.iteritems(), key=operator.itemgetter(1), reverse=True)[0:25]
    rows = []
    i = 1
    for user, count in stats:
        rows.append(u'''| %d\n| %s\n| %s\n|-''' % (i, user, count))
        i += 1
    output += table_template % (query['name'], '\n'.join(rows))
    if query['len'] > 25:
        output += "Full results are available [[{{FULLPAGENAME}}#Totals|below]].\n"

master_table_template = u'''
== Totals ==
Hover over the abbreviations to see the full action name.

{| class="wikitable sortable" style="width:100%%; margin:auto;"
|- style="white-space:nowrap;"
! No.
! User
%s
! Total
|-
%s class="sortbottom"
! colspan="2" | Totals
%s
|}
'''

new_query_list = []

for query in query_list:
    if query['len'] > 25:
        new_query_list.append(query)

query_list = new_query_list

rows = []
totals = dict([(query['name'], 0) for query in query_list])
totals['total'] = 0
i = 1
user_stats_sorted = sorted(user_stats.iteritems(), key=operator.itemgetter(0))
for user,stats in user_stats_sorted:
    row = []
    total = 0
    row.append(str(i))
    row.append(user)
    for query in query_list:
        if query['name'] in stats:
            row.append(str(stats[query['name']]))
            total += stats[query['name']]
            totals[query['name']] += stats[query['name']]
            totals['total'] += stats[query['name']]
        else:
            row.append('0')
    row.append(str(total))
    rows.append('| %s \n|-' % ('\n| '.join(row)))
    i += 1

output += master_table_template % (
    '\n'.join(['! <span title="%s">%s</span>' % (query['name'], query['short_name']) for query in query_list]), 
    '\n'.join(rows),
    '\n'.join([u'! style="text-align:left;" | %d' % totals[query['name']] for query in query_list]) + u'\n! style="text-align:left;" | %d' % totals['total']
)

cursor.execute('SELECT UNIX_TIMESTAMP() - UNIX_TIMESTAMP(rc_timestamp) FROM recentchanges ORDER BY rc_timestamp DESC LIMIT 1;')
rep_lag = cursor.fetchone()[0]
current_of = (datetime.datetime.utcnow() - datetime.timedelta(seconds=rep_lag)).strftime('%H:%M, %d %B %Y (UTC)')

final_output = report_template % (current_of, output)
final_output = final_output.encode('utf-8')
report = wikitools.Page(wiki, report_title)
report.edit(final_output, summary=settings.editsumm, bot=1)

cursor.close()
conn.close()

crontab

edit
20 0 * * * python ~/scripts/database-reports/logactions.py > /dev/null