10 from datetime import datetime
12 from monitor import config
13 from monitor import database
15 def convert_time(time_str):
18 tup = time.strptime(str, "%Y-%m-%d %H:%M:%S")
20 tup = time.strptime(str, "%Y-%m-%d-%H:%M")
22 tup = time.strptime(str, "%m/%d/%Y")
24 tup = time.strptime(str, "%m/%d/%Y")
25 d_ret = datetime.fromtimestamp(time.mktime(tup))
31 rt_db = MySQLdb.connect(host=config.RT_DB_HOST,
32 user=config.RT_DB_USER,
33 passwd=config.RT_DB_PASSWORD,
35 except Exception, err:
36 print "Failed to connect to RT database: %s" %err
41 def fetch_from_db(db, sql):
43 # create a 'cursor' (required by MySQLdb)
46 except Exception, err:
47 print "Could not execute RT query %s" %err
50 # fetch all rows (list of lists)
55 def get_rt_tickets(date):
56 print "open db connection"
64 #date_select = "AND tr.Created >= DATE_SUB(CURDATE(),INTERVAL %s DAY)" % date
65 date_select = "AND tr.Created >= STR_TO_DATE('%s', '%%Y-%%m-%%d')" % date
67 sql = """SELECT tk.id, tk.Queue, tr.Type, tr.Field, tr.OldValue, tr.NewValue,
68 tr.Created, at.id, at.Subject, at.Content, us.Name
69 FROM Tickets as tk, Transactions as tr, Users as us
70 LEFT OUTER JOIN Attachments as at ON tr.id=at.TransactionId
71 WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>0 AND
72 us.id=tr.Creator %s""" % date_select
73 #WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>12506 AND
74 # WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>40800 AND
75 #WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>39896 AND tk.id<42241 AND ## (oct15th2008)
76 #WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>40800 AND ## (1st3months)
77 #WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>12506 # 12506 jan-1-2006
81 raw = fetch_from_db(db, sql)
94 datecreated = x[6] # already a datetime object
100 if ticket_id not in tickets:
101 print "found new ticket_id", ticket_id
102 tickets[ticket_id] = {'queue' : queue,
103 'transactions' : [] }
106 subject_map[ticket_id] = subject
107 elif ticket_id in subject_map:
108 subject = subject_map[ticket_id]
110 # subject == "" and no record in subject_map yet
111 # should probably put on a queue to be processed later.
112 print "no subject for %s" % ticket_id
117 'oldvalue' : oldvalue,
118 'newvalue' : newvalue,
119 'datecreated' : datecreated,
120 'attachmentid' : attachmentid,
125 tickets[ticket_id]['transactions'].append(transaction)
129 list = map(parse_ticket, raw)
140 # classify into category
141 # remove from ticket set, add to classified-set
143 # add new search patterns,
144 # re-run classify algorithm
147 #('mom', {'pattern' : '.*pl_mom.*'}),
148 #('technical-support', {'pattern' : '.*PlanetLab node.* down'}),
149 #('technical-support', {'pattern' : 'Node .* was stopped by'}), # and opened
150 #('technical-support', {'pattern' : 'bootcd|BootCD|bootCD|boot cd|boot CD|booting'}),
151 #('technical-support', {'pattern' : '.* failed to authenticate'}),
152 #('technical-support', {'pattern' : '.* fails to boot'}),
153 #('technical-support', {'pattern' : '.* fail.* to boot'}),
154 #('technical-support', {'pattern' : '.* failed to authenticate'}),
155 #('technical-support', {'pattern' : 'curl (60)|.* CA certificates.*|peer certificate.*authenticated'}),
156 #('technical-support', {'pattern' : '(usb|USB).*(key|Disk|stick|boot|help|problem|trouble)'}),
157 #('complaint', {'pattern' : '.*omplaint|.*attack'}),
158 #('complaint', {'pattern' : '.* stop .*'}), # and subject
160 #('user-support', {'pattern' : '(R|r)egistration|(R|r)egister'}),
161 #('user-support', {'pattern' : 'password reset|reset password'}),
162 ('user-support', {'pattern' : 'New PI account registration from'}),
166 def sort_tickets(tickets, re_map):
168 ticket_count = len(tickets.keys())
171 for ticket_id in sorted(tickets.keys()):
172 for i,(name, pattern) in enumerate(re_map):
173 if 'compile' not in pattern:
174 pattern['compile'] = re.compile(pattern['pattern'])
175 pat = pattern['compile']
176 for transaction in tickets[ticket_id]['transactions']:
179 if transaction['subject'] and re.match(pat, transaction['subject']):
180 print "ticket %s matches pattern %s: %s" % (ticket_id,
181 pattern['pattern'], transaction['subject'])
184 if transaction['content'] and re.match(pat, transaction['content']):
185 print "ticket %s matches pattern %s: %s" % (ticket_id,
186 pattern['pattern'], transaction['subject'])
187 #if transaction['subject'] == "":
193 print traceback.print_exc()
202 print ticket_count - marked_content - marked_content
205 from optparse import OptionParser
206 parser = OptionParser()
208 parser.set_defaults(runsql=False,date=None)
210 parser.add_option("", "--runsql", dest="runsql", action="store_true",
211 help="Whether to collect data from the MySQL server before "+
212 "caching it, or to just use the previously collected data.")
213 parser.add_option("", "--date", dest="date",
214 help="Query all transactions after the given date.")
216 (config, args) = parser.parse_args()
217 if len(sys.argv) == 1:
222 tickets = get_rt_tickets(config.date)
223 database.dbDump("survey_tickets", tickets)
226 tickets = database.dbLoad("survey_tickets")
228 if __name__ == '__main__':