From: Stephen Soltesz Date: Fri, 27 May 2011 22:40:15 +0000 (+0000) Subject: Added commands for interacting with google's spreadsheets: X-Git-Tag: monitor-3.1-2~1 X-Git-Url: http://git.onelab.eu/?p=monitor.git;a=commitdiff_plain;h=5b75bd95cdacf04311b1aa4e28084eff17faba4d Added commands for interacting with google's spreadsheets: statistics/add-google-record.py statistics/get-records.py Added commands for parsing RT database and generating figure. Added command for dumping monitor db to CSV (dump_db_m3_raw.py) minor fixes --- diff --git a/Monitor.spec b/Monitor.spec index 658c07a..d2970bb 100644 --- a/Monitor.spec +++ b/Monitor.spec @@ -133,7 +133,7 @@ The server side include all python modules and scripts needed to fully operation, track, and interact with any third-party monitoring software, such as Zabbix DB. -####################################### RunlevelAgent +######################################## RunlevelAgent %package runlevelagent summary: the RunlevelAgent that reports node runlevels group: applications/system @@ -168,7 +168,7 @@ install -d $RPM_BUILD_ROOT/%{python_sitearch}/monitor # plc.d scripts install -D -m 644 monitor.functions $RPM_BUILD_ROOT/%{_sysconfdir}/plc.d/monitor.functions install -D -m 755 monitor-server.init $RPM_BUILD_ROOT/%{_sysconfdir}/plc.d/monitor -install -D -m 755 zabbix/monitor-zabbix.init $RPM_BUILD_ROOT/%{_sysconfdir}/plc.d/zabbix +#install -D -m 755 zabbix/monitor-zabbix.init $RPM_BUILD_ROOT/%{_sysconfdir}/plc.d/zabbix install -D -m 755 nagios/monitor-nagios.init $RPM_BUILD_ROOT/%{_sysconfdir}/plc.d/monitor-nagios install -D -m 644 nagios/monitor-nagios.cron $RPM_BUILD_ROOT/%{_sysconfdir}/cron.d/monitor-nagios.cron @@ -233,13 +233,12 @@ rm -rf $RPM_BUILD_ROOT /usr/share/%{name}/RunlevelAgent.py* /usr/share/%{name}/automate-default.sh /usr/share/%{name}/monitor-default.conf -/usr/share/%{name}/monitor-runlevelagent.init /usr/share/%{name}/monitor-server.cron /usr/share/%{name}/monitor-server.init /usr/share/%{name}/monitor.functions /usr/share/%{name}/setup.py* /usr/share/%{name}/threadpool.py* -/usr/share/%{name}/zabbix.spec +#/usr/share/%{name}/zabbix.spec /usr/share/%{name}/bootcd /usr/share/%{name}/commands @@ -259,7 +258,7 @@ rm -rf $RPM_BUILD_ROOT %{_sysconfdir}/cron.d/monitor-server.cron %{_sysconfdir}/plc.d/monitor %{_sysconfdir}/plc.d/monitor.functions -%{_sysconfdir}/plc.d/zabbix +#%{_sysconfdir}/plc.d/zabbix %{_sysconfdir}/httpd/conf.d %{python_sitearch} diff --git a/automate-default.sh b/automate-default.sh index db53fd4..d691c66 100755 --- a/automate-default.sh +++ b/automate-default.sh @@ -27,6 +27,7 @@ API=$(${MONITOR_SCRIPT_ROOT}/tools/testapi.py) if [ "$API" != "ok" ] ; then # NOTE: Do not try to run any commands if the API is obviously broken. echo "API IS DOWN : "`date` + send_mail "API IS DOWN: canceled monitor run for `date`" "have a nice day..." exit 1 fi @@ -91,9 +92,25 @@ ps ax | grep BatchMode | grep -v grep | awk '{print $1}' | xargs -r kill || : ps ax | grep locfg | grep -v grep | awk '{print $1}' | xargs -r kill || : +${MONITOR_SCRIPT_ROOT}/commands/repair.py $DATE || : ${MONITOR_SCRIPT_ROOT}/commands/policy.py $DATE || : curl -s 'http://summer.cs.princeton.edu/status/tabulator.cgi?table=table_nodeview&formatcsv' > /var/lib/monitor/comon/$DATE.comon.csv || : +#${MONITOR_SCRIPT_ROOT}/statistics/add-google-record.py --email email --password password --database MonitorStats --sheet NodeHistory `${MONITOR_SCRIPT_ROOT}/statistics/get-records.py nodes` +#${MONITOR_SCRIPT_ROOT}/statistics/add-google-record.py --email email --password password --database MonitorStats --sheet SiteHistory `${MONITOR_SCRIPT_ROOT}/statistics/get-records.py sites` + cp ${MONITOR_SCRIPT_ROOT}/monitor.log ${MONITOR_ARCHIVE_ROOT}/`date +%F-%H:%M`.monitor.log service plc restart monitor || : rm -f $MONITOR_PID + +D=`date +%F-%H:%M` + +# NOTE: check log for major sections. +wc=`grep -E "^(findbad|findbadpcu|nodebad|pcubad|sitebad|apply-policy)$" ${MONITOR_SCRIPT_ROOT}/monitor.log | wc -l` +if [[ $wc -ge 6 ]] ; then + send_mail "A:finished monitor run for $SD at $D" "Thank you..." +else + send_mail "ERROR finished monitor run for $SD at $D" "Missing some sections: + $(grep -E "findbad|findbadpcu|nodebad|pcubad|sitebad|apply-policy" ${MONITOR_SCRIPT_ROOT}/monitor.log)" +fi + diff --git a/commands/nodebad.py b/commands/nodebad.py index d1b2d35..5fd34ea 100755 --- a/commands/nodebad.py +++ b/commands/nodebad.py @@ -32,6 +32,16 @@ def main2(config): checkAndRecordState(l_nodes, l_plcnodes) +def get_uptime(uptime_str): + up = 0 + if len(uptime_str) > 0: + try: + up = float(uptime_str.split()[0]) + print "uptime: %s" % up + except: + up = 0 + return up + # Node states: def check_node_state(rec, node): @@ -95,9 +105,15 @@ def check_node_state(rec, node): node.last_changed = datetime.now() if node_state == 'BOOT' and node.status != 'online' and node.status != 'good': - print "changed status from %s to online" % node.status - node.status = 'online' - node.last_changed = datetime.now() + old_status = node.status + uptime = get_uptime(rec.uptime) + if uptime > (60*60*24): + node.status = 'good' + node.last_changed = datetime.now() - timedelta(0,uptime) + else: + node.status = 'online' + node.last_changed = datetime.now() + print "changed status from %s to %s" % (old_status, node.status) ################################################################# # Switch temporary hystersis states into their 'firm' states. diff --git a/monitor/common.py b/monitor/common.py index 5cf8151..ae32156 100644 --- a/monitor/common.py +++ b/monitor/common.py @@ -283,13 +283,13 @@ def found_within(recent_actions, action_type, within): return False -class Time: - @classmethod - def dt_to_ts(cls, dt): - t = time.mktime(dt.timetuple()) - return t - - @classmethod - def ts_to_dt(cls, ts): - d = datetime.fromtimestamp(ts) - return d +#class Time: +# @classmethod +# def dt_to_ts(cls, dt): +# t = time.mktime(dt.timetuple()) +# return t +# +# @classmethod +# def ts_to_dt(cls, ts): +# d = datetime.fromtimestamp(ts) +# return d diff --git a/monitor/reboot.py b/monitor/reboot.py index cd49a59..46d667b 100755 --- a/monitor/reboot.py +++ b/monitor/reboot.py @@ -9,7 +9,7 @@ import xml, xmlrpclib import errno, time, traceback import urllib2 import urllib -import threading, popen2 +import threading import array, struct import base64 from subprocess import PIPE, Popen diff --git a/statistics/add-google-record.py b/statistics/add-google-record.py index ae0d313..31f1a89 100755 --- a/statistics/add-google-record.py +++ b/statistics/add-google-record.py @@ -76,9 +76,9 @@ def main(): values=None, valuelist=None, update=None, - email=None, - password=None, - prefix="", + email=None, + password=None, + prefix="", create=False) parser.add_option("", "--email", dest="email", help="") parser.add_option("", "--password", dest="password", help="") @@ -92,9 +92,9 @@ def main(): parser.add_option("", "--prefix", dest="prefix", help="add a prefix to numeric headers") (config, args) = parser.parse_args() - #if len(sys.argv) == 1: - # parser.print_help() - # sys.exit(1) + if len(sys.argv) == 1: + parser.print_help() + sys.exit(1) email = config.email password = config.password @@ -107,10 +107,11 @@ def main(): if config.labels: config.labels = config.labels.split(',') - config.labels = [config.labels[0] ] + [ config.prefix + l for l in config.labels[1:] ] + config.labels = [config.labels[0] ] + [ config.prefix + l for l in config.labels[1:] ] data_list = [] if config.values: + config.values = config.values.replace("+", " ") config.values = config.values.split(',') data_list = [dict(zip(config.labels, config.values))] print data_list diff --git a/statistics/dump_db_m3_raw.py b/statistics/dump_db_m3_raw.py new file mode 100755 index 0000000..477c269 --- /dev/null +++ b/statistics/dump_db_m3_raw.py @@ -0,0 +1,125 @@ +#!/usr/bin/python + +from monitor.database.info.model import * +import sys +from math import * +from monitor.generic import * +from datetime import datetime, timedelta +import time +import string + +def list_to_str(list): + ret = [] + for l in list: + if type(l) == type([]): + ret.append(" ".join([str(i) for i in l])) + else: + s = str(l) + s = s.translate(string.maketrans(",\n\r\"", ";||'")) + ret.append(s) + return ret + +def add_if_not_present(d, add_fields=None): + if type(d) == type({}): + key_list = d.keys() + for k in add_fields.keys(): + if k not in key_list: + d[k] = add_fields[k] + else: + add_if_not_present(d[k], add_fields[k]) + return + +def dict_to_list(d, add_fields=None, ignore_fields=None): + """ return a list of header names from a nested dict + { 'a' : 1, 'b' : { 'c':2, 'd' : 3}} + would return: + [ 'a', 'b_c', 'b_d' ] + """ + k_list = [] + d_list = [] + if add_fields: add_if_not_present(d, add_fields) + for k in d.keys(): + if type(d[k]) == type({}): + (z_kl, z_dl) = dict_to_list(d[k]) + for i,zk in enumerate(map(lambda x: "%s_%s" % (k,x), z_kl)): + if ignore_fields is None or zk not in ignore_fields: + k_list.append(zk) + d_list.append(z_dl[i]) + else: + if ignore_fields is None or k not in ignore_fields: + k_list.append(k) + d_list.append(d[k]) + r = zip(k_list, list_to_str(d_list)) + r.sort(lambda x,y: cmp(x[0], y[0])) + return ([ i[0] for i in r ], [ i[1] for i in r ]) + + +if len(sys.argv) > 1 and sys.argv[1] == "--action": + args = sys.argv[2:] + find_action = True +else: + if len(sys.argv) > 1: + since_time = Time.str_to_dt(sys.argv[1], "%Y-%m-%d") + skip = int(sys.argv[2]) + args = sys.argv[3:] + else: + args = sys.argv[1:] + + find_action = False + +first_time = True +index = 0 +t1=t2=0 +if find_action: + a = ActionRecord.query.all() + print >>sys.stderr, len(a) + for node in a: + + print >>sys.stderr, index, node.hostname, t2-t1 + index += 1 + t1 = time.time() + + d = node.__dict__ + (k,l) = dict_to_list(d) + if first_time: + print "timestamp_unix,%s" % ",".join(k[1:]) + first_time = False + + print "%s,%s" % (Time.dt_to_ts(d['date_created']), ",".join(l[1:])) + t2=time.time() + +else: + ignore_fields = ['plc_node_stats_nodenetwork_ids', 'port_status_806', 'port_status_22', 'port_status_80' ] + add_fields = {'plc_node_stats' : { 'last_boot' : 0, 'last_pcu_confirmation' : 0, 'last_pcu_reboot' : 0, 'last_download' : 0, 'run_level': 0, }} + for node in FindbadNodeRecord.query.all(): + + print >>sys.stderr, index, node.hostname, t2-t1 + index += 1 + t1 = time.time() + if index > skip : + for v in node.versions: + + d = v.__dict__ + (k,l) = dict_to_list(d, add_fields=add_fields, ignore_fields=ignore_fields) + if not first_time: + if cmp(k, k_last) != 0: + print >>sys.stderr, "mismatching key lists" + print >>sys.stderr, k + print >>sys.stderr, k_last + for i in zip(k,k_last): + print >>sys.stderr, i + print >>sys.stderr, set(k) - set(k_last) + print >>sys.stderr, set(k_last) - set(k) + #sys.exit(1) + continue + + if d['timestamp'] > since_time: + if first_time: + print "timestamp_unix,%s" % ",".join(k[1:]) + first_time = False + + + print "%s,%s" % (Time.dt_to_ts(d['timestamp']), ",".join(l[1:])) + + k_last = k + t2=time.time() diff --git a/statistics/dump_subset.py b/statistics/dump_subset.py new file mode 100755 index 0000000..ccb1659 --- /dev/null +++ b/statistics/dump_subset.py @@ -0,0 +1,111 @@ +#!/usr/bin/python + +import sys +import os + +def recent(q, val, length): + return [val] + q[:length-1] + +def add(d, path, v): + if path not in d: + d[path] = [] + d[path] = recent(d[path], v, 6) + #d[path].append(v) + +def stateof(l): + if len(set(l)) == 1: + return l[0] + else: + return "BOOT" + +def in_hard_state(l): + if len(set(l)) == 1: + return True + else: + return False + +def main(): + from optparse import OptionParser + parser = OptionParser() + + parser.set_defaults(database="", + sheet="", + fields="date_checked,timestamp_unix,hostname,uptime,kernel_version,observed_status", + values=None, + valuelist=None, + update=None, + fieldpositions=None, + showfieldpositions=False, + create=False) + parser.add_option("", "--database", dest="database", help="") + parser.add_option("", "--create", dest="create", action="store_true", help="") + parser.add_option("", "--sheet", dest="sheet", help="") + parser.add_option("", "--values", dest="values", help="") + parser.add_option("", "--valuelist", dest="valuelist", help="") + parser.add_option("", "--update", dest="update", help="") + parser.add_option("", "--fields", dest="fields", help="") + parser.add_option("", "--fieldpositions", dest="fieldpositions", help="") + parser.add_option("", "--showfieldpositions", dest="showfieldpositions", action="store_true", help="") + (config, args) = parser.parse_args() + + if config.fields: + config.fields = config.fields.split(',') + + if config.fieldpositions: + config.fieldpositions = [ int(x) for x in config.fieldpositions.split(',') ] + + first = True + #for f in config.fields: + # print "%s,"%f, + #print "" + + while True: + line = sys.stdin.readline() + if not line: + break + line = line.strip() + # NOTE assumes ts are ordered. + #try: + if True: + fields = line.split(',') + if first: + headers = [ f for f in fields ] + #for i,f in enumerate(headers): + # print i, headers[i] + first=False + if config.showfieldpositions: + for f in config.fields: + i = headers.index(f) + print i, + print "" + + + #except: + # print >>sys.stderr, "EXCEPTION:", line + # sys.exit(1) + #for i,f in enumerate(fields): + #print i, headers + #print i, f + # print i, headers[i], f + + for pos,f in enumerate(config.fields): + + if config.fieldpositions: + i = config.fieldpositions[pos] + else: + try: + i = headers.index(f) + except: + print "could not find field: %s" % f + sys.exit(1) + + try: + v = fields[i] + except: + continue + + print "%s," % v, + print "" + +if __name__ == "__main__": + main() diff --git a/statistics/get-records.py b/statistics/get-records.py new file mode 100755 index 0000000..b6a9ecc --- /dev/null +++ b/statistics/get-records.py @@ -0,0 +1,60 @@ +#!/usr/bin/python + +from monitor.database.info.model import * +import time +import sys + +sumdata = {} +sumdata['nodes'] = {} +sumdata['sites'] = {} +sumdata['pcus'] = {} + +def summarize(query, type): + for o in query: + if o.status not in sumdata[type]: + sumdata[type][o.status] = 0 + sumdata[type][o.status] += 1 + +time_str = time.strftime("%m/%d/%y+%H:%M") + +if len(sys.argv) == 1: + print "For use in conjunction with add-google-record.py" + print "Usage: %s " % sys.argv[0] + sys.exit(1) + +elif sys.argv[1] == "sites": + + site_type_list = ['date', 'good', 'offline', 'down', 'online', 'new'] + + for k in site_type_list: + sumdata['sites'][k]=0 + + fbquery = HistorySiteRecord.query.all() + summarize(fbquery, 'sites') + sumdata['sites']['date'] = time_str + for f in sumdata['sites']: + sumdata['sites'][f] = str(sumdata['sites'][f]) + + l = ",".join(site_type_list) + v = ",".join([ sumdata['sites'][k] for k in site_type_list ]) + print "--labels=%s --values=%s" % ( l, v ) + +elif sys.argv[1] == "nodes": + + node_type_list = ['date', 'good', 'offline', 'down', 'online', 'disabled', 'failboot', 'safeboot'] + for k in node_type_list: + sumdata['nodes'][k]=0 + fbquery = HistoryNodeRecord.query.all() + summarize(fbquery, 'nodes') + sumdata['nodes']['date'] = time_str + for f in sumdata['nodes']: + sumdata['nodes'][f] = str(sumdata['nodes'][f]) + + l = ",".join(node_type_list) + v = ",".join([ sumdata['nodes'][k] for k in node_type_list ]) + print "--labels=%s --values=%s" % ( l, v ) + + +#row.content +#row.Push() +#row.Pull() diff --git a/statistics/rt_s1_raw_dump.py b/statistics/rt_s1_raw_dump.py new file mode 100755 index 0000000..da8c1fb --- /dev/null +++ b/statistics/rt_s1_raw_dump.py @@ -0,0 +1,229 @@ +#!/usr/bin/python + +import os, sys, shutil +import MySQLdb +import string + +import re + +import time +from datetime import datetime + +from monitor import config +from monitor import database + +def convert_time(time_str): + if '-' in str: + try: + tup = time.strptime(str, "%Y-%m-%d %H:%M:%S") + except: + tup = time.strptime(str, "%Y-%m-%d-%H:%M") + elif '/' in str: + tup = time.strptime(str, "%m/%d/%Y") + else: + tup = time.strptime(str, "%m/%d/%Y") + d_ret = datetime.fromtimestamp(time.mktime(tup)) + return d_ret + +def open_rt_db(): + + try: + rt_db = MySQLdb.connect(host=config.RT_DB_HOST, + user=config.RT_DB_USER, + passwd=config.RT_DB_PASSWORD, + db=config.RT_DB_NAME) + except Exception, err: + print "Failed to connect to RT database: %s" %err + return -1 + + return rt_db + +def fetch_from_db(db, sql): + try: + # create a 'cursor' (required by MySQLdb) + c = db.cursor() + c.execute(sql) + except Exception, err: + print "Could not execute RT query %s" %err + return -1 + + # fetch all rows (list of lists) + raw = c.fetchall() + return raw + + +def get_rt_tickets(date): + print "open db connection" + db = open_rt_db() + if db == -1: + return "" + + if date is None: + date_select = "" + else: + #date_select = "AND tr.Created >= DATE_SUB(CURDATE(),INTERVAL %s DAY)" % date + date_select = "AND tr.Created >= STR_TO_DATE('%s', '%%Y-%%m-%%d')" % date + + sql = """SELECT tk.id, tk.Queue, tr.Type, tr.Field, tr.OldValue, tr.NewValue, + tr.Created, at.id, at.Subject, at.Content, us.Name + FROM Tickets as tk, Transactions as tr, Users as us + LEFT OUTER JOIN Attachments as at ON tr.id=at.TransactionId + WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>0 AND + us.id=tr.Creator %s""" % date_select + #WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>12506 AND + # WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>40800 AND + #WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>39896 AND tk.id<42241 AND ## (oct15th2008) + #WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>40800 AND ## (1st3months) + #WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>12506 # 12506 jan-1-2006 + + print sql + print "run query" + raw = fetch_from_db(db, sql) + if raw == -1: + return raw + + tickets = {} + subject_map = {} + def parse_ticket(x): + ticket_id = int(x[0]) + queue = int(x[1]) + trtype = str(x[2]) + field = x[3] + oldvalue = x[4] + newvalue = x[5] + datecreated = x[6] # already a datetime object + attachmentid = x[7] + subject = x[8] + content = x[9] + creator = x[10] + + if ticket_id not in tickets: + print "found new ticket_id", ticket_id + tickets[ticket_id] = {'queue' : queue, + 'transactions' : [] } + + if subject != "": + subject_map[ticket_id] = subject + elif ticket_id in subject_map: + subject = subject_map[ticket_id] + else: + # subject == "" and no record in subject_map yet + # should probably put on a queue to be processed later. + print "no subject for %s" % ticket_id + + transaction = { + 'type' : trtype, + 'field' : field, + 'oldvalue' : oldvalue, + 'newvalue' : newvalue, + 'datecreated' : datecreated, + 'attachmentid' : attachmentid, + 'creator' : creator, + 'subject' : subject, + 'content' : content, + } + tickets[ticket_id]['transactions'].append(transaction) + + + print "sort data" + list = map(parse_ticket, raw) + + db.close() + + + return tickets + + +# flow chart: +# classify: +# for each ticket +# classify into category +# remove from ticket set, add to classified-set +# +# add new search patterns, +# re-run classify algorithm + +re_map = [ + #('mom', {'pattern' : '.*pl_mom.*'}), + #('technical-support', {'pattern' : '.*PlanetLab node.* down'}), + #('technical-support', {'pattern' : 'Node .* was stopped by'}), # and opened + #('technical-support', {'pattern' : 'bootcd|BootCD|bootCD|boot cd|boot CD|booting'}), + #('technical-support', {'pattern' : '.* failed to authenticate'}), + #('technical-support', {'pattern' : '.* fails to boot'}), + #('technical-support', {'pattern' : '.* fail.* to boot'}), + #('technical-support', {'pattern' : '.* failed to authenticate'}), + #('technical-support', {'pattern' : 'curl (60)|.* CA certificates.*|peer certificate.*authenticated'}), + #('technical-support', {'pattern' : '(usb|USB).*(key|Disk|stick|boot|help|problem|trouble)'}), + #('complaint', {'pattern' : '.*omplaint|.*attack'}), + #('complaint', {'pattern' : '.* stop .*'}), # and subject + #('spam', {}),j + #('user-support', {'pattern' : '(R|r)egistration|(R|r)egister'}), + #('user-support', {'pattern' : 'password reset|reset password'}), + ('user-support', {'pattern' : 'New PI account registration from'}), + #('other', {}), +] + +def sort_tickets(tickets, re_map): + + ticket_count = len(tickets.keys()) + marked_subject = 0 + marked_content = 0 + for ticket_id in sorted(tickets.keys()): + for i,(name, pattern) in enumerate(re_map): + if 'compile' not in pattern: + pattern['compile'] = re.compile(pattern['pattern']) + pat = pattern['compile'] + for transaction in tickets[ticket_id]['transactions']: + + try: + if transaction['subject'] and re.match(pat, transaction['subject']): + print "ticket %s matches pattern %s: %s" % (ticket_id, + pattern['pattern'], transaction['subject']) + marked_subject += 1 + break + if transaction['content'] and re.match(pat, transaction['content']): + print "ticket %s matches pattern %s: %s" % (ticket_id, + pattern['pattern'], transaction['subject']) + #if transaction['subject'] == "": + # print transaction + marked_content += 1 + break + except: + import traceback + print traceback.print_exc() + print transaction + print ticket_id + print pattern + sys.exit(1) + + print ticket_count + print marked_subject + print marked_content + print ticket_count - marked_content - marked_content + +def main(): + from optparse import OptionParser + parser = OptionParser() + + parser.set_defaults(runsql=False,date=None) + + parser.add_option("", "--runsql", dest="runsql", action="store_true", + help="Whether to collect data from the MySQL server before "+ + "caching it, or to just use the previously collected data.") + parser.add_option("", "--date", dest="date", + help="Query all transactions after the given date.") + + (config, args) = parser.parse_args() + if len(sys.argv) == 1: + parser.print_help() + sys.exit(1) + + if config.runsql: + tickets = get_rt_tickets(config.date) + database.dbDump("survey_tickets", tickets) + else: + print "loading" + tickets = database.dbLoad("survey_tickets") + +if __name__ == '__main__': + main() diff --git a/statistics/rt_s2_parse_raw.py b/statistics/rt_s2_parse_raw.py new file mode 100755 index 0000000..006b17c --- /dev/null +++ b/statistics/rt_s2_parse_raw.py @@ -0,0 +1,54 @@ +#!/usr/bin/python + +from datetime import datetime + +from monitor import config +from monitor import database +from monitor.common import Time +import sys + +def main(): + tickets = database.dbLoad("survey_tickets") + +#if True: + #f = open('rt_monitor_data.csv','w') + queue = int(sys.argv[1]) + exclude = ['monitor'] + + f = sys.stdout + print >>f, "ticket_id,s1,s2,start,lastreply,replies,complete,creator" + for t in sorted(tickets.keys()): + ticket_id = 0 + start = datetime(2004,1,1) + lastreply = datetime.now() + resolved = 0 + complete = 0 + replies = 1 + creator = '' + if tickets[t]['queue'] != queue: continue + for tr in tickets[t]['transactions']: + # create - ticketid,creator, datecreated, + # correspond - creator, datecreated, content + # status - newvalue = resolved + if tr['type'] == 'Create': + start = tr['datecreated'] + creator = tr['creator'] + if complete==0: complete = 1 + elif tr['type'] == 'Correspond': + if tr['creator'] not in exclude: + lastreply = tr['datecreated'] + replies += 1 + if complete == 1: complete = 2 + + elif tr['type'] == 'Status' and tr['newvalue'] == 'resolved': + resolved = 1 + if complete == 2: complete = 3 + + if replies < 1100: + if complete in [2,3]: complete = 1 + else: complete = 0 + print >>f, "%s,%s,%s,%s,%s,%s,%s,%s" % (t, start.strftime('%Y-%m-%d'), lastreply.strftime('%Y-%m-%d'), Time.dt_to_ts(start), Time.dt_to_ts(lastreply), replies, complete, creator) + f.close() + +if __name__ == '__main__': + main() diff --git a/statistics/rt_s3_overhead.r b/statistics/rt_s3_overhead.r new file mode 100644 index 0000000..682b6a1 --- /dev/null +++ b/statistics/rt_s3_overhead.r @@ -0,0 +1,193 @@ +source("functions.r"); + + + +median_time_to_resolve_window <- function (t, tg, window) +{ + hbreaks<-tg$week_ts + + xx<-NULL; + yy<-NULL; + yy_sd_high<-NULL; + yy_sd_low<-NULL; + date_index <- NULL; + q_list <- NULL; + + x<-seq(-20,20,0.01) + + for ( i in seq(1,length(hbreaks)-window-1) ) + { + print (sprintf("round %s of %s", i, length(hbreaks)-window-1)) + # get range from t + t_sub <- t[which(t$start > hbreaks[i] & t$start<= hbreaks[i+window]),] + if ( length(t_sub$start) <= 1 ) { next } + # take log, then sn.mle -> h + d <- (t_sub$lastreply - t_sub$start)/(60*60) # hours + d <- log(d) # log(hours) + # sn.mle + print (sprintf("length: %s", length(d))) + q<-quantile(d) + print(q) + + date_index <- c(date_index, round(i+window/2)) + + xx<- c(xx, hbreaks[round(i+window/2)]) + q_list <- rbind(q_list, q) + + } + return (cbind(xx,q_list)) +} + +available_nodes <- function (ns, from, to, type, fmt="%b") +{ + # find 'type' range of days + dates <-seq(as.Date(from), as.Date(to), type) + months <- format(dates, fmt) + hbreaks<-unclass(as.POSIXct(dates)) + + xx<-NULL; + yy<-NULL; + + for ( i in seq(1,length(hbreaks)-1) ) + { + # get range from ns + ns_sub <- ns[which(ns$date > hbreaks[i] & ns$date <= hbreaks[i+1] & ns$status == 'BOOT'),] + nodes <- length(ns_sub$date) + + xx<- c(xx, hbreaks[i]) + yy<- c(yy, nodes) + + } + m<- months[1:length(months)-1] + return (rbind(xx,yy,m)) +} + + +open_tickets <- function (t, tg) +{ + xx<-NULL; + yy<-NULL; + + hbreaks<-tg$day_ts + + for ( i in seq(1,length(hbreaks)-1) ) + { + # identify any tickets with a start time in range, lastreply in range + # or where both start is less and lastreply is greater than the range + t_sub <- t[which( (t$start < hbreaks[i] & t$lastreply > hbreaks[i+1]) | + (t$start > hbreaks[i] & t$start <= hbreaks[i+1]) | + (t$lastreply > hbreaks[i] & t$lastreply <= hbreaks[i+1]) ),] + tickets <- length(t_sub$start) + + xx<- c(xx, hbreaks[i]) + yy<- c(yy, tickets) + } + return (rbind(xx,yy)) +} + +online_nodes <- function (fb) +{ + breaks <- unique(fb$timestamp) + n<-NULL + o<-NULL + x<-NULL + for (i in seq(1,length(breaks)) ) + { + ts <- breaks[i] + sub <- fb[which(fb$timestamp == ts),] + node_count <- length(unique(sub$hostname)) + online_count <- length(unique(sub$hostname[which(sub$state=='BOOT')])) + x<-c(x,ts) + n<-c(n,node_count) + o<-c(o,online_count) + } + print(length(x)) + print(length(n)) + print(length(o)) + return (rbind(x,n,o)) +} + +##### + +# system("rt_s1_raw_dump.py --runsql"); +# system("rt_s2_parse_raw.py 3 > rt_data.csv"); +# t <- read.csv('rt_data_2004-2011.csv', sep=',', header=TRUE) +#t <- read.csv(, sep=',', header=TRUE) + +draw_rt_data <- function (input_filename, output_filename, start_date, end_date, draw=TRUE, one=FALSE) +{ + t <- read.csv(input_filename, sep=',', header=TRUE) + t2 <- t[which(t$complete == 1),] + + tg <- time_graph_setup(start_date, end_date) + ot <- open_tickets(t2, tg) + + if ( draw == TRUE ) { + start_image(output_filename, width=600, height=400) + } + if ( one == TRUE ) + { + par(mfrow=c(1,1)) + par(mai=c(0.8,1,0.4,0.1)) + } else { + par(mfrow=c(2,1)) + par(mai=c(0,1,0.3,0.1)) + } + + x1<-as.numeric(ot[1,]) + y1<-as.numeric(ot[2,]) + + a_ot<-lowess_smooth(x1, y1) + + plot(x1, y1, col='grey80', type='l', axes=F, + ylab="a) Open Tickets (tickets/day)", xlab="Date", + ylim=c(0,120)) # , ylim=c(0,260)) + lines(a_ot$x, round(a_ot$y), col='black') + + axis(2, las=1) + if ( one == TRUE ) { + axis(1, labels=tg$month_str, at=tg$month_ts, cex.axis=0.7) + axis(1, labels=tg$year_str, at=tg$year_ts, cex.axis=0.7, line=1, lwd=0) + } + + + abline(h=15, lty=3, col='grey80') + abline(h=25, lty=3, col='grey80') + abline(h=40, lty=3, col='grey80') + + plc_releases(120) + if ( one == FALSE ) + { + par(mai=c(1,1,0.1,0.1)) + for ( s in c(5) ) + { + d <- median_time_to_resolve_window(t2, tg, s) # "2004/1/1", "2011/1/28", s, "%b") + plot(d[,1], exp(as.numeric(d[,5]))/24, type='l', lty=1, xlab="", + axes=F, ylim=c(0.01, 15), ylab="b) Resolution Time by", col='black', + xlim=c(min(x1), max(x1))) + mtext("Quartile (days)", 2, 2) + lines(d[,1], exp(as.numeric(d[,4]))/24, lty=1, col='grey50') + lines(d[,1], exp(as.numeric(d[,3]))/24, lty=1, col='grey75') + axis(1, labels=tg$month_str, at=tg$month_ts, cex.axis=0.7) + axis(1, labels=tg$year_str, at=tg$year_ts, cex.axis=0.7, line=1, lwd=0) + axis(2, labels=c(0,1,4,7,14), at=c(0,1,4,7,14), las=1) + m<-round(max(exp(as.numeric(d[,4]))/24), 2) + } + + abline(h=1, lty=3, col='grey80') + abline(h=4, lty=3, col='grey80') + abline(h=7, lty=3, col='grey80') + + planetlab_releases(15) + } + + if ( draw == TRUE ) { + end_image() + } +} + +#system("./rt_s2_parse_raw.py 3 > rt_data_2004-2011.csv"); +draw_rt_data('rt_data_2004-2011.csv', "rt_operator_support_2004-2011.png", "2004/1/1", "2011/6/1", TRUE, TRUE) +#draw_rt_data('rt_data_monitor_2004-2011.csv',"rt_operator_monitor_2004-2011.png", "2004/1/1", "2011/4/1") + +#draw_rt_data('short_support_20110101.csv',"rt_short_2011.png", "2010/11/1", "2011/4/1", FALSE)