def gen_city_view(): sql = "select id,view_country,view_isp,view_region,view_province from Polaris_tb_fact_temp_view_info where view_grade = 5" res = my_custom_sql(sql) country_isp_region_province_dict = {} for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] view_region = line[3] view_province = line[4] country_isp_region_province_dict["{}_{}_{}_{}".format( view_country, view_isp, view_region, view_province)] = view_id sql = "select country,isp,region,province,city from Polaris_tb_fact_ori_view_info group by country,isp,region,province,city" res = my_custom_sql(sql) for line in res: view_country = line[0] view_isp = line[1] view_region = line[2] view_province = line[3] view_city = line[4] view_father_id = country_isp_region_province_dict.get( "{}_{}_{}_{}".format(view_country, view_isp, view_region, view_province)) sql = 'insert into Polaris_tb_fact_temp_view_info(view_father_id,view_default,view_country,view_isp,view_region,view_province,view_city,view_grade_name,view_grade,view_father_grade,view_type_id) values({},"default","{}","{}","{}","{}","{}","city",6,5,1)'.format( view_father_id, view_country, view_isp, view_region, view_province, view_city) res = my_custom_sql(sql)
def gen_ipset(): sql = "select start_address,end_address,country,isp,region,province,city from Polaris_tb_fact_ori_view_info" res = my_custom_sql(sql) for line in res: view_start_address = line[0] view_end_address = line[1] view_country = line[2] view_isp = line[3] view_region = line[4] view_province = line[5] view_city = line[6] if view_country!= None and view_isp!= None and view_region!= None and view_province!= None and view_city !=None and view_country!="*" and view_isp!="*" and view_region !="*" and view_province!="*" and view_city !="*": view_id = view_id_dict.get("{}_{}_{}_{}_{}".format(view_country,view_isp,view_region,view_province,view_city)) elif view_country!= None and view_isp!= None and view_region!= None and view_province!= None and view_country!="*" and view_isp!="*" and view_region !="*" and view_province!="*": view_id = view_id_dict.get("{}_{}_{}_{}".format(view_country,view_isp,view_region,view_province)) if view_isp=="广电网" and view_province=="江苏": print("{}_{}_{}_{}_{}_{}_{}".format(view_id,view_start_address,view_end_address,view_country,view_isp,view_province,view_city)) elif view_country!= None and view_isp!= None and view_region!= None and view_country!="*" and view_isp!="*" and view_region !="*": view_id = view_id_dict.get("{}_{}_{}".format(view_country,view_isp,view_region)) elif view_country!= None and view_isp!= None and view_country!="*" and view_isp!="*": view_id = view_id_dict.get("{}_{}".format(view_country,view_isp)) elif view_country!= None and view_country!="*": view_id = view_id_dict.get("{}".format(view_country)) sql = 'insert into Polaris_tb_fact_ipset_info(view_id_id,start_address,end_address) values("{}","{}","{}")'.format(view_id,view_start_address,view_end_address) res = my_custom_sql(sql) print(sql)
def gen_country_view(): sql = "select id,view_default from Polaris_tb_fact_temp_view_info where view_grade = 1" res = my_custom_sql(sql) default_view_dict = {} for line in res: view_id = line[0] view_default = line[1] default_view_dict[view_default] = view_id sql = "select country from Polaris_tb_fact_ori_view_info group by country" res = my_custom_sql(sql) print(res) for line in res: view_country = line[0] sql = 'insert into Polaris_tb_fact_temp_view_info(view_father_id,view_default,view_country,view_grade_name,view_grade,view_father_grade,view_type_id) values(0,"default","{}","country",2,1,1)'.format( view_country) res = my_custom_sql(sql)
def gen_isp_view(): sql = "select id,view_country from Polaris_tb_fact_temp_view_info where view_grade = 2" res = my_custom_sql(sql) country_view_dict = {} for line in res: view_id = line[0] view_country = line[1] country_view_dict[view_country] = view_id sql = "select country,isp from Polaris_tb_fact_ori_view_info group by country,isp" res = my_custom_sql(sql) for line in res: view_country = line[0] view_isp = line[1] view_father_id = country_view_dict.get(view_country) sql = 'insert into Polaris_tb_fact_temp_view_info(view_father_id,view_default,view_country,view_isp,view_grade_name,view_grade,view_father_grade,view_type_id) values({},"default","{}","{}","isp",3,2,1)'.format( view_father_id, view_country, view_isp) res = my_custom_sql(sql)
def gen_detect_viplist_cache(): try: sql = "select detect_frency,detect_frency_unit from Polaris_tb_fact_detecttask_info where detect_name='detect_device_availability'" res = my_custom_sql(sql) frequency = 15 frequency_unit = 'second' for item in res: frequency = item[0] frequency_unit = item[1] if frequency == None or frequency_unit == None: frequency = 15 frequency_unit = 'second' sql = "select a.admin_ip,b.vip_address,a.isp,a.availability_status from Polaris_tb_fact_adminip_info as a left join Polaris_tb_fact_device_info as b on a.isp =b.node_isp" res = my_custom_sql(sql) raw_vip = {} for item in res: adminip = item[0] vip = item[1] isp = item[2] status = item[3] if raw_vip.get(adminip) == None: raw_vip[adminip] = {} raw_vip[adminip]["vip_address"] = [] raw_vip[adminip]["adminip_isp"] = isp raw_vip[adminip]["detect_switch"] = status raw_vip[adminip]["frequency"] = frequency # raw_vip[adminip]["frequency_unit"] = frequency_unit raw_vip[adminip]["vip_address"].append(vip) keys = get_keys_from_cache("vipdevice", "detect-vipaddress") for key in keys: key = str(key, encoding="raw_unicode_escape") if raw_vip.get(key) == None: logger.info("the adminip {} is disable".format(key)) delete_to_cache_cluster("vipdevice", "detect-vipaddress", key) for item in raw_vip: write_to_cache_cluster("vipdevice", "detect-vipaddress", item, json.dumps(raw_vip[item])) logger.info("adminip is {},detect vip is {} ".format( item, read_from_cache_cluster("vipdevice", "detect-vipaddress", item))) except Exception as err: logger.error(err)
def get_dnstype_from_cache(address): logger.info("the {} qdns get dnstype ".format(address)) sql = 'select b.dns_name from Polaris_tb_fact_dnsip_info a left join Polaris_tb_fact_dnstype_info b on a.dns_type_id = b.id where dns_ip = "{}"'.format( address) res = my_custom_sql(sql) if res != None and len(res) != 0: for item in res: dnstype = item[0] if dnstype != None: return dnstype return ""
def gen_region_view(): ''' region_province_dict = { "华东大区":["上海","江苏","浙江","安徽","江西","山东","福建","台湾"], "华北大区":["北京","天津","山西","河北","内蒙古"], "华中大区":["河南","湖北","湖南"], "华南大区":["广东","广西","海南","香港","澳门"], "西南大区":["四川","云南","贵州","西藏","重庆"], "西北大区":["陕西","甘肃","青海","宁夏","新疆"], "东北大区":["黑龙江","辽宁","吉林"] } for region,province_list in region_province_dict.items(): for province in province_list: sql = 'update Polaris_tb_fact_ori_view_info set region = "{}" where province = "{}"'.format(region,province) print(sql) res = my_custom_sql(sql) ''' sql = "select id,view_country,view_isp from Polaris_tb_fact_temp_view_info where view_grade = 3" res = my_custom_sql(sql) country_isp_dict = {} for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] country_isp_dict["{}_{}".format(view_country, view_isp)] = view_id sql = "select country,isp,region from Polaris_tb_fact_ori_view_info group by country,isp,region" res = my_custom_sql(sql) for line in res: view_country = line[0] view_isp = line[1] view_region = line[2] view_father_id = country_isp_dict.get("{}_{}".format( view_country, view_isp)) print(view_father_id) sql = 'insert into Polaris_tb_fact_temp_view_info(view_father_id,view_default,view_country,view_isp,view_region,view_grade_name,view_grade,view_father_grade,view_type_id) values({},"default","{}","{}","{}","region",4,3,1)'.format( view_father_id, view_country, view_isp, view_region) res = my_custom_sql(sql)
def generate_device_status_cache(): try: sql = 'select vip_address,vip_status,vip_enable_switch from Polaris_tb_fact_device_info' res = my_custom_sql(sql) vip_avil_dict = {} for item in res: vip_address = item[0] vip_status = item[1] vip_enable_switch = item[2] if vip_enable_switch == "enable": detect_vip_availability = read_from_cache_cluster( "vipdevice", "detect-vipaddress-availability", vip_address) if detect_vip_availability == "enable" or detect_vip_availability == "disable": #write_to_cache_cluster("vipdevice","vipaddress-availability",vip_address,detect_vip_availability) vip_avil_dict[vip_address] = detect_vip_availability else: vip_avil_dict[vip_address] = vip_status #write_to_cache_cluster("vipdevice","vipaddress-availability",vip_address,vip_status) else: vip_avil_dict[vip_address] = vip_status #write_to_cache_cluster("vipdevice","vipaddress-availability",vip_address,vip_status) logger.info( "the vip {},the vip_enable_switch is {},the vip_status is {},the detect status is {}" .format( vip_address, vip_enable_switch, vip_status, read_from_cache_cluster("vipdevice", "vipaddress-availability", vip_address))) keys = get_keys_from_cache("vipdevice", "vipaddress-availability") for key in keys: key = str(key, encoding="raw_unicode_escape") if vip_avil_dict.get(key) == None: logger.info("the vip {} is disable".format(key)) delete_to_cache_cluster("vipdevice", "vipaddress-availability", key) for vip, status in vip_avil_dict.items(): write_to_cache_cluster("vipdevice", "vipaddress-availability", vip, status) except Exception as err: logger.error(err)
def load_zone_from_table(): zoneqdns_dict = {} sql = 'select zone_name,record_name,record_content,internet_type,record_type,record_ttl,dns_name from Polaris_tb_fact_backend_dnszone_info left join Polaris_tb_fact_zonetype_info on Polaris_tb_fact_backend_dnszone_info.zone_name_id = Polaris_tb_fact_zonetype_info.id left join Polaris_tb_fact_dnstype_info on Polaris_tb_fact_backend_dnszone_info.dns_type_id = Polaris_tb_fact_dnstype_info.id where zone_status = "enable"' zoneres = my_custom_sql(sql) if zoneres != None and len(zoneres) != 0: zonename_tag = False for zoneitem in zoneres: zone_name = zoneitem[0] record_name = zoneitem[1] record_content = zoneitem[2] internet_type = zoneitem[3] record_type = zoneitem[4] record_ttl = zoneitem[5] dnsname = zoneitem[6] if zoneqdns_dict.get(dnsname) == None: zoneqdns_dict[dnsname] = {} if zoneqdns_dict[dnsname].get(zone_name) == None: zoneqdns_dict[dnsname][zone_name] = [] zoneqdns_dict[dnsname][zone_name].append({ "record_name": record_name, "record_content": record_content, "internet_type": internet_type, "record_type": record_type, "record_ttl": record_ttl }) # zoneqdns_dict[dnsname][zone_name].append("{} {} {} {} {}\n".format(record_name,record_ttl,internet_type,record_type,record_content)) if len(zoneqdns_dict) == 0: logger.info("the zone dict is null") keys = get_keys_from_cache("vipdevice", "zone-config") for key in keys: key = str(key, encoding="raw_unicode_escape") if zoneqdns_dict.get(key) == None: logger.info("the nameid {} is disable".format(key)) delete_to_cache_cluster("vipdevice", "zone-config", key) for dnstype, zoneinfo in zoneqdns_dict.items(): logger.info("the dnstype is {},the zone info is {}".format( dnstype, json.dumps(zoneinfo))) write_to_cache_cluster("vipdevice", "zone-config", str(dnstype), json.dumps(zoneinfo))
def sload_zone_from_table(): zoneqdns_dict = {} objs = tb_fact_nameid_info.objects.all() for obj in objs: nameid = obj.nameid_name status = obj.nameid_status if status != "enable": continue sql = 'select a.dns_type_id,b.dns_name from Polaris_tb_fact_nameid_info a left join Polaris_tb_fact_dnstype_info b on a.dns_type_id = b.id where nameid_name = "{}"'.format( nameid) res = my_custom_sql(sql) if res != None and len(res) != 0: for item in res: id = item[0] dnsname = item[1] sql = 'select zone_name,record_name,record_content,internet_type,record_type,record_ttl from Polaris_tb_fact_dnszone_info left join Polaris_tb_fact_zonetype_info on Polaris_tb_fact_dnszone_info.zone_name_id = Polaris_tb_fact_zonetype_info.id where zone_status = "enable" and dns_type_id = "{}"'.format( id) zoneres = my_custom_sql(sql) if zoneres != None and len(zoneres) != 0: zonename_tag = False for zoneitem in zoneres: zone_name = zoneitem[0] if nameid.find(zone_name) != -1: zonename_tag = True record_name = zoneitem[1] record_content = zoneitem[2] internet_type = zoneitem[3] record_type = zoneitem[4] record_ttl = zoneitem[5] if zoneqdns_dict.get(dnsname) == None: zoneqdns_dict[dnsname] = {} if zoneqdns_dict[dnsname].get(zone_name) == None: zoneqdns_dict[dnsname][zone_name] = [] zoneqdns_dict[dnsname][zone_name].append({ "record_name": record_name, "record_content": record_content, "internet_type": internet_type, "record_type": record_type, "record_ttl": record_ttl }) if len(zoneqdns_dict) == 0 or zonename_tag == False: logger.info( "the nameid zone is not legal,the nameid is {}". format(nameid)) if len(zoneqdns_dict) == 0: logger.info("the zone dict is null") keys = get_keys_from_cache("vipdevice", "zone-config") for key in keys: key = str(key, encoding="raw_unicode_escape") if zoneqdns_dict.get(key) == None: logger.info("the nameid {} is disable".format(key)) delete_to_cache_cluster("vipdevice", "zone-config", key) for dnstype, zoneinfo in zoneqdns_dict.items(): logger.info("the dnstype is {},the zone info is {}".format( dnstype, json.dumps(zoneinfo))) write_to_cache_cluster("vipdevice", "zone-config", str(dnstype), json.dumps(zoneinfo))
def load_device_availability_cache(): try: sql = "select node_isp,total_value,absolute_value,relative_rate from Polaris_tb_fact_detectdeviceavailability_standard_info" res = my_custom_sql(sql) isp_standard_data = {} for item in res: isp = item[0] total_value = item[1] absolute_value = item[2] relative_rate = item[3] if isp_standard_data.get(isp) == None: isp_standard_data[isp] = {} isp_standard_data[isp]["total_value"] = total_value isp_standard_data[isp]["absolute_value"] = absolute_value isp_standard_data[isp]["relative_rate"] = relative_rate sql = "select effective_time,effective_time_unit from Polaris_tb_fact_detecttask_info where detect_name='detect_device_availability'" res = my_custom_sql(sql) effective_time = 15 effective_time_unit = 'second' for item in res: effective_time = item[0] effective_time_unit = item[1] if effective_time == None or effective_time_unit == None: effective_time = 15 effective_time_unit = 'second' #这里group by vip和availability,以vip为key,拿到最终上报的关于该vip的总的探针个数,上报的状态,以及运营商 sql = 'select vip_address,availability,admin_isp,count(availability) from Polaris_tb_fact_detectdeviceavailability_select_info where create_time >= DATE_FORMAT(DATE_ADD(now(), INTERVAL - {} {}),"%y-%m-%d %H:%i:%s") group by vip_address,availability'.format( effective_time, effective_time_unit) res = my_custom_sql(sql) vip_avil_dict = {} for item in res: vip_address = item[0] status = item[1] admin_isp = item[2] num = item[3] #设备的运营商信息以最后一次为准 if vip_avil_dict.get(vip_address) == None: vip_avil_dict[vip_address] = [status, num, admin_isp] elif vip_avil_dict[vip_address][1] < num: vip_avil_dict[vip_address][0] = status vip_avil_dict[vip_address][ 1] = vip_avil_dict[vip_address][1] + num vip_avil_dict[vip_address][2] = admin_isp else: vip_avil_dict[vip_address][ 1] = vip_avil_dict[vip_address][1] + num vip_avil_dict[vip_address][2] = admin_isp detect_vip_status = {} #if vip_avil_dict.get(vip_address) == None or vip_avil_dict[vip_address][1] < num: # vip_avil_dict[vip_address] = [status,num,admin_isp] #开始确定每个vip的状态,对于每个vip都要满足俩个标准,一个是上报的探针个数要达到那个绝对值,还有一个是要达到那个相对值,会有三种状态,对于数据不符合检验标准的,都认为是无效的,对于无效的探测数据,还是要采用手工配置的方式 for item in vip_avil_dict: status = vip_avil_dict[item][0] num = vip_avil_dict[item][1] admin_isp = vip_avil_dict[item][2] if isp_standard_data.get(admin_isp) == None: detect_vip_status[item] = "invalid" #write_to_cache_cluster("vipdevice","detect-vipaddress-availability",item,"invalid") else: total_value = isp_standard_data[admin_isp]["total_value"] absolute_value = isp_standard_data[admin_isp]["absolute_value"] relative_rate = isp_standard_data[admin_isp]["relative_rate"] logger.info( "the vip is {},the total detect alive vip num is {},the standared num is {},the relative percent alive is {},the standared is {}" .format(item, num, absolute_value, num / total_value, relative_rate)) if num < absolute_value or (num / total_value < relative_rate): detect_vip_status[item] = "invalid" #write_to_cache_cluster("vipdevice","detect-vipaddress-availability",item,"invalid") else: detect_vip_status[item] = vip_avil_dict[item][0] #write_to_cache_cluster("vipdevice","detect-vipaddress-availability",item,vip_avil_dict[item][0]) logger.info("the vip {},the detect status is {}".format( item, read_from_cache_cluster("vipdevice", "detect-vipaddress-availability", item))) keys = get_keys_from_cache("vipdevice", "detect-vipaddress-availability") for key in keys: key = str(key, encoding="raw_unicode_escape") if detect_vip_status.get(key) == None: logger.info("the detect vip {} is disable".format(key)) delete_to_cache_cluster("vipdevice", "detect-vipaddress-availability", key) for vip, status in detect_vip_status.items(): write_to_cache_cluster("vipdevice", "detect-vipaddress-availability", vip, status) except Exception as err: logger.error(err)
def gen_default_view(): sql = 'insert into Polaris_tb_fact_temp_view_info(view_father_id,view_default,view_grade_name,view_grade,view_father_grade,view_type_id) values(0,"default","default",1,0,1)' res = my_custom_sql(sql)
def load_view_viewid(): sql = "select id,view_country,view_isp,view_region,view_province,view_city from Polaris_tb_fact_temp_view_info where view_grade= 6" res = my_custom_sql(sql) for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] view_region = line[3] view_province = line[4] view_city = line[5] if view_country!= None and view_isp!= None and view_region!= None and view_province!= None and view_city !=None and view_country!="*" and view_isp!="*" and view_region !="*" and view_province!="*" and view_city !="*": view_id_dict["{}_{}_{}_{}_{}".format(view_country,view_isp,view_region,view_province,view_city)] = view_id sql = "select id,view_country,view_isp,view_region,view_province,view_city from Polaris_tb_fact_temp_view_info where view_grade= 5" res = my_custom_sql(sql) for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] view_region = line[3] view_province = line[4] view_city = line[5] if view_country!= None and view_isp!= None and view_region!= None and view_province!= None and view_country!="*" and view_isp!="*" and view_region !="*" and view_province!="*": if view_isp=="广电网" and view_province=="江苏": print(view_id) view_id_dict["{}_{}_{}_{}".format(view_country,view_isp,view_region,view_province)] = view_id sql = "select id,view_country,view_isp,view_region,view_province,view_city from Polaris_tb_fact_temp_view_info where view_grade= 4" res = my_custom_sql(sql) for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] view_region = line[3] view_province = line[4] view_city = line[5] if view_country!= None and view_isp!= None and view_region!= None and view_country!="*" and view_isp!="*" and view_region !="*": view_id_dict["{}_{}_{}".format(view_country,view_isp,view_region)] = view_id sql = "select id,view_country,view_isp,view_region,view_province,view_city from Polaris_tb_fact_temp_view_info where view_grade= 3" res = my_custom_sql(sql) for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] view_region = line[3] view_province = line[4] view_city = line[5] if view_country!= None and view_isp!= None and view_country!="*" and view_isp!="*": view_id_dict["{}_{}".format(view_country,view_isp)] = view_id sql = "select id,view_country,view_isp,view_region,view_province,view_city from Polaris_tb_fact_temp_view_info where view_grade= 2" res = my_custom_sql(sql) for line in res: view_id = line[0] view_country = line[1] view_isp = line[2] view_region = line[3] view_province = line[4] view_city = line[5] if view_country!= None and view_country!="*": view_id_dict["{}".format(view_country)] = view_id