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]