import Queue
import time
import re
-import comon
-import soltesz
+import database
from threading import *
+import config
# TODO: merge the RT mailer from mailer.py into this file.
-# RT database access constants file
-RT_DB_CONSTANTS_PATH='/etc/planetlab/rt_db'
-
#Logging
logger = logging.getLogger("monitor")
def open_rt_db():
# read plc database passwords and connect
- rt_db_constants= readConstantsFile(RT_DB_CONSTANTS_PATH)
- if rt_db_constants is None:
- print "Unable to read database access constants from %s" % \
- RT_DB_CONSTANTS_PATH
- return -1
+ #rt_db_constants= readConstantsFile(RT_DB_CONSTANTS_PATH)
+ #if rt_db_constants is None:
+ # print "Unable to read database access constants from %s" % \
+ # RT_DB_CONSTANTS_PATH
+ # return -1
try:
- rt_db = MySQLdb.connect(host=rt_db_constants['RT_DB_HOST'],
- user=rt_db_constants['RT_DB_USER'],
- passwd=rt_db_constants['RT_DB_PASSWORD'],
- db=rt_db_constants['RT_DB_NAME'])
+ rt_db = MySQLdb.connect(host=config.RT_DB_HOST,
+ user=config.RT_DB_USER,
+ passwd=config.RT_DB_PASSWORD,
+ db=config.RT_DB_NAME)
except Exception, err:
print "Failed to connect to RT database: %s" %err
return -1
+def fetch_from_db(db, sql):
+ try:
+ # create a 'cursor' (required by MySQLdb)
+ c = db.cursor()
+ c.execute(sql)
+ except Exception, err:
+ print "Could not execute RT query %s" %err
+ return -1
+
+ # fetch all rows (list of lists)
+ raw = c.fetchall()
+ return raw
+
def rt_tickets():
db = open_rt_db()
+ if db == -1:
+ return ""
# sql = """SELECT distinct Tk.id, Tk.Status, Tk.Subject
# FROM Tickets AS Tk
# JOIN Transactions AS Tr ON Tk.id=Tr.ObjectId
# % (hostname,hostname)
# Queue == 10 is the spam Queue in RT.
+# 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' ;
+#
+
sql = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content
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'"""
#Tr.id=At.TransactionID AND (Tk.Status = 'new' OR Tk.Status = 'open')"""
-
- try:
- # create a 'cursor' (required by MySQLdb)
- c = db.cursor()
- c.execute(sql)
- except Exception, err:
- print "Could not execute RT query %s" %err
- return -1
-
- # fetch all rows (list of lists)
- raw = c.fetchall()
-
- # map list of lists (raw) to list of dicts (tickets)
- # when int gets pulls from SQL into python ints are converted to LONG to
- # prevent overflow .. convert back
- #tickets = map(lambda x: {"ticket_id":int(x[0]),
+ #sqlall = """SELECT distinct Tk.id, Tk.Status, Tk.Subject, At.Content
+#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' OR
+#Tk.Status = 'new') """
+ 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 """
+
+
+ raw = fetch_from_db(db, sql)
+ if raw == -1:
+ return raw
tickets = map(lambda x: {"ticket_id":str(x[0]),
"status":x[1],
"subj":str(x[2]),
"content":str(x[3])},
raw)
+
+ raw = fetch_from_db(db,sqlall)
+ if raw == -1:
+ return raw
+ tickets_all = map(lambda x: {"ticket_id":str(x[0]),
+ "status":x[1],
+ "subj":str(x[2]),
+ "content":str(x[3]),
+ "email":str(x[4]),
+ "lastupdated":str(x[5]),
+ "queue":str(x[6]),
+ "owner":str(x[7]),
+ },
+ raw)
+
db.close()
+ idTickets = {}
+ for t in tickets_all:
+ idTickets[t['ticket_id']] = t
+ database.dbDump("idTickets", idTickets)
+
return tickets
def is_host_in_rt_tickets(host, ticket_blacklist, ad_rt_tickets):
return (False, None)
# This search, while O(tickets), takes less than a millisecond, 05-25-07
- #t = soltesz.MyTimer()
+ #t = commands.MyTimer()
ret = search_tickets(host, ad_rt_tickets)
#del t
ch.setFormatter(formatter)
logger.addHandler(ch)
- bucket = Queue.Queue()
- tickets = {}
- a = RT(tickets, bucket)
- b = RT(tickets, bucket)
- c = RT(tickets, bucket)
- d = RT(tickets, bucket)
- e = RT(tickets, bucket)
- a.start()
- b.start()
- c.start()
- d.start()
- tmp = ('planetlab-1.cs.ucy.ac.cy','planetlab-2.vuse.vanderbilt.edu', 'planetlab-11.cs.princeton.edu', 'planet03.csc.ncsu.edu', 'planetlab1.pop-rj.rnp.br', 'planet1.halifax.canet4.nodes.planet-lab.org', 'planet1.cavite.nodes.planet-lab.org', 'ds-pl3.technion.ac.il', 'planetlab2.cs.purdue.edu', 'planetlab3.millennium.berkeley.edu', 'planetlab1.unl.edu', 'planetlab1.cs.colorado.edu', 'planetlab02.cs.washington.edu', 'orbpl2.rutgers.edu', 'planetlab2.informatik.uni-erlangen.de', 'pl2.ernet.in', 'neu2.6planetlab.edu.cn', 'planetlab-2.cs.uni-paderborn.de', 'planetlab1.elet.polimi.it', 'planetlab2.iiitb.ac.in', 'server1.planetlab.iit-tech.net', 'planetlab2.iitb.ac.in', 'planetlab1.ece.ucdavis.edu', 'planetlab02.dis.unina.it', 'planetlab-1.dis.uniroma1.it', 'planetlab1.iitb.ac.in', 'pku1.6planetlab.edu.cn', 'planetlab1.warsaw.rd.tp.pl', 'planetlab2.cs.unc.edu', 'csu2.6planetlab.edu.cn', 'pl1.ernet.in', 'planetlab2.georgetown.edu', 'planetlab1.cs.uchicago.edu')
- for host in tmp:
- bucket.put(host)
- #et = Thread(target=e.pushHosts)
- #et.start()
- time.sleep(15)
- print tickets.keys()
- time.sleep(15)
- print tickets.keys()
- time.sleep(15)
- print tickets.keys()
- #cmn = comon.Comon(cdb, bucket)
- #cmn.updatebkts()
- #for bucket in cmn.comonbkts.keys():
-# for host in getattr(cmn,bucket):
-# alldown.put(host)
-#
- at = Thread(target=a.cleanTickets)
- at.start()
- time.sleep(15)
- print tickets.keys()
- os._exit(0)
+ tickets = rt_tickets()
+ database.dbDump("ad_dbTickets", tickets)
+
if __name__ == '__main__':
main()