import datetime import json import time import requests import os import subprocess import pymysql host = "tidb.e23bdccc.cb17aaf2.us-west-2.prod.aws.tidbcloud.com" passwd = os.getenv('passwd') databases = ["dev_central", 'staging_central', "yinliang3_central", "nizheming_central"] webhook= 'https://open.feishu.cn/open-apis/bot/v2/hook/72a1432d-978a-43f5-9ef3-fde440fee823' deleting_projects = [] long_lived_projects = [] personal_environment_projects = [] databased_projects = {} def find_victim_projects(): projects = {} for dbName in databases: db = pymysql.connect(host=host, port=4000, user='root', password=passwd, db=dbName, charset='utf8') cursor = db.cursor() cursor.execute("select id,name from projects where status = 'active' and id != 1") results = cursor.fetchall() for row in results: id = row[0] name = row[1] projects[id] = name for project_id in projects.keys(): empty_project = False if project_id == 1 and dbName in ("dev_central", 'staging_central'): continue userEmails = {} cursor.execute("select id,deleted_time,status from clusters where project_id={} " "and created_at > NOW() - INTERVAL 2 DAY" .format(project_id)) results = cursor.fetchall() if len( results ) == 0: cursor.execute("select user_id,project_id from user_projects where project_id={}".format(project_id)) results = cursor.fetchall() for row in results: cursor.execute("select first_name,last_name, email from users where id={}".format(row[0])) userResults = cursor.fetchall() for user in userResults: userEmails[user[0] + '-' + user[1] ] = user[2] envName = '' if dbName.startswith('dev'): envName = 'nightly' elif dbName.startswith('staging'): envName = 'staging' elif dbName.startswith('yinliang3'): envName = 'yinliang3' elif dbName.startswith('nizheming'): envName = 'nizheming' print(envName + '-' + str(project_id)) databased_projects[ envName + '-' + str(project_id) ] = userEmails return databased_projects def fetch_dev_infra_network_json(): cmd = "KUBECONFIG=dev-infra-apiserver /usr/local/bin/kubectl get network -A -o json" inputDomain = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE) jsonS = json.dumps(inputDomain.communicate()[0].decode('utf-8')) return json.loads(jsonS) def fetch_staging_infra_network_json(): cmd = "KUBECONFIG=staging-infra-apiserver /usr/local/bin/kubectl get network -A -o json" inputDomain = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE) jsonS = json.dumps(inputDomain.communicate()[0].decode('utf-8')) return json.loads(jsonS) def process_infra_network(): pendingNetworks = [] rawData = fetch_dev_infra_network_json() networks = json.loads(rawData) for network in networks['items']: if network['spec']['provider'] != 'aws-free-tier': networkName = network['metadata']['name'] networkNamespace = network['metadata']['namespace'] networkPhase = network['status']['phase'] deletionTimestamp = None shootName = None if 'fromProvider' in network['status']: if 'shootName' in network['status']['fromProvider']: shootName = network['status']['fromProvider']['shootName'] if shootName is not None: if 'deletionTimestamp' in network['metadata']: deletionTimestamp = network['metadata']['deletionTimestamp'] pendingNetworks.append({ 'namespace': networkNamespace, 'shoot': shootName, 'status': networkPhase, 'deletionTimestamp': deletionTimestamp, }) rawData = fetch_staging_infra_network_json() networks = json.loads(rawData) for network in networks['items']: if network['spec']['provider'] != 'aws-free-tier': networkName = network['metadata']['name'] networkNamespace = network['metadata']['namespace'] deletionTimestamp = None shootName = None networkPhase = network['status']['phase'] if 'fromProvider' in network['status']: if 'shootName' in network['status']['fromProvider']: shootName = network['status']['fromProvider']['shootName'] if shootName is not None: if 'deletionTimestamp' in network['metadata']: deletionTimestamp = network['metadata']['deletionTimestamp'] pendingNetworks.append({ 'namespace': networkNamespace, 'shoot': shootName, 'status': networkPhase, 'deletionTimestamp': deletionTimestamp, }) return pendingNetworks def filter_deleting_projects( networks ): deleting_projects = [] for network in networks: if network['status'] == 'PendingDelete' or \ network['status'] == 'Deleting': deleting_projects.append( network['namespace'] ) return deleting_projects def filter_e2e_projects( networks): e2e_projects = [] for network in networks: if 'e2e-' in network['namespace'] : deleting_projects.append( network['namespace'] ) return e2e_projects def filter_the_victim_projects(projects, networks): victiom_projects = [] for project in projects: for network in networks: if project == network['namespace']: victiom_projects.append(project) return victiom_projects def post_deletingnetworks_to_feishu( deletingNetworks): headers = { 'Content-Type': 'application/json' } networks = list( set(deletingNetworks) ) payload_message = { "timestamp": str(int(time.time())), "msg_type": "interactive", "card": { "i18n_elements": { "en_us": [ { "tag": "div", "text": { "tag": "lark_md", "content": "**Need To Delete Projects**" }, "fields": [ { "is_short": False, "text": { "tag": "lark_md", "content": "" } }, { "is_short": True, "text": { "tag": "lark_md", "content": "未能正确被删除的 Project: {}".format( '\n'.join(networks) ) } }, ] }, ] } } } response = requests.request( "POST", webhook, headers=headers, data=json.dumps(payload_message)) print(response.text) def post_victim_projects_to_feishu(victim_projects): headers = { 'Content-Type': 'application/json' } projects = list(set(victim_projects)) payload_message = { "timestamp": str(int(time.time())), "msg_type": "interactive", "card": { "i18n_elements": { "en_us": [ { "tag": "div", "text": { "tag": "lark_md", "content": "**Need To Delete Projects**" }, "fields": [ { "is_short": False, "text": { "tag": "lark_md", "content": "" } }, { "is_short": True, "text": { "tag": "lark_md", "content": "长时间未运行TiDB集群的 Project: {}".format( '\n'.join(projects) ) } }, ] }, ] } } } response = requests.request( "POST", webhook, headers=headers, data=json.dumps(payload_message)) print(response.text) def post_e2e_projects_to_feishu( e2e_projects ): headers = { 'Content-Type': 'application/json' } projects = list( set(e2e_projects) ) payload_message = { "timestamp": str(int(time.time())), "msg_type": "interactive", "card": { "i18n_elements": { "en_us": [ { "tag": "div", "text": { "tag": "lark_md", "content": "**Need To Delete Projects**" }, "fields": [ { "is_short": False, "text": { "tag": "lark_md", "content": "" } }, { "is_short": True, "text": { "tag": "lark_md", "content": "残存 E2E Project: {}".format( '\n'.join(projects) ) } }, ] }, ] } } } response = requests.request( "POST", webhook, headers=headers, data=json.dumps(payload_message)) print(response.text) databased_projects = find_victim_projects() pendingNetworks = process_infra_network() victim_projects = filter_the_victim_projects(databased_projects, pendingNetworks) e2e_projects = filter_e2e_projects(pendingNetworks) deletingNetworks = filter_deleting_projects(pendingNetworks) print('process done!') print(databased_projects) print(victim_projects) print(e2e_projects) print(deletingNetworks) post_victim_projects_to_feishu(victim_projects) post_e2e_projects_to_feishu(e2e_projects) post_deletingnetworks_to_feishu(deletingNetworks)