commit 3403859aee7fe8df73fd9c10f4a44216296d9861 Author: Lunar <lunar@torproject.org> Date: Tue Mar 25 16:17:36 2014 +0100 Add script used to produce monthly reports --- monthly-report/README.txt | 13 +++ monthly-report/monthly_stats.py | 187 +++++++++++++++++++++++++++++++++++++++ 2 files changed, 200 insertions(+) diff --git a/monthly-report/README.txt b/monthly-report/README.txt new file mode 100644 index 0000000..d5549d3 --- /dev/null +++ b/monthly-report/README.txt @@ -0,0 +1,13 @@ +Monthly reporting scripts +========================= + +`monthly_stats.py` is meant to be run on rude.torproject.org and will +produce most statistics for a monthly help desk report. + +Usage: monthly_stats.py [YEAR MONTH] + +By default the report will be for the current month. + +`~/.pgpass` should be properly filled, e.g.: + + drobovi.torproject.org:5432:rt:rtreader:A_SECRET_PASSWORD diff --git a/monthly-report/monthly_stats.py b/monthly-report/monthly_stats.py new file mode 100755 index 0000000..81625f8 --- /dev/null +++ b/monthly-report/monthly_stats.py @@ -0,0 +1,187 @@ +#!/usr/bin/python +# +# This program is free software. It comes without any warranty, to +# the extent permitted by applicable law. You can redistribute it +# and/or modify it under the terms of the Do What The Fuck You Want +# To Public License, Version 2, as published by Sam Hocevar. See +# http://sam.zoy.org/wtfpl/COPYING for more details. + +import calendar +import datetime +import psycopg2 +import re +import subprocess +import sys +import yaml + +RT_CONNINFO = "host=drobovi.torproject.org sslmode=require user=rtreader dbname=rt" + +SELECT_TRANSACTIONS_QUERY = """ + SELECT tickets.id, tickets.lastupdatedby, queues.name, transactions.created, transactions.field, transactions.newvalue + FROM tickets + JOIN queues ON (queues.id = tickets.queue) + JOIN transactions ON (transactions.objectid = tickets.id + AND transactions.objecttype = 'RT::Ticket') + WHERE tickets.lastupdated >= %s + AND tickets.created < %s + INTERVAL '1 MONTH' + AND transactions.created < %s + INTERVAL '1 MONTH' + ORDER BY tickets.id, transactions.id; +""" + +SELECT_QUEUE_NAME_QUERY = """ + SELECT queues.name + FROM queues + WHERE queues.id = %s +""" + +SELECT_USER_NAME_QUERY = """ + SELECT users.name + FROM users + WHERE users.id = %s +""" + +def get_queue_name(queue_id): + global con + + cur = con.cursor() + try: + cur.execute(SELECT_QUEUE_NAME_QUERY, (queue_id,)) + return cur.fetchone()[0] + finally: + cur.close() + +def get_user_name(user_id): + global con + + cur = con.cursor() + try: + cur.execute(SELECT_USER_NAME_QUERY, (user_id,)) + return cur.fetchone()[0] + finally: + cur.close() + +def account_ticket(fields): + global resolved, rejected, open_tickets, ticket_count + + ticket_count += 1 + + queue = fields.get('Queue') + if queue and queue.isdigit(): + queue = get_queue_name(queue) + + owner = fields.get('Owner') + if owner and owner.isdigit(): + owner = get_user_name(owner) + + status = fields['Status'] + + if status == 'resolved': + if queue != 'spam' and owner: + if not owner in resolved: + resolved[owner] = {} + if not queue in resolved[owner]: + resolved[owner][queue] = 0 + resolved[owner][queue] += 1 + elif status == 'rejected': + user = get_user_name(fields['LastUpdatedBy']) + if user not in rejected: + rejected[user] = 0 + rejected[user] += 1 + else: + if queue != 'spam' and 'MergedInto' not in fields: + if queue not in open_tickets: + open_tickets[queue] = 0 + open_tickets[queue] += 1 + +def account_tickets(year, month): + global con + + cur = con.cursor() + first_of_month = datetime.datetime(year, month, 1).date() + print cur.mogrify(SELECT_TRANSACTIONS_QUERY, (first_of_month, first_of_month, first_of_month)) + cur.execute(SELECT_TRANSACTIONS_QUERY, (first_of_month, first_of_month, first_of_month)) + + fields = {} + for ticket_id, last_updated_by, current_queue, transaction_created, transaction_field, transaction_new_value in cur: + if ticket_id != fields.get('TicketId'): + if 'TicketId' in fields: + account_ticket(fields) + fields = { 'TicketId': ticket_id, 'LastUpdatedBy': last_updated_by, 'Status': 'new', 'Owner': None, 'Queue': current_queue } + if transaction_field: + fields[transaction_field] = transaction_new_value + if 'TicketId' in fields: + account_ticket(fields) + cur.close() + + print "Ticket count: %d" % ticket_count + print "Stats:" + print_stats() + +def get_queues(resolved): + queues = set() + for user in resolved: + queues.update(resolved[user]) + return queues + +def short_queue_name(queue): + if queue.startswith('help-'): + return queue[-2:] + return 'en' + +def print_stats(): + global resolved, rejected, open_tickets, year, month + + users = resolved.keys() + users.sort() + queues = list(get_queues(resolved)) + queues.sort(key=short_queue_name) + + queue_totals = {} + for queue in queues: + queue_totals[queue] = 0 + + header = (' ' * 10) + '| ' + ' | '.join(map(short_queue_name, queues)) + ' | Total | (Rejected)' + horiz_sep = re.sub(r'\|', '+', re.sub(r'[^|]', '-', header)) + print header + print horiz_sep + for user in users: + d = { 'user': user, 'rejected': rejected.get(user, ''), 'total': sum(resolved[user].values()) } + for queue in queues: + d[queue] = resolved[user].get(queue, '') + queue_totals[queue] += resolved[user].get(queue, 0) + print ('%(user)9s | ' + ' | '.join(['%%(%s)4s' % q for q in queues]) + ' | %(total)5s | %(rejected)9s ') % d + print horiz_sep + d = { 'total': sum(queue_totals.values()), 'rejected': sum(rejected.values()) } + d.update(queue_totals) + print (' Total | ' + ' | '.join(['%%(%s)4s' % q for q in queues]) + ' | %(total)5s | %(rejected)9s ') % d + d = { 'total': sum(open_tickets.values()) } + for queue in queues: + d[queue] = open_tickets.get(queue, '') + print (' (Open) | ' + ' | '.join(['%%(%s)4s' % q for q in queues]) + ' | %(total)5s | ') % d + print '' + print "That's almost %d tickets resolved each day on average." % (sum(queue_totals.values()) / calendar.monthrange(year, month)[1]) + print '' + print 'month,queue,newtickets' + for queue in queues: + print '%04d-%02d,%s,%d' % (year, month, short_queue_name(queue), queue_totals.get(queue, 0) + open_tickets.get(queue, 0)) + +con = None +ticket_count = 0 +resolved = {} +rejected = {} +open_tickets = {} +year = None +month = None + +if __name__ == '__main__': + if len(sys.argv) == 1: + now = datetime.datetime.now() + year, month = now.year, now.month + elif len(sys.argv) == 3: + year, month = int(sys.argv[1]), int(sys.argv[2]) + else: + print >>sys.stderr, "Usage: %s [YEAR MONTH]" % (sys.argv[0]) + sys.exit(1) + con = psycopg2.connect(RT_CONNINFO) + account_tickets(year, month) + con.close()