commit f6053d050e466d39c61383960edd8ab0cb27f372 Author: Lunar <lunar@torproject.org> Date: Tue Mar 25 17:21:48 2014 +0100 Add first version of a script to extract response time --- response-time/response_time.py | 77 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 77 insertions(+) diff --git a/response-time/response_time.py b/response-time/response_time.py new file mode 100755 index 0000000..0b7d1b9 --- /dev/null +++ b/response-time/response_time.py @@ -0,0 +1,77 @@ +#!/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 +from datetime import datetime, timedelta +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, transactions.created, transactions.type, 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' + ORDER BY tickets.id, transactions.id; +""" + +BUCKET_SIZE = 4 # hours + +def account_tickets(con, year, month): + cur = con.cursor() + first_of_month = datetime(year, month, 1).date() + print cur.mogrify(SELECT_TRANSACTIONS_QUERY, (first_of_month, first_of_month)) + cur.execute(SELECT_TRANSACTIONS_QUERY, (first_of_month, first_of_month)) + + open_tickets = {} # ticket_id -> time + response_times = {} + fields = {} + for ticket_id, transaction_created, transaction_type, transaction_new_value in cur: + if transaction_type not in ('Create', 'Status'): + continue + status = transaction_new_value + last_update = transaction_created + print "%d %s %s" % (ticket_id, status, last_update) + if status == 'resolved' or status == 'rejected': + if ticket_id in open_tickets: + response_time = last_update - open_tickets[ticket_id] + bucket = int(response_time.total_seconds() / (BUCKET_SIZE * 60 * 60)) + response_times[bucket] = 1 + response_times.get(bucket, 0) + print "%d anwsered in %s" % (ticket_id, response_time) + del open_tickets[ticket_id] + else: + print "Don't know when %d was opened." % (ticket_id) + else: + if ticket_id not in open_tickets: + open_tickets[ticket_id] = last_update + cur.close() + print '' + buckets = response_times.keys() + buckets.sort() + for bucket in buckets: + print "%3d hours - %3d hours: %d" % (bucket * BUCKET_SIZE, (bucket + 1) * BUCKET_SIZE, response_times[bucket]) + +if __name__ == '__main__': + if len(sys.argv) == 1: + now = 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(con, year, month) + con.close()