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
63 FROM Tickets as tk, Transactions as tr
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>10000 """
68 raw = fetch_from_db(db, sql)
81 datecreated = x[6] # already a datetime object
86 if ticket_id not in tickets:
87 print "found new ticket_id", ticket_id
88 tickets[ticket_id] = {'queue' : queue,
92 subject_map[ticket_id] = subject
93 elif ticket_id in subject_map:
94 subject = subject_map[ticket_id]
96 # subject == "" and no record in subject_map yet
97 # should probably put on a queue to be processed later.
98 print "no subject for %s" % ticket_id
103 'oldvalue' : oldvalue,
104 'newvalue' : newvalue,
105 'datecreated' : datecreated,
106 'attachmentid' : attachmentid,
110 tickets[ticket_id]['transactions'].append(transaction)
114 list = map(parse_ticket, raw)
116 # map(lambda x: { "email":str(x[4]), "lastupdated":str(x[5]), "owner":str(x[7]), }, raw)
127 # classify into category
128 # remove from ticket set, add to classified-set
130 # add new search patterns,
131 # re-run classify algorithm
134 #('mom', {'pattern' : '.*pl_mom.*'}),
135 #('technical-support', {'pattern' : '.*PlanetLab node.* down'}),
136 #('technical-support', {'pattern' : 'Node .* was stopped by'}), # and opened
137 #('technical-support', {'pattern' : 'bootcd|BootCD|bootCD|boot cd|boot CD|booting'}),
138 #('technical-support', {'pattern' : '.* failed to authenticate'}),
139 #('technical-support', {'pattern' : '.* fails to boot'}),
140 #('technical-support', {'pattern' : '.* fail.* to boot'}),
141 #('technical-support', {'pattern' : '.* failed to authenticate'}),
142 #('technical-support', {'pattern' : 'curl (60)|.* CA certificates.*|peer certificate.*authenticated'}),
143 #('technical-support', {'pattern' : '(usb|USB).*(key|Disk|stick|boot|help|problem|trouble)'}),
144 #('complaint', {'pattern' : '.*omplaint|.*attack'}),
145 #('complaint', {'pattern' : '.* stop .*'}), # and subject
147 #('user-support', {'pattern' : '(R|r)egistration|(R|r)egister'}),
148 #('user-support', {'pattern' : 'password reset|reset password'}),
149 ('user-support', {'pattern' : 'New PI account registration from'}),
153 def sort_tickets(tickets, re_map):
155 ticket_count = len(tickets.keys())
158 for ticket_id in sorted(tickets.keys()):
159 for i,(name, pattern) in enumerate(re_map):
160 if 'compile' not in pattern:
161 pattern['compile'] = re.compile(pattern['pattern'])
162 pat = pattern['compile']
163 for transaction in tickets[ticket_id]['transactions']:
166 if transaction['subject'] and re.match(pat, transaction['subject']):
167 print "ticket %s matches pattern %s: %s" % (ticket_id,
168 pattern['pattern'], transaction['subject'])
171 if transaction['content'] and re.match(pat, transaction['content']):
172 print "ticket %s matches pattern %s: %s" % (ticket_id,
173 pattern['pattern'], transaction['subject'])
174 #if transaction['subject'] == "":
180 print traceback.print_exc()
189 print ticket_count - marked_content - marked_content
192 from optparse import OptionParser
193 parser = OptionParser()
195 parser.set_defaults(runsql=False,)
197 parser.add_option("", "--runsql", dest="runsql", action="store_true",
198 help="Whether to collect data from the MySQL server before "+
199 "caching it, or to just use the previously collected data.")
201 (config, args) = parser.parse_args()
202 if len(sys.argv) == 1:
206 for i,(name, pattern) in enumerate(re_map):
210 tickets = get_rt_tickets()
211 database.dbDump("survey_tickets", tickets)
214 tickets = database.dbLoad("survey_tickets")
215 print tickets[42171]['transactions'][0]
217 sort_tickets(tickets, re_map)
220 # scan for known keywords and sort into classes
221 # record assigned class
223 # review all tickets that remain
225 if __name__ == '__main__':