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
