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)
56 print "open db connection"
61 sql = """SELECT tk.id, tk.Queue, tr.Type, tr.Field, tr.OldValue, tr.NewValue,
62 tr.Created, at.id, at.Subject, at.Content, us.Name
63 FROM Tickets as tk, Transactions as tr, Users as us
64 LEFT OUTER JOIN Attachments as at ON tr.id=at.TransactionId
65 WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>0 AND
67 #WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>12506 AND
68 # WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>40800 AND
69 #WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>39896 AND tk.id<42241 AND ## (oct15th2008)
70 #WHERE (tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>40800 AND ## (1st3months)
71 #WHERE (tk.Queue=3 OR tk.Queue=22) AND tk.id=tr.ObjectId AND tk.id>12506 # 12506 jan-1-2006
74 raw = fetch_from_db(db, sql)
87 datecreated = x[6] # already a datetime object
93 if ticket_id not in tickets:
94 print "found new ticket_id", ticket_id
95 tickets[ticket_id] = {'queue' : queue,
99 subject_map[ticket_id] = subject
100 elif ticket_id in subject_map:
101 subject = subject_map[ticket_id]
103 # subject == "" and no record in subject_map yet
104 # should probably put on a queue to be processed later.
105 print "no subject for %s" % ticket_id
110 'oldvalue' : oldvalue,
111 'newvalue' : newvalue,
112 'datecreated' : datecreated,
113 'attachmentid' : attachmentid,
118 tickets[ticket_id]['transactions'].append(transaction)
122 list = map(parse_ticket, raw)
133 # classify into category
134 # remove from ticket set, add to classified-set
136 # add new search patterns,
137 # re-run classify algorithm
140 #('mom', {'pattern' : '.*pl_mom.*'}),
141 #('technical-support', {'pattern' : '.*PlanetLab node.* down'}),
142 #('technical-support', {'pattern' : 'Node .* was stopped by'}), # and opened
143 #('technical-support', {'pattern' : 'bootcd|BootCD|bootCD|boot cd|boot CD|booting'}),
144 #('technical-support', {'pattern' : '.* failed to authenticate'}),
145 #('technical-support', {'pattern' : '.* fails to boot'}),
146 #('technical-support', {'pattern' : '.* fail.* to boot'}),
147 #('technical-support', {'pattern' : '.* failed to authenticate'}),
148 #('technical-support', {'pattern' : 'curl (60)|.* CA certificates.*|peer certificate.*authenticated'}),
149 #('technical-support', {'pattern' : '(usb|USB).*(key|Disk|stick|boot|help|problem|trouble)'}),
150 #('complaint', {'pattern' : '.*omplaint|.*attack'}),
151 #('complaint', {'pattern' : '.* stop .*'}), # and subject
153 #('user-support', {'pattern' : '(R|r)egistration|(R|r)egister'}),
154 #('user-support', {'pattern' : 'password reset|reset password'}),
155 ('user-support', {'pattern' : 'New PI account registration from'}),
159 def sort_tickets(tickets, re_map):
161 ticket_count = len(tickets.keys())
164 for ticket_id in sorted(tickets.keys()):
165 for i,(name, pattern) in enumerate(re_map):
166 if 'compile' not in pattern:
167 pattern['compile'] = re.compile(pattern['pattern'])
168 pat = pattern['compile']
169 for transaction in tickets[ticket_id]['transactions']:
172 if transaction['subject'] and re.match(pat, transaction['subject']):
173 print "ticket %s matches pattern %s: %s" % (ticket_id,
174 pattern['pattern'], transaction['subject'])
177 if transaction['content'] and re.match(pat, transaction['content']):
178 print "ticket %s matches pattern %s: %s" % (ticket_id,
179 pattern['pattern'], transaction['subject'])
180 #if transaction['subject'] == "":
186 print traceback.print_exc()
195 print ticket_count - marked_content - marked_content
198 from optparse import OptionParser
199 parser = OptionParser()
201 parser.set_defaults(runsql=False,)
203 parser.add_option("", "--runsql", dest="runsql", action="store_true",
204 help="Whether to collect data from the MySQL server before "+
205 "caching it, or to just use the previously collected data.")
207 (config, args) = parser.parse_args()
208 if len(sys.argv) == 1:
212 #for i,(name, pattern) in enumerate(re_map):
216 tickets = get_rt_tickets()
217 database.dbDump("survey_tickets", tickets)
220 tickets = database.dbLoad("survey_tickets")
221 #print tickets[42171]['transactions'][0]
223 #sort_tickets(tickets, re_map)
226 # scan for known keywords and sort into classes
227 # record assigned class
229 # review all tickets that remain
231 if __name__ == '__main__':