import bigquery import dbaasquery import infraquery import datetime import notify DEV_HOST = "gateway01.us-west-2.prod.aws.tidbcloud.com" DEV_PORT = 4000 DEV_USER = "R9ez7VWZfnXH5Lw.nightly_cm" DEV_DATABASE = "nightly_ms_central" DEV_PASSWORD = "UEGJ%EawyvSR8lH_demZUl53" DEV_BILLING_DATABASE = "nightly_ms_billing" STAGING_HOST = "gateway01.us-west-2.prod.aws.tidbcloud.com" STAGING_PORT = 4000 STAGING_USER = "R9ez7VWZfnXH5Lw.staging" STAGING_DATABASE = "staging_central" STAGING_PASSWORD = "ZqfjTO5G_CJx%AdiqFuyVS6P" STAGING_BILLING_DATABASE = "staging_billing" GARDENER_PROJECT = ['1369847559691457744', '1369847559691427623', '1369847559691367750', '1369847559691368078', '1369847559691427545', '1369847559691427623', '1369847559691427556', '1369847559691457585', '1369847559691457683', '1369847559691487332', '1369847559691517289', '1369847559691487359', '1369847559692177294', '73772', '43494', '73986', '74051', '74080', '253467'] def check_region_billing(account_id, start_time, end_time): new_start_time = start_time.strftime('%Y%m%d') new_end_time = end_time.strftime('%Y%m%d') region_map_result = {} result = bigquery.query_region_bills(account_id, new_start_time, new_end_time) for row in result: if row[0] != 'us-west-2' and row[0] != 'us-east-1' and row[0] != 'global' and row[1] > 0.01: region_map_result[row[0]] = row[1] return region_map_result def check_project_billing(account_id, start_time, end_time): new_start_time = start_time.strftime('%Y%m%d') new_end_time = end_time.strftime('%Y%m%d') project_map_result = {} result = bigquery.query_project_bills(account_id, new_start_time, new_end_time) for row in result: if row[0] != 'admin' and row[0] != 'default' and row[0] != '' and row[1] > 0.01: project_map_result[row[0]] = row[1] return project_map_result def check_cluster_billing(account_id, projectid, start_time, end_time): new_start_time = start_time.strftime('%Y%m%d') new_end_time = end_time.strftime('%Y%m%d') cluster_map_result = {} result = bigquery.query_project_cluster_bills(account_id, projectid, new_start_time, new_end_time) for row in result: if row[1] > 0.01: cluster_map_result[row[0]] = row[1] return cluster_map_result def check_project_need_migrate(account_id, start_time, end_time): project_need_migrate = {} leak_project = {} gardener_project = {} project_map_result = check_project_billing(account_id, start_time, end_time) print(project_map_result) for project, bills in project_map_result.items(): print(project) if len(project) == 19: data = dbaasquery.query_org_info(project, STAGING_USER, STAGING_PASSWORD, STAGING_HOST, STAGING_DATABASE, STAGING_PORT) env = "staging" user = STAGING_USER password = STAGING_PASSWORD host = STAGING_HOST database = STAGING_DATABASE port = STAGING_PORT else: data = dbaasquery.query_org_info(project, DEV_USER, DEV_PASSWORD, DEV_HOST, DEV_DATABASE, DEV_PORT) env = "dev" user = DEV_USER password = DEV_PASSWORD host = DEV_HOST database = DEV_DATABASE port = DEV_PORT if len(data) == 0: if "other" in project_need_migrate: project_need_migrate["other"].append(project+':%s' % bills) else: project_need_migrate["other"] =[project+':%s' % bills] else: is_exist, normal_count, hibernated_count = infraquery.is_network_exist_in_infra(env, project) cluster_list = dbaasquery.query_cluster_info(project, user, password, host, database, port, start_time, end_time) print(is_exist, data[0][2]) if (is_exist or len(cluster_list) > 0) and data[0][2] == 'active': if project not in GARDENER_PROJECT: if data[0][0] in project_need_migrate: project_need_migrate[data[0][0]].append(project+':%s' % bills) else: project_need_migrate[data[0][0]] =[project+':%s' % bills] else: if data[0][0] in gardener_project: gardener_project[data[0][0]].append(project+':%s' % bills) else: gardener_project[data[0][0]] =[project+':%s' % bills] else: if data[0][0] in leak_project: leak_project[data[0][0]].append(project+':%s' % bills) else: leak_project[data[0][0]] =[project+':%s' % bills] return project_need_migrate, leak_project, gardener_project def check_project_cluster_leak(account_id, gardener_project, is_shoot, start_time, end_time): eks_need_clean = {} project_need_hibernated = {} cluster_need_clean = [] cluster_need_leak = {} for tenant_id, projects_info in gardener_project.items(): for project_info in projects_info: if tenant_id != 'other': info = project_info.split(":", 1) print(info[0]) if len(info[0]) == 19: user = STAGING_USER password = STAGING_PASSWORD host = STAGING_HOST database = STAGING_DATABASE port = STAGING_PORT env = "staging" else: user = DEV_USER password = DEV_PASSWORD host = DEV_HOST database = DEV_DATABASE port = DEV_PORT env = "dev" is_exist, normal_count, hibernated_count = infraquery.is_network_exist_in_infra(env, info[0]) cluster_map_result = check_cluster_billing(account_id, info[0], start_time, end_time) clusters_info = dbaasquery.query_cluster_info(info[0], user, password, host, database, port, start_time, end_time) hibernated_flag = False cluster_not_exist_flag = False cluster_create_list = [] if len(clusters_info) != 0: for cluster_info in clusters_info: cluster_create_list.append('%s' % cluster_info[0]) if cluster_info[3] == 'None': if (datetime.datetime.now() - cluster_info[2]).hours > 8 and 'keep' not in cluster_info[1]: cluster_need_clean.append(cluster_info[0]) print(cluster_create_list) else: cluster_not_exist_flag = True clusters_info = dbaasquery.query_cluster_info(info[0], user, password, host, database, port, start_time + datetime.timedelta(days=-1), end_time) if len(clusters_info) == 0: hibernated_flag = True for cluster_id, cluster_bills in cluster_map_result.items(): if is_shoot: if cluster_id == '' and cluster_bills > 0.1 and (normal_count + hibernated_count) > 2: eks_need_clean[info[0]] = cluster_bills elif cluster_id == 'admin' and cluster_not_exist_flag and hibernated_flag and cluster_bills > 2.0: project_need_hibernated[info[0]] = [info[1]] elif cluster_id not in cluster_create_list and cluster_id != 'admin': cluster_need_leak[cluster_id] = cluster_bills else: if cluster_id == '' and cluster_not_exist_flag and hibernated_flag and cluster_bills > 0.0: project_need_hibernated[info[0]] = [info[1]] elif cluster_id not in cluster_create_list and cluster_id != '': cluster_need_leak[cluster_id] = cluster_bills elif cluster_id == '' and cluster_bills > (normal_count + hibernated_count) * 14: # need calculate by eks network num eks_need_clean[info[0]] = cluster_bills return eks_need_clean, project_need_hibernated, cluster_need_clean, cluster_need_leak def check_eks_project_num(account_id, project_need_migrate, start_time, end_time): eks_project_list = {} count = 0 for tenant_id, projects_info in project_need_migrate.items(): for project_info in projects_info: if tenant_id != 'other': info = project_info.split(":", 1) cluster_map_result = check_cluster_billing(account_id, info[0], start_time, end_time) for cluster_id, cluster_bills in cluster_map_result.items(): if cluster_id == '' and cluster_bills > 0.0: count += 1 if tenant_id in eks_project_list: eks_project_list[tenant_id].append(info[0] + ':%s' % cluster_bills) else: eks_project_list[tenant_id] = [info[0] + ':%s' % cluster_bills] return eks_project_list, count def dbaas_aws_billing_compare(project_billings, is_shoot, start_time, end_time): new_start_time = start_time.strftime('%Y-%m-%d') new_end_time = end_time.strftime('%Y-%m-%d') tenant_billing_compare = {} project_billing_abnormal = {} for tenant_id, projects_info in project_billings.items(): for project_info in projects_info: if tenant_id != 'other': info = project_info.split(":", 1) if len(info[0]) == 19: user = STAGING_USER password = STAGING_PASSWORD host = STAGING_HOST database = STAGING_BILLING_DATABASE port = STAGING_PORT env = "staging" else: user = DEV_USER password = DEV_PASSWORD host = DEV_HOST database = DEV_BILLING_DATABASE port = DEV_PORT env = "dev" is_exist, normal_count, hibernated_count = infraquery.is_network_exist_in_infra(env, info[0]) dbaas_bills = dbaasquery.query_user_project_bills(tenant_id, info[0], new_start_time, new_end_time, user, password, host, database, port) if dbaas_bills[0][0] is None: dbaas_bill = 0 else: dbaas_bill = float(dbaas_bills[0][0]) if tenant_id in tenant_billing_compare: tenant_billing_compare[tenant_id].append(info[0] + ':aws[%s] dbaas[%s]' % (info[1], dbaas_bill)) else: tenant_billing_compare[tenant_id] = [info[0] + ':aws[%s] dbaas[%s]' % (info[1], dbaas_bill)] if is_shoot: if (float(info[1]) - dbaas_bill) > (normal_count + hibernated_count) * 2: project_billing_abnormal[tenant_id] = info[0] + ':aws[%s] dbaas[%s] normal_count:%s ' \ 'hibernated_count:%s' % (info[1], dbaas_bill, normal_count, hibernated_count) elif (float(info[1]) - dbaas_bill) > (normal_count + hibernated_count) * 14: project_billing_abnormal[tenant_id] = info[0] + ':aws[%s] dbaas[%s] normal_count:%s ' \ 'hibernated_count:%s' % (info[1], dbaas_bill, normal_count, hibernated_count) return tenant_billing_compare, project_billing_abnormal def main(argv): parser = argparse.ArgumentParser() parser.add_argument("-account", dest="account", type=str) # 获取参数 args = parser.parse_args() account_id = args.username time = datetime.datetime.now() + datetime.timedelta(days=-2) print("check time is:%s, check account is:%s" % (time, account_id)) print("begin checking region bills") region_map_result = check_region_billing(account_id, time, time) print("check region bills result is:%s" % region_map_result) print("++++++++++++++++++++++++++++++++") print("begin checking the project need to be migrated to other aws account") project_need_migrate, leak_project, gardener_project = check_project_need_migrate(account_id, time, time) print("Those projects need to be migrated to other aws account:%s" % project_need_migrate) # projects leak 主要是 1. infra 层查询不到 project 2. 那天用户没有创建过集群 3.数据库project 状态为非 active print("Those projects have resource leak:%s" % leak_project) print("++++++++++++++++++++++++++++++++") print("begin checking the gardener clusters resource leak") gardener_eks_need_clean, gardener_project_need_hibernated, gardener_cluster_need_clean, gardener_cluster_need_leak = \ check_project_cluster_leak(account_id, gardener_project, True, time, time) print("Those projects may have abnormal bills:%s" % gardener_eks_need_clean) print("Those projects may need hibernated:%s" % gardener_project_need_hibernated) print("Those clusters need to be deleted:%s" % gardener_cluster_need_clean) print("Those clusters may be have resource leak:%s" % gardener_cluster_need_leak) print("++++++++++++++++++++++++++++++++") print("begin checking the eks clusters resource leak") eks_need_clean, project_need_hibernated, cluster_need_clean, cluster_need_leak = \ check_project_cluster_leak(account_id, project_need_migrate, False, time, time) print("Those projects may have abnormal bills:%s" % eks_need_clean) print("Those projects may need hibernated:%s" % project_need_hibernated) print("Those clusters need to be deleted:%s" % cluster_need_clean) print("Those clusters may be have resource leak:%s" % cluster_need_leak) print("++++++++++++++++++++++++++++++++") print("begin checking the total eks project") eks_project_list, count = check_eks_project_num(account_id, project_need_migrate, time, time) print("Total eks count:%s, detail:%s" % (count, eks_project_list)) print("++++++++++++++++++++++++++++++++") print("begin comparing eks aws and dbaas bills") tenant_billing_compare, project_billing_abnormal = dbaas_aws_billing_compare(project_need_migrate, False, time, time) print("Those projects may have abnormal bills:%s, all tenant detail:%s" % (project_billing_abnormal, tenant_billing_compare)) print("++++++++++++++++++++++++++++++++") print("begin comparing gardener aws and dbaas bills") gardener_tenant_billing_compare, gardener_project_billing_abnormal = dbaas_aws_billing_compare(gardener_project, True, time, time) print("Those projects may have abnormal bills:%s, all tenant detail:%s" % (gardener_tenant_billing_compare, gardener_project_billing_abnormal)) print("+++++++++++++++++Reusult Summary+++++++++++++++") print("check region bills result is:%s" % region_map_result) print("++++++++++++++++region bills++++++++++++++++") print("Those projects need to be migrated to other aws account:%s" % project_need_migrate) # projects leak 主要是 1. infra 层查询不到 project 2. 那天用户没有创建过集群 3.数据库project 状态为非 active print("Those projects have resource leak:%s" % leak_project) print("+++++++++++++++gardener clusters resource leak+++++++++++++++++") print("Those projects may have abnormal bills:%s" % gardener_eks_need_clean) print("Those projects may need hibernated:%s" % gardener_project_need_hibernated) print("Those clusters need to be deleted:%s" % gardener_cluster_need_clean) print("Those clusters may be have resource leak:%s" % gardener_cluster_need_leak) print("+++++++++++++++eks clusters resource leak+++++++++++++++++") print("Those projects may have abnormal bills:%s" % eks_need_clean) print("Those projects may need hibernated:%s" % project_need_hibernated) print("Those clusters need to be deleted:%s" % cluster_need_clean) print("Those clusters may be have resource leak:%s" % cluster_need_leak) print("+++++++++++++++total eks project+++++++++++++++++") print("Total eks count:%s, detail:%s" % (count, eks_project_list)) print("+++++++++++++++eks aws and dbaas bills+++++++++++++++++") print("Those projects may have abnormal bills:%s, all tenant detail:%s" % (project_billing_abnormal, tenant_billing_compare)) print("+++++++++++++++gardener aws and dbaas bills+++++++++++++++++") print("Those projects may have abnormal bills:%s, all tenant detail:%s" % (gardener_tenant_billing_compare, gardener_project_billing_abnormal)) notify.send_msg(region_map_result, project_need_migrate, leak_project, gardener_eks_need_clean, gardener_project_need_hibernated, gardener_cluster_need_clean, gardener_cluster_need_leak, eks_need_clean, project_need_hibernated, cluster_need_clean, cluster_need_leak, count, eks_project_list, project_billing_abnormal, tenant_billing_compare, gardener_tenant_billing_compare, gardener_project_billing_abnormal) if __name__ == "__main__": main(sys.argv[1:])