12 from threading import *
14 # TODO: merge the RT mailer from mailer.py into this file.
16 # RT database access constants file
17 RT_DB_CONSTANTS_PATH='rt_db'
20 logger = logging.getLogger("monitor")
22 # seconds between ticket update
25 def stripQuotes( str ):
29 if str[len(str)-1] in quotes:
34 def readConstantsFile( file_path ):
36 read a file consisting of lines of
40 and return a dictionary of the values.
42 blank lines, and lines starting with # (comments) are skipped
48 input_file= file(file_path,"r")
52 for line in input_file:
55 line= string.strip(line)
59 parts= string.split(line,"=",)
63 contents[parts[0]]= stripQuotes(parts[1])
71 # read plc database passwords and connect
72 rt_db_constants= readConstantsFile(RT_DB_CONSTANTS_PATH)
73 if rt_db_constants is None:
74 print "Unable to read database access constants from %s" % \
79 rt_db = MySQLdb.connect(host=rt_db_constants['RT_DB_HOST'],
80 user=rt_db_constants['RT_DB_USER'],
81 passwd=rt_db_constants['RT_DB_PASSWORD'],
82 db=rt_db_constants['RT_DB_NAME'])
83 except Exception, err:
84 print "Failed to connect to RT database: %s" %err
91 def fetch_from_db(db, sql):
93 # create a 'cursor' (required by MySQLdb)
96 except Exception, err:
97 print "Could not execute RT query %s" %err
100 # fetch all rows (list of lists)
109 # sql = """SELECT distinct Tk.id, Tk.Status, Tk.Subject
111 # JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId
112 # JOIN Attachments AS At ON Tr.id=At.TransactionID
113 # WHERE (At.Content LIKE '%%%s%%' OR
114 # At.Subject LIKE '%%%s%%') AND
115 # (Tk.Status = 'new' OR Tk.Status = 'open') AND
116 # Tk.Queue = 3 OR Tk.Queue = 19
117 # ORDER BY Tk.Status, Tk.LastUpdated DESC""" \
118 # % (hostname,hostname)
119 # sql = """SELECT distinct Tk.id, Tk.Status, Tk.Subject
121 # JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId
122 # JOIN Attachments AS At ON Tr.id=At.TransactionID
123 # WHERE (At.Content LIKE '%%%s%%' OR
124 # At.Subject LIKE '%%%s%%') AND
125 # (Tk.Status = 'new' OR Tk.Status = 'open')
126 # ORDER BY Tk.Status, Tk.LastUpdated DESC""" \
127 # % (hostname,hostname)
129 # Queue == 10 is the spam Queue in RT.
130 # SELECT Tk.* FROM Tickets AS Tk, Attachments AS At JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId WHERE Tk.Queue != 10 AND Tk.id > 10000 AND Tr.id=At.TransactionID AND Tk.Status = 'open' ;
133 sql = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content
134 FROM Tickets AS Tk, Attachments AS At
135 JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId
136 WHERE Tk.Queue != 10 AND Tk.id > 10000 AND
137 Tr.id=At.TransactionID AND Tk.Status = 'open'"""
138 #Tr.id=At.TransactionID AND (Tk.Status = 'new' OR Tk.Status = 'open')"""
139 #sqlall = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content
140 #FROM Tickets AS Tk, Attachments AS At
141 #JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId
142 #WHERE Tk.Queue != 10 AND Tk.id > 10000 AND
143 #Tr.id=At.TransactionID AND ( Tk.Status = 'open' OR
144 #Tk.Status = 'new') """
145 sqlall = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content, Us.EmailAddress, Tk.LastUpdated FROM Tickets AS Tk, Attachments AS At, Users as Us JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId WHERE (Tk.Queue=3 OR Tk.Queue=22) AND Tk.id > 10000 AND Tr.id=At.TransactionID AND ( Tk.Status = 'open' OR Tk.Status = 'new') AND Us.id=Tk.LastUpdatedBy """
148 raw = fetch_from_db(db, sql)
151 tickets = map(lambda x: {"ticket_id":str(x[0]),
154 "content":str(x[3])},
157 raw = fetch_from_db(db,sqlall)
160 tickets_all = map(lambda x: {"ticket_id":str(x[0]),
165 "lastupdated":str(x[5]),
172 for t in tickets_all:
173 idTickets[t['ticket_id']] = t
174 soltesz.dbDump("idTickets", idTickets)
178 def is_host_in_rt_tickets(host, ticket_blacklist, ad_rt_tickets):
179 # ad_rt_tickets is an array of dicts, defined above.
180 if len(ad_rt_tickets) == 0:
183 d_ticket = ad_rt_tickets[0]
184 if not ('ticket_id' in d_ticket and 'status' in d_ticket and
185 'subj' in d_ticket and 'content' in d_ticket):
186 logger.debug("RT_tickets array has wrong fields!!!")
189 #logger.debug("Searching all tickets for %s" % host)
190 def search_tickets(host, ad_rt_tickets):
191 # compile once for more efficiency
192 re_host = re.compile(host)
193 for x in ad_rt_tickets:
194 if re_host.search(x['subj'], re.MULTILINE|re.IGNORECASE) or \
195 re_host.search(x['content'], re.MULTILINE|re.IGNORECASE):
196 logger.debug("\t ticket %s has %s" % (x['ticket_id'], host))
197 print "\t ticket %s has %s" % (x['ticket_id'], host)
198 if x['ticket_id'] in ticket_blacklist:
202 print "\t noticket -- has %s" % host
203 #logger.debug("\t noticket -- has %s" % host)
206 # This search, while O(tickets), takes less than a millisecond, 05-25-07
207 #t = soltesz.MyTimer()
208 ret = search_tickets(host, ad_rt_tickets)
215 Finds tickets associated with hostnames.
216 The idea is if you give it an array of host names,
217 presumeably from comon's list of bad nodes, it starts
218 a few threads to query RT. RT takes a while to return.
220 This is turning into a reinvention of DB design, which I dont believe in.
221 In an effort to keep things minimal, here's the basic algo:
223 Give list of hostnames to RT()
224 Finds tickets associate with new hostnames (not in dict(tickets)).
225 Remove nodes that have come backup. Don't care of ticket is closed after first query.
226 Another thread refresh tickets of nodes already in dict and remove nodes that have come up.
229 def __init__(self, dbTickets, q_toRT, q_fromRT, l_ticket_blacklist, target = None):
230 # Time of last update of ticket DB
231 self.dbTickets = dbTickets
233 self.l_ticket_blacklist = l_ticket_blacklist
235 self.q_fromRT = q_fromRT
237 Thread.__init__(self,target = self.getTickets)
239 # Takes node from q_toRT, gets tickets.
240 # Thread that actually gets the tickets.
241 def getTickets(self):
244 diag_node = self.q_toRT.get(block = True)
245 if diag_node != None:
246 host = diag_node['nodename']
247 (b_host_inticket, r_ticket) = is_host_in_rt_tickets(host, \
248 self.l_ticket_blacklist, \
250 diag_node['found_rt_ticket'] = None
252 logger.debug("RT: found tickets for %s" %host)
253 diag_node['found_rt_ticket'] = r_ticket['ticket_id']
256 if r_ticket is not None:
257 print "Ignoring ticket %s" % r_ticket['ticket_id']
258 # TODO: why do i return the ticket id for a
259 # blacklisted ticket id?
260 #diag_node['found_rt_ticket'] = r_ticket['ticket_id']
261 self.count = self.count + 1
263 self.q_fromRT.put(diag_node)
265 print "RT processed %d nodes with noticket" % self.count
266 logger.debug("RT filtered %d noticket nodes" % self.count)
267 self.q_fromRT.put(None)
271 # Removes hosts that are no longer down.
272 def remTickets(self):
273 logger.debug("Removing stale entries from DB.")
274 prevdown = self.tickets.keys()
277 ##BEGIN HACK. This should be outside of this file. passed to class.
278 #cmn = comon.Comon(None, None)
280 #for bucket in cmn.comonbkts.keys():
281 # for host in getattr(cmn,bucket):
282 # if host not in currdown: currdown.append(host)
285 # Actually do the comparison
286 #for host in prevdown:
287 # if host not in currdown:
288 # del self.tickets[host]
289 # logger.info("RT: %s no longer down." % host)
292 def updateTickets(self):
293 logger.info("Refreshing DB.")
294 for host in self.tickets.keys():
295 # Put back in Q to refresh
296 self.q_toRT.put(host)
298 def cleanTickets(self):
305 logger.setLevel(logging.DEBUG)
306 ch = logging.StreamHandler()
307 ch.setLevel(logging.DEBUG)
308 formatter = logging.Formatter('%(message)s')
309 ch.setFormatter(formatter)
310 logger.addHandler(ch)
312 tickets = rt_tickets()
313 soltesz.dbDump("ad_dbTickets", tickets)
316 if __name__ == '__main__':