def get_event_monitor(self, pk):
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where evm.id = %d and ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC""" % (int(pk))
db = Database()
event_monitor_list = db.execute_query(sql)
return event_monitor_list
def get_completed_event_monitor_list(self):
sql = """select evm.id, evm.pid, ev.name as event_name, from_unixtime(ev.start_date), from_unixtime(ev.end_date), en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date < unix_timestamp(now()) and ev.end_date < unix_timestamp(now()) and ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""
db = Database()
event_monitor_list = db.execute_query(sql)
return event_monitor_list
"""
@brief Script to create sensor test db tables from scratch. Existing tables
will be dropped.
@author J. Chiang <*****@*****.**>
"""
from MySQL_Database import Database
def drop_tables(db, infile='dropTables.sql'):
for sql in open(infile):
try:
db.apply(sql)
except:
pass
def create_tables(db, infile='createTables.sql'):
sql = ''.join(open(infile).readlines())
print sql
db.apply(sql)
if __name__ == '__main__':
# db = Database('db_dev_u.par')
db = Database('db_test_u.par')
drop_tables(db)
create_tables(db)
def __init__(self):
self.db = Database()
class Event():
def __init__(self):
self.db = Database()
def get_event_monitor_list(self, region = None):
if region:
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.active = 1 and ev.region = '%s' and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""%(region)
else:
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""
status, message, data_table = self.db.execute_query(sql)
return data_table
def get_running_event_monitor_list(self, region = None):
if region:
sql ="""select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date < unix_timestamp(now()) and ev.end_date > unix_timestamp(now()) and ev.active = 1 and ev.region = '%s' and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""%("HCM")
else:
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date < unix_timestamp(now()) and ev.end_date > unix_timestamp(now()) and ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""
status, message, data_table = self.db.execute_query(sql)
return data_table
def get_waiting_event_monitor_list(self, region = None):
if region:
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date > unix_timestamp(now()) and ev.end_date > unix_timestamp(now()) and ev.active = 1 and ev.region = '%s' and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""%(region)
else:
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date > unix_timestamp(now()) and ev.end_date > unix_timestamp(now()) and ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""
status, message, data_table = self.db.execute_query(sql)
return data_table
def get_completed_event_monitor_list(self, region = None):
if region:
sql = """select evm.id, evm.pid, ev.name as event_name, from_unixtime(ev.start_date), from_unixtime(ev.end_date), en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date < unix_timestamp(now()) and ev.end_date < unix_timestamp(now()) and ev.active = 1 and ev.region = '%s' and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""%(region)
else:
sql = """select evm.id, evm.pid, ev.name as event_name, from_unixtime(ev.start_date), from_unixtime(ev.end_date), en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where ev.start_date < unix_timestamp(now()) and ev.end_date < unix_timestamp(now()) and ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""
status, message, data_table = self.db.execute_query(sql)
return data_table
def get_event_monitor(self, pk):
sql = """select evm.id, evm.pid, ev.name as event_name, ev.start_date, ev.end_date, en.name as encoder, svc.id as service_check_id, svc.name as service_check_name, en.ip_monitor, en.source_main, en.source_backup, evm.status, evm.last_update, evm.active
from event_monitor as evm, event as ev, encoder as en, service_check as svc
where evm.id = %d and ev.active = 1 and evm.active = 1 and en.active = 1 and svc.active = 1 and evm.event_id = ev.id and evm.encoder_id = en.id and evm.service_check_id = svc.id
ORDER BY ev.start_date DESC"""%(int(pk))
status, message, data_table = self.db.execute_query(sql)
return data_table
def update_last_update(self, pk):
sql = """update event_monitor set last_update = unix_timestamp(now()) where id = %d"""%(int(pk))
status, message, data_table = self.db.execute_non_query(sql)
return status
def __init__(self):
self.db = Database()
self.logger = logging.getLogger("probe")
class ProfileAgent:
def __init__(self):
self.db = Database()
self.logger = logging.getLogger("probe")
"""
Monitor list
"""
def convert_profile_agent_monitor_list_to_array(self, data_table):
args = []
for profile_agent in data_table:
try:
args.append({
'id':
profile_agent[0] if profile_agent[0] else None,
'ip':
profile_agent[1] if profile_agent[1] else "",
'protocol':
profile_agent[2] if profile_agent[2] else 'udp',
'status':
profile_agent[3] if profile_agent[3] else 0,
'agent':
profile_agent[4] if profile_agent[4] else "",
'thread':
profile_agent[5] if profile_agent[5] else 10,
'name':
profile_agent[6] if profile_agent[6] else "",
'type':
profile_agent[7] if profile_agent[7] else None
})
except Exception as e:
self.logger.error("error: %d, message: %s" % (1, str(e)))
return args
def get_profile_agent_monitor_list(self, ip):
http_status_code = 500
message = "Unknow"
data = None
sql = """select pa.id, p.ip, p.protocol, pa.status, a.name as agent, a.thread, c.name, p.type
from profile as p, agent as a, profile_agent as pa,channel as c
where a.ip='%s' and a.active=1 and pa.monitor=1 and p.channel_id=c.id and pa.profile_id=p.id and pa.agent_id=a.id""" % (
ip)
status, message, data_table = self.db.execute_query(sql)
if status == 1:
http_status_code = 500
message = message
data = None
self.logger.error("ststus: %d, message: %s" %
(http_status_code, message))
if status == 0:
http_status_code = 200
message = message
data = self.convert_profile_agent_monitor_list_to_array(data_table)
self.logger.debug("ststus: %s, message: total %d" %
(http_status_code, len(data)))
json_response = {
"status": http_status_code,
"message": message,
"data": data
}
json_response = json.dumps(json_response)
json_response = json.loads(json_response)
return json_response
def get_profile_agent_monitor_list_by_source_ip_multicast(
self, ip, source):
http_status_code = 500
message = "Unknow"
data = None
sql = """select pa.id, p.ip, p.protocol, pa.status, a.name as agent, a.thread, c.name, p.type
from profile as p, agent as a, profile_agent as pa,channel as c
where p.ip LIKE '%s:%%' and a.ip='%s' and a.active=1 and pa.monitor=1 and p.channel_id=c.id and pa.profile_id=p.id and pa.agent_id=a.id""" % (
source, ip)
status, message, data_table = self.db.execute_query(sql)
if status == 1:
http_status_code = 500
message = message
data = None
self.logger.error("ststus: %d, message: %s" %
(http_status_code, message))
if status == 0:
http_status_code = 200
message = message
data = self.convert_profile_agent_monitor_list_to_array(data_table)
self.logger.debug("ststus: %s, message: total %d" %
(http_status_code, len(data)))
json_response = {
"status": http_status_code,
"message": message,
"data": data
}
json_response = json.dumps(json_response)
json_response = json.loads(json_response)
return json_response
"""
Snmp list
"""
def convert_profile_agent_snmp_list_to_array(self, data_table):
args = []
for profile_agent in data_table:
try:
args.append({
'id':
profile_agent[0] if profile_agent[0] else None,
'name':
profile_agent[1] if profile_agent[1] else "",
'ip':
profile_agent[2] if profile_agent[2] else "",
'type':
profile_agent[3] if profile_agent[3] else "",
'monitor':
profile_agent[4] if profile_agent[4] else 0,
'status':
profile_agent[5] if profile_agent[5] else 0,
'analyzer':
profile_agent[6] if profile_agent[6] else 0,
'analyzer_status':
profile_agent[7] if profile_agent[7] else 0,
"video_status":
profile_agent[8] if profile_agent[8] else 0
})
except Exception as e:
self.logger.error("error: %d, message: %s" % (1, str(e)))
return args
def get_profile_agent_snmp_list(self, ip):
sql = """select pa.id, c.name, p.ip, p.type, pa.monitor, pa.status, pa.analyzer, pa.analyzer_status, pa.video
from profile as p, agent as a, profile_agent as pa,channel as c
where a.ip='%s' and (pa.monitor=1 or pa.analyzer=1) and a.active=1 and p.channel_id=c.id and pa.profile_id=p.id and pa.agent_id=a.id order by c.name""" % (
ip)
status, message, data_table = self.db.execute_query(sql)
if status == 1:
http_status_code = 500
message = message
data = None
self.logger.error("ststus: %d, message: %s" %
(http_status_code, message))
if status == 0:
http_status_code = 200
message = message
data = self.convert_profile_agent_snmp_list_to_array(data_table)
self.logger.debug("ststus: %s, message: total %d" %
(http_status_code, len(data)))
json_response = {
"status": http_status_code,
"message": message,
"data": data
}
json_response = json.dumps(json_response)
json_response = json.loads(json_response)
return json_response
"""
check video
"""
def convert_profile_agent_check_video_list_to_array(self, data_table):
args = []
for profile_agent in data_table:
try:
args.append({
"id":
profile_agent[0] if profile_agent[0] else None,
"ip":
profile_agent[1] if profile_agent[1] else "",
"protocol":
profile_agent[2] if profile_agent[2] else "udp",
"status":
profile_agent[3] if profile_agent[3] else 0,
"thread":
profile_agent[4] if profile_agent[4] else 10,
"name":
profile_agent[5] if profile_agent[5] else "Unknow",
"agent":
profile_agent[6] if profile_agent[6] else "Unknow",
"type":
profile_agent[7] if profile_agent[7] else "",
"video_status":
profile_agent[8] if profile_agent[8] else 0
})
except Exception as e:
self.logger.error("error: %d, message: %s" % (1, str(e)))
return args
def get_profile_agent_check_video_list(self, ip):
sql = """select pa.id, p.ip, p.protocol, pa.status, a.thread, c.name, a.name as agent_name, p.type, pa.video
from profile as p, agent as a, profile_agent as pa, channel as c
where a.ip = '%s' and a.active = 1 and pa.monitor = 1 and (pa.status = 1 or pa.video !=1) and pa.profile_id = p.id and pa.agent_id = a.id and p.channel_id = c.id""" % (
ip)
status, message, data_table = self.db.execute_query(sql)
if status == 1:
http_status_code = 500
message = message
data = None
self.logger.error("ststus: %d, message: %s" %
(http_status_code, message))
if status == 0:
http_status_code = 200
message = message
data = self.convert_profile_agent_check_video_list_to_array(
data_table)
self.logger.debug("ststus: %s, message: total %d" %
(http_status_code, len(data)))
json_response = {
"status": http_status_code,
"message": message,
"data": data
}
json_response = json.dumps(json_response)
json_response = json.loads(json_response)
return json_response
"""
Anylazer first check list
"""
def convert_profile_agent_first_check_anylazer_list_to_array(
self, data_table):
args = []
for profile_agent in data_table:
try:
args.append({
'id':
profile_agent[0] if profile_agent[0] else None,
'ip':
profile_agent[1] if profile_agent[1] else "",
'agent_ip':
profile_agent[2] if profile_agent[2] else "",
'dropframe':
profile_agent[3] if profile_agent[3] else 0,
'discontinuity':
profile_agent[4] if profile_agent[4] else 0
})
except Exception as e:
self.logger.error("error: %d, message: %s" % (1, str(e)))
return args
def get_profile_agent_first_check_anylazer_list(self):
sql = """select pa.id,p.ip,a.ip,dropframe,discontinuity
from profile_agent as pa, profile as p, agent as a
where (a.name like '%Origin%' or a.name like '%4500%') and pa.analyzer=1 and a.active=1 and pa.profile_id=p.id and pa.agent_id=a.id"""
status, message, data_table = self.db.execute_query(sql)
if status == 1:
http_status_code = 500
message = message
data = None
self.logger.error("ststus: %d, message: %s" %
(http_status_code, message))
if status == 0:
http_status_code = 200
message = message
data = self.convert_profile_agent_first_check_anylazer_list_to_array(
data_table)
self.logger.debug("ststus: %s, message: total %d" %
(http_status_code, len(data)))
json_response = {
"status": http_status_code,
"message": message,
"data": data
}
json_response = json.dumps(json_response)
json_response = json.loads(json_response)
return json_response
"""
Anylazer last check list
"""
def convert_profile_agent_last_check_analyzer_list_to_array(
self, data_table):
args = []
for profile_agent in data_table:
try:
args.append({
'id':
profile_agent[0] if profile_agent[0] else None,
'ip':
profile_agent[1] if profile_agent[1] else "",
'agent_ip':
profile_agent[2] if profile_agent[2] else "",
'analyzer_status':
profile_agent[3] if profile_agent[3] else 0,
'dropframe':
profile_agent[4] if profile_agent[4] else 0,
'dropframe_threshold':
profile_agent[5] if profile_agent[5] else 0,
'discontinuity':
profile_agent[6] if profile_agent[6] else 0,
'discontinuity_threshold':
profile_agent[7] if profile_agent[7] else 0,
})
except Exception as e:
self.logger.error("error: %d, message: %s" % (1, str(e)))
return args
def get_profile_agent_last_check_analyzer_list(self):
sql = """select pa.id, p.ip, a.ip, pa.analyzer_status, pa.dropframe, pa.dropframe_threshold, pa.discontinuity, pa.discontinuity_threshold
from profile_agent as pa, profile as p, agent as a
where (a.name like '%Origin%' or a.name like '%4500%') and (pa.dropframe > 0 or pa.discontinuity > 0 or analyzer_status !=1) and a.active=1 and pa.analyzer=1 and pa.profile_id=p.id and pa.agent_id=a.id"""
status, message, data_table = self.db.execute_query(sql)
if status == 1:
http_status_code = 500
message = message
data = None
self.logger.error("ststus: %d, message: %s" %
(http_status_code, message))
if status == 0:
http_status_code = 200
message = message
data = self.convert_profile_agent_last_check_analyzer_list_to_array(
data_table)
self.logger.debug("ststus: %s, message: total %d" %
(http_status_code, len(data)))
json_response = {
"status": http_status_code,
"message": message,
"data": data
}
json_response = json.dumps(json_response)
json_response = json.loads(json_response)
return json_response
def __init__(self, dbdata):
Database.__init__(self, dbdata)