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, Q.Name, Tk.Owner FROM Tickets AS Tk, Attachments AS At, Queues as Q, 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 AND Q.id=Tk.Queue """
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]),
174 for t in tickets_all:
175 idTickets[t['ticket_id']] = t
176 soltesz.dbDump("idTickets", idTickets)
180 def is_host_in_rt_tickets(host, ticket_blacklist, ad_rt_tickets):
181 # ad_rt_tickets is an array of dicts, defined above.
182 if len(ad_rt_tickets) == 0:
185 d_ticket = ad_rt_tickets[0]
186 if not ('ticket_id' in d_ticket and 'status' in d_ticket and
187 'subj' in d_ticket and 'content' in d_ticket):
188 logger.debug("RT_tickets array has wrong fields!!!")
191 #logger.debug("Searching all tickets for %s" % host)
192 def search_tickets(host, ad_rt_tickets):
193 # compile once for more efficiency
194 re_host = re.compile(host)
195 for x in ad_rt_tickets:
196 if re_host.search(x['subj'], re.MULTILINE|re.IGNORECASE) or \
197 re_host.search(x['content'], re.MULTILINE|re.IGNORECASE):
198 logger.debug("\t ticket %s has %s" % (x['ticket_id'], host))
199 print "\t ticket %s has %s" % (x['ticket_id'], host)
200 if x['ticket_id'] in ticket_blacklist:
204 print "\t noticket -- has %s" % host
205 #logger.debug("\t noticket -- has %s" % host)
208 # This search, while O(tickets), takes less than a millisecond, 05-25-07
209 #t = soltesz.MyTimer()
210 ret = search_tickets(host, ad_rt_tickets)
217 Finds tickets associated with hostnames.
218 The idea is if you give it an array of host names,
219 presumeably from comon's list of bad nodes, it starts
220 a few threads to query RT. RT takes a while to return.
222 This is turning into a reinvention of DB design, which I dont believe in.
223 In an effort to keep things minimal, here's the basic algo:
225 Give list of hostnames to RT()
226 Finds tickets associate with new hostnames (not in dict(tickets)).
227 Remove nodes that have come backup. Don't care of ticket is closed after first query.
228 Another thread refresh tickets of nodes already in dict and remove nodes that have come up.
231 def __init__(self, dbTickets, q_toRT, q_fromRT, l_ticket_blacklist, target = None):
232 # Time of last update of ticket DB
233 self.dbTickets = dbTickets
235 self.l_ticket_blacklist = l_ticket_blacklist
237 self.q_fromRT = q_fromRT
239 Thread.__init__(self,target = self.getTickets)
241 # Takes node from q_toRT, gets tickets.
242 # Thread that actually gets the tickets.
243 def getTickets(self):
246 diag_node = self.q_toRT.get(block = True)
247 if diag_node != None:
248 host = diag_node['nodename']
249 (b_host_inticket, r_ticket) = is_host_in_rt_tickets(host, \
250 self.l_ticket_blacklist, \
252 diag_node['found_rt_ticket'] = None
254 logger.debug("RT: found tickets for %s" %host)
255 diag_node['found_rt_ticket'] = r_ticket['ticket_id']
258 if r_ticket is not None:
259 print "Ignoring ticket %s" % r_ticket['ticket_id']
260 # TODO: why do i return the ticket id for a
261 # blacklisted ticket id?
262 #diag_node['found_rt_ticket'] = r_ticket['ticket_id']
263 self.count = self.count + 1
265 self.q_fromRT.put(diag_node)
267 print "RT processed %d nodes with noticket" % self.count
268 logger.debug("RT filtered %d noticket nodes" % self.count)
269 self.q_fromRT.put(None)
273 # Removes hosts that are no longer down.
274 def remTickets(self):
275 logger.debug("Removing stale entries from DB.")
276 prevdown = self.tickets.keys()
279 ##BEGIN HACK. This should be outside of this file. passed to class.
280 #cmn = comon.Comon(None, None)
282 #for bucket in cmn.comonbkts.keys():
283 # for host in getattr(cmn,bucket):
284 # if host not in currdown: currdown.append(host)
287 # Actually do the comparison
288 #for host in prevdown:
289 # if host not in currdown:
290 # del self.tickets[host]
291 # logger.info("RT: %s no longer down." % host)
294 def updateTickets(self):
295 logger.info("Refreshing DB.")
296 for host in self.tickets.keys():
297 # Put back in Q to refresh
298 self.q_toRT.put(host)
300 def cleanTickets(self):
307 logger.setLevel(logging.DEBUG)
308 ch = logging.StreamHandler()
309 ch.setLevel(logging.DEBUG)
310 formatter = logging.Formatter('%(message)s')
311 ch.setFormatter(formatter)
312 logger.addHandler(ch)
314 tickets = rt_tickets()
315 soltesz.dbDump("ad_dbTickets", tickets)
318 if __name__ == '__main__':