import base64 import datetime import hmac import json import os import time from hashlib import sha256 import yaml from kubernetes import client, config import pymysql import requests feishu_webhook = "https://open.feishu.cn/open-apis/bot/v2/hook/5b6e2051-ac9b-4dbb-a6a6-5a135701cd52" def get_personal_users(): users = [] config.load_incluster_config() apiClient = client.CoreV1Api() namespaces = apiClient.list_namespace() tidbClient = client.CustomObjectsApi() for ns in namespaces.items: tidbclusters = tidbClient.list_namespaced_custom_object( group="pingcap.com", version="v1alpha1", namespace=ns.metadata.name, plural="tidbclusters", ) if len(tidbclusters['items']) > 0: users.append(ns.metadata.name) return users def get_root_password(user): config.load_incluster_config() apiClient = client.CoreV1Api() secrets = apiClient.list_namespaced_secret(user) for secret in secrets.items: if "tidb-secret" in secret.metadata.name: password = apiClient.read_namespaced_secret(secret.metadata.name, user) password = password.data["root"] password= base64.b64decode(password) return password.decode() def post_bot_webhook(post_infos): if len(post_infos) == 0: return url = feishu_webhook payload_message = { "timestamp": str(int(time.time())), "msg_type": "interactive", "card": { "i18n_elements": { "en_us": [ { "tag": "div", "text": { "tag": "lark_md", "content": "**测试环境使用超过8小时的集群** {}\n".format( datetime.datetime.today().strftime("%Y-%m-%d")), }, "fields": post_infos, }, ] } } } headers = { 'Content-Type': 'application/json' } response = requests.request( "POST", url, headers=headers, data=json.dumps(payload_message)) print(response.text) def process_staging_environments(post_infos): users = ['dev', 'staging', 'stability', 'louis2', 'yinliang3', 'topsql', 'liyilong', 'gweber', 'slowquery', 'xuyifan02', 'jyzdev10', 'rajeshwer', 'hanlins'] host = 'tidb.e23bdccc.cb17aaf2.us-west-2.prod.aws.tidbcloud.com' envClusters = {} for user in users: id = '' created_at = datetime.datetime.now() name = '' tenant_id = '' status = '' tenant_name = 'unknown' tenant_email = 'unknown' creator = 'unknown' alertTidbs = [] try: db = pymysql.connect(host=host, port=4000, user='root', password=os.getenv('passwd'), db=user + "_central", charset='utf8') cursor = db.cursor() cursor.execute("select * from clusters where status in ('normal', 'scaling') and cluster_profile_id not in (select id from cluster_profiles where name like '%dev');") results = cursor.fetchall() for row in results: id = row[0] created_at = row[1] name = row[4] tenant_id = row[5] status = row[12] creator_id = row[6] cursor.execute("select * from tenants where id = {}".format(tenant_id)) results = cursor.fetchall() if len(results) == 0: continue tenant_name = results[0][3] tenant_email = results[0][5] creator = "" cursor.execute("select * from users where id = {}".format(creator_id)) results = cursor.fetchall() if len(results) == 0: continue creator = "{} {}".format(results[0][6], results[0][5]) alertTidbs.append({ 'tenantName': tenant_name, 'tenantEmail': tenant_email, 'id': id, 'created_at': created_at, 'name': name, 'status': status, 'tenant_id': tenant_id, 'creator': creator, }) except Exception as e: print("pass", user, e) alertTidbs.append({ 'tenantName': tenant_name, 'tenantEmail': tenant_email, 'id': id, 'created_at': created_at, 'name': name, 'status': status, 'tenant_id': tenant_id, 'creator': creator, }) envClusters[user] = alertTidbs for env in envClusters: alertTidbs = envClusters[env] for tidb in alertTidbs: created_date = tidb['created_at'] if datetime.datetime.now() - created_date > datetime.timedelta(hours=8): post_infos.append( { "is_short": True, "text": { "tag": "lark_md", "content": "环境: {} \n 租户名：{} \n租户邮箱：{} \n" "租户id: {} \n" "集群名：{} \n" "集群id: {} \n" "创建者： {}\n集群创建时间：{} \n\n\n\n-------------------\n\n\n\n". format(env, tidb['tenantName'], tidb['tenantEmail'], tidb['tenant_id'], tidb['name'], tidb['id'], tidb['creator'], tidb['created_at'], tidb['status']) } }, ) return post_infos def process_personal_environments(): users = get_personal_users() envClusters = {} for user in users: alertTidbs = [] try: passwd = get_root_password(user) print("process {} {}".format("db-tidb." + user, passwd)) db = pymysql.connect(host= "db-tidb." + user, port=4000, user='root', password=passwd, db='dbaas', charset='utf8') cursor = db.cursor() cursor.execute("select * from clusters where status in ('normal', 'scaling') and cluster_profile_id not in (select id from cluster_profiles where name like '%dev');") results = cursor.fetchall() for row in results: id = row[0] created_at = row[1] name = row[4] tenant_id = row[5] status = row[12] creator_id = row[6] cursor.execute("select * from tenants where id = {}".format(tenant_id)) results = cursor.fetchall() if len(results) == 0: continue tenant_name = results[0][3] tenant_email = results[0][4] cursor.execute("select * from users where id = {}".format(creator_id)) results = cursor.fetchall() if len(results) == 0: continue creator = "{} {}".format(results[0][6], results[0][5]) alertTidbs.append({ 'tenantName': tenant_name, 'tenantEmail': tenant_email, 'id': id, 'created_at': created_at, 'name': name, 'tenant_id': tenant_id, 'creator': creator, }) except: print("pass it") envClusters[user] = alertTidbs post_infos = [] post_infos.append( { "is_short": False, "text": { "tag": "lark_md", "content": "" } }, ) for env in envClusters: alertTidbs = envClusters[env] for tidb in alertTidbs: created_date = tidb['created_at'] if datetime.datetime.now() - created_date > datetime.timedelta(hours=8): post_infos.append( { "is_short": True, "text": { "tag": "lark_md", "content": "环境: {} \n 租户名：{} \n租户邮箱：" "{} \n租户id: {} " "\n集群名：" "{} \n集群id: " "{} \n创建者： {}\n集群创建时间：{} \n\n\n\n-------------------\n\n\n\n".format(env, tidb['tenantName'], tidb['tenantEmail'], tidb['tenant_id'], tidb['name'], tidb['id'], tidb['creator'], tidb['created_at']) } }, ) return post_infos def process(): post_infos = process_personal_environments() post_infos = process_staging_environments(post_infos) post_bot_webhook(post_infos) process()