def refresh():
db_conn.execute(sync_sql, []) # 同步主机ip数据
db_conn.commit()
cursor = db_conn.execute(sql, [])
rows = cursor.fetchall()
cursor and cursor.close()
for row in rows:
DashBoardUrls[row[0]] = row[1]
print 'endpoint dashboard urls'
print(DashBoardUrls)
def gets_by_endpoint_ids(cls, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids + [start, limit]
if not limit:
cursor = db_conn.execute('''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in ('''+placeholder+''')''',endpoint_ids)
else:
cursor = db_conn.execute('''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in ('''+placeholder+''') limit %s, %s''', args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_endpoint_ids(cls, qs, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids
for q in qs:
args.append("%"+q+"%")
args += [start, limit]
sql = '''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in (''' +placeholder+ ''') '''
i = 0
size = len(qs)
if size > 0:
sql += ''' and ('''
for q in qs:
if i+1 != size:
sql += ''' counter like %s or '''
else:
sql += ''' counter like %s '''
i += 1
sql += ''')'''
sql += ''' limit %s,%s'''
print ('search_in_endpoint_ids sql===>>>%s'%sql)
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def get_name_by_id(cls,id): grp_id = str(id[0:2]) idc_id = str(grp_id+id[2:4]) args = [grp_id,idc_id,id] sql = '''select id,pid,name,isparent from ztree where id = %s or id = %s or id =%s order by id DESC''' cursor = db_conn.execute(sql,args) rows = cursor.fetchall() cursor and cursor.close() return [cls(*row) for row in rows]
def refresh():
cursor = db_conn.execute(sql, [])
rows = cursor.fetchall()
cursor and cursor.close()
for row in rows:
Desps[row[0]] = row[1]
print 'counter descriptions'
print(Desps)
def search_in_endpoint_ids(cls, qs, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids
sql = '''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in (''' +placeholder+ ''') '''
tags = {}
is_packet_loss_rate = False
is_average = False
for q in qs:
match = re.match('([^\0]+)=([^\0]+)', q)
if match:
tags[match.group(1)] = match.group(2).split(',')
else:
if "packet-loss-rate" in q:
q = "packets-sent"
is_packet_loss_rate = True
elif "average" in q:
q = "transmission-time"
is_average = True
args.append("%"+q+"%")
sql += ''' and counter like %s'''
if 'isp' in tags:
sql += EndpointCounter.tag_query(tags, args, 'isp')
if 'province' in tags:
sql += EndpointCounter.tag_query(tags, args, 'province')
if 'city' in tags:
sql += EndpointCounter.tag_query(tags, args, 'city')
if 'tag' in tags:
sql += EndpointCounter.tag_query(tags, args, 'tag')
args += [start, limit]
sql += ''' limit %s,%s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
if is_packet_loss_rate or is_average:
rows = list(rows)
# clone the first one and then set the filed of NQM
row = list(rows[0])
if is_packet_loss_rate:
row[2] = 'packet-loss-rate'
elif is_average:
row[2] = 'average'
rows = (rows + [(row)])
return [cls(*row) for row in rows]
def search_type(cls, type, start=0, limit=100, deadline=0):
args = [type]
print ("type===>>%s" % type)
sql = """select id, endpoint, ts from endpoint where t_type = %s"""
print ("search_type sql is===>>>%s" % sql)
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_agent_and_httpapi_by_endpoint(cls, endpoint):
try:
args = [endpoint]
sql = """SELECT t1.port AS id, t2.endpoint as endpoint, t1.http_api as ts FROM endpoint t1, endpoint t2 WHERE t1.agent_id = t2.id AND t1.endpoint = %s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
except Exception, e:
print e
raise e
def search_endpoint_by_cluster(cls, cluster_group, start=0, limit=100, deadline=0):
try:
args = [cluster_group]
sql = """select port as id, endpoint, ts from endpoint where cluster_group = %s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
except Exception, e:
print e
raise e
def gets(cls, ids, deadline=0):
if not ids:
return []
holders = ["%s" for x in ids]
placeholder = ",".join(holders)
args = ids + [deadline, ]
cursor = db_conn.execute('''select id, endpoint, ts from endpoint where id in (''' + placeholder + ''') and ts > %s''', args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_agent_endpoint_by_cluster(cls, cluster_group, start=0, limit=100, deadline=0):
try:
args = [cluster_group]
# sql = '''select port as id, endpoint, ts from endpoint where cluster_group = %s'''
sql = """SELECT t1.port AS id, t2.endpoint, t1.ts FROM endpoint t1, endpoint t2 WHERE t1.agent_id = t2.id AND t1.cluster_group = %s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
except Exception, e:
print e
raise e
def search_cluster(cls, type, start=0, limit=100, deadline=0):
try:
args = [type]
sql = """SELECT 1, cluster_group as endpoint, 1 FROM endpoint where t_type = %s group by cluster_group HAVING cluster_group!=\'\' and cluster_group is not null"""
print ("search_cluster sql is===>>>%s" % sql)
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
except Exception, e:
print e
raise e
def get_endpoint_ids(cls, tags, limit=200):
if not tags:
return []
holders = ["%s" for x in tags]
placeholder = ",".join(holders)
args = tags
cursor = db_conn.execute(
'''select distinct endpoint_id from tag_endpoint where tag in ('''
+ placeholder + ''')''', args)
rows = cursor.fetchall()
ids = [x[0] for x in rows]
if not ids:
return []
res = None
for t in tags:
holders = ["%s" for x in ids]
placeholder = ",".join(holders)
args = list(ids) + [
t,
]
sql = '''select endpoint_id from tag_endpoint where endpoint_id in (''' + placeholder + ''') and tag=%s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
if not rows:
return []
if res is None:
res = set([row[0] for row in rows])
else:
res.intersection_update(set([row[0] for row in rows]))
ret = list(res) if res else []
return ret[:limit]
def gets_by_endpoint(cls, endpoints, deadline=0):
if not endpoints:
return []
holders = ["%s" for x in endpoints]
placeholder = ",".join(holders)
args = endpoints + [deadline]
cursor = db_conn.execute(
"""select id, endpoint, ts from endpoint where endpoint in (""" + placeholder + """) and ts > %s""", args
)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def get_endpoint_ids(cls, tags, limit=200):
if not tags:
return []
holders = ["%s" for x in tags]
placeholder = ",".join(holders)
args = tags
cursor = db_conn.execute(
"""select distinct endpoint_id from tag_endpoint where tag in (""" + placeholder + """)""", args
)
rows = cursor.fetchall()
ids = [x[0] for x in rows]
if not ids:
return []
res = None
for t in tags:
holders = ["%s" for x in ids]
placeholder = ",".join(holders)
args = list(ids) + [t]
sql = """select endpoint_id from tag_endpoint where endpoint_id in (""" + placeholder + """) and tag=%s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
if not rows:
return []
if res is None:
res = set([row[0] for row in rows])
else:
res.intersection_update(set([row[0] for row in rows]))
ret = list(res) if res else []
return ret[:limit]
def gets_by_endpoint_ids(cls, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids + [start, limit]
cursor = db_conn.execute(
'''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in ('''
+ placeholder + ''') limit %s, %s''', args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search(cls, qs, start=0, limit=100, deadline=0):
args = [deadline]
for q in qs:
args.append("%" + q + "%")
args += [start, limit]
sql = """select id, endpoint, ts from endpoint where ts > %s and t_type not in (\'mysql-cluster\', \'redis-cluster\', \'rabbitmq-cluster\') """
for q in qs:
sql += """ and endpoint like %s"""
sql += """ limit %s,%s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_regexp(cls, qs, start=0, limit=100, deadline=0):
args = [deadline, ]
for q in qs:
args.append("^"+q)
args += [start, limit]
sql = '''select id, endpoint, ts from endpoint where ts > %s '''
for q in qs:
sql += ''' and endpoint regexp %s'''
sql += ''' limit %s,%s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def gets(cls, ids, deadline=0):
if not ids:
return []
holders = ["%s" for x in ids]
placeholder = ",".join(holders)
args = ids + [deadline]
cursor = db_conn.execute(
"""select id, endpoint, ts from endpoint where t_type not in (\'mysql-cluster\', \'redis-cluster\', \'rabbitmq-cluster\') and id in ("""
+ placeholder
+ """) and ts > %s""",
args,
)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_ids(cls, qs, ids, deadline=0):
if not ids:
return []
holders = ["%s" for x in ids]
placeholder = ",".join(holders)
args = ids + [deadline, ]
for q in qs:
args.append("%"+q+"%")
sql = '''select id, endpoint, ts from endpoint where id in (''' + placeholder + ''') and ts > %s '''
for q in qs:
sql += ''' and endpoint like %s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search(cls, qs, start=0, limit=0, deadline=0):
args = [deadline, ]
for q in qs:
args.append("%"+q+"%")
sql = '''select id, endpoint, ts from endpoint where ts > %s '''
flag = False
for q in qs:
if not flag:
sql += ''' and endpoint like %s'''
flag = True
else:
sql += ''' or endpoint like %s'''
if limit:
args += [start, limit]
sql += ''' limit %s,%s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_endpoint_ids(cls, qs, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids
for q in qs:
args.append("%"+q+"%")
sql = '''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in (''' +placeholder+ ''') '''
for q in qs:
sql += ''' and counter like %s'''
if limit:
sql += ''' limit %s,%s'''
args += [start, limit]
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_endpoint_ids(cls, qs, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids
for q in qs:
args.append("%" + q + "%")
args += [start, limit]
sql = '''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in (''' + placeholder + ''') '''
for q in qs:
sql += ''' and counter like %s'''
sql += ''' limit %s,%s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_ids(cls, qs, ids, deadline=0):
if not ids:
return []
holders = ["%s" for x in ids]
placeholder = ",".join(holders)
args = ids + [deadline]
for q in qs:
args.append("%" + q + "%")
sql = (
"""select id, endpoint, ts from endpoint where id in ("""
+ placeholder
+ """) and ts > %s and t_type not in (\'mysql-cluster\', \'redis-cluster\', \'rabbitmq-cluster\') """
)
for q in qs:
sql += """ and endpoint like %s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_endpoint_ids(cls, qs, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids
sql = (
"""select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in ("""
+ placeholder
+ """) """
)
tags = {}
is_packet_loss_rate = False
is_average = False
sql_sub = []
for q in qs:
match = re.match("([^\0]+)=([^\0]+)", q)
if match:
tags[match.group(1)] = match.group(2).split(",")
else:
if "packet-loss-rate" in q:
q = "packets-sent"
is_packet_loss_rate = True
elif "average" in q:
q = "transmission-time"
is_average = True
args.append("%" + q + "%")
sql_sub.append(""" counter like %s """)
if len(sql_sub) != 0:
sql += " AND (" + " OR ".join(sql_sub) + ") "
if "nqm-agent-isp" in tags:
sql += EndpointCounter.tag_query(tags, args, "nqm-agent-isp")
if "nqm-agent-province" in tags:
sql += EndpointCounter.tag_query(tags, args, "nqm-agent-province")
if "nqm-agent-city" in tags:
sql += EndpointCounter.tag_query(tags, args, "nqm-agent-city")
if "target-isp" in tags:
sql += EndpointCounter.tag_query(tags, args, "target-isp")
if "target-province" in tags:
sql += EndpointCounter.tag_query(tags, args, "target-province")
if "target-city" in tags:
sql += EndpointCounter.tag_query(tags, args, "target-city")
if "target-name-tag" in tags:
sql += EndpointCounter.tag_query(tags, args, "target-name-tag")
args += [start, limit]
sql += """ limit %s,%s"""
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
if is_packet_loss_rate or is_average:
rows = list(rows)
# clone the first one and then set the filed of NQM
row = list(rows[0])
if is_packet_loss_rate:
row[2] = "packet-loss-rate"
elif is_average:
row[2] = "average"
rows = rows + [(row)]
return [cls(*row) for row in rows]
def gets(cls):
sql = '''select id,pid,name,isparent from ztree order by name'''
cursor = db_conn.execute(sql)
rows = cursor.fetchall()
cursor and cursor.close()
return [cls(*row) for row in rows]
def search_in_endpoint_ids(cls, qs, endpoint_ids, start=0, limit=100):
if not endpoint_ids:
return []
holders = ["%s" for x in endpoint_ids]
placeholder = ",".join(holders)
args = endpoint_ids
sql = '''select id, endpoint_id, counter, step, type from endpoint_counter where endpoint_id in (''' + placeholder + ''') '''
tags = {}
is_packet_loss_rate = False
is_average = False
sql_sub = []
for q in qs:
match = re.match('([^\0]+)=([^\0]+)', q)
if match:
tags[match.group(1)] = match.group(2).split(',')
else:
if "packet-loss-rate" in q:
q = "packets-sent"
is_packet_loss_rate = True
elif "average" in q:
q = "transmission-time"
is_average = True
args.append("%" + q + "%")
sql_sub.append(''' counter like %s ''')
if len(sql_sub) != 0:
sql += " AND (" + " OR ".join(sql_sub) + ") "
if 'nqm-agent-isp' in tags:
sql += EndpointCounter.tag_query(tags, args, 'nqm-agent-isp')
if 'nqm-agent-province' in tags:
sql += EndpointCounter.tag_query(tags, args, 'nqm-agent-province')
if 'nqm-agent-city' in tags:
sql += EndpointCounter.tag_query(tags, args, 'nqm-agent-city')
if 'target-isp' in tags:
sql += EndpointCounter.tag_query(tags, args, 'target-isp')
if 'target-province' in tags:
sql += EndpointCounter.tag_query(tags, args, 'target-province')
if 'target-city' in tags:
sql += EndpointCounter.tag_query(tags, args, 'target-city')
if 'target-name-tag' in tags:
sql += EndpointCounter.tag_query(tags, args, 'target-name-tag')
args += [start, limit]
sql += ''' limit %s,%s'''
cursor = db_conn.execute(sql, args)
rows = cursor.fetchall()
cursor and cursor.close()
if is_packet_loss_rate or is_average:
rows = list(rows)
# clone the first one and then set the filed of NQM
row = list(rows[0])
if is_packet_loss_rate:
row[2] = 'packet-loss-rate'
elif is_average:
row[2] = 'average'
rows = (rows + [(row)])
return [cls(*row) for row in rows]
