import os import json from aliyunsdkcore.client import AcsClient from aliyunsdkecs.request.v20140526.DescribeInstancesRequest import DescribeInstancesRequest from model import InstanceList,IntfaceList # from DBSession import session_maker from model_longvideo import produce_video_task from DBSession_longvideo import session_maker_longvideo from aliyun.log.logclient import LogClient from aliyun.log.getlogsrequest import GetLogsRequest import requests import time import pymysql from sqlalchemy.sql import func from model_longvideo import produce_video_project client = AcsClient('LTAI4GBWbFvvXoXsSVBe1o9f', 'kRAikWitb4kDxaAyBqNrmLmllMEDO3', 'cn-hangzhou') def intances_list_update(): request = DescribeInstancesRequest() request.set_accept_format('json') request.set_PageSize(100) request.set_InstanceNetworkType("vpc") request.set_Tags([ { "Key": "ecs" } ]) response = client.do_action_with_exception(request) instance_info = json.loads(response) intances_list_del() print(len(instance_info["Instances"]["Instance"])) for i in range(len(instance_info["Instances"]["Instance"])): instance_id = instance_info["Instances"]["Instance"][i]["InstanceId"] ipaddr = instance_info["Instances"]["Instance"][i]["VpcAttributes"]["PrivateIpAddress"]["IpAddress"][0] server_name = instance_info["Instances"]["Instance"][i]["Tags"]["Tag"][0]["TagValue"] status = instance_info["Instances"]["Instance"][i]["Status"] instance_name = instance_info["Instances"]["Instance"][i]["HostName"] if status == "running": status = 1 instance_insert(instance_id, ipaddr, instance_name, server_name, status) return "OK" def intances_list_del(): with session_maker() as session: session.query(InstanceList).delete() def instance_insert(instance_id, ipadd, instance_name, server_name, status): InstanceInfo = None InstanceInfo = InstanceList() with session_maker() as session: InstanceInfo.instance_id = instance_id InstanceInfo.ipadd = ipadd InstanceInfo.instance_name = instance_name InstanceInfo.server_name = server_name InstanceInfo.status = status session.add(InstanceInfo) def healthcheck(ipaddr,server_name): """返回容器upload时间，容器运行状态，健康坚持接口返回值，""" svc_name = server_name print(server_name) if svc_name == "longvideoapi.prod" : url = "http://{}:8080/longvideoapi/test".format(ipaddr) res = requests.post(url, timeout=5) print(res.status_code) return res.status_code if svc_name == "commonapi.prod": url = "http://{}:8080/commonapi/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code if svc_name == "speed.prod": url = "http://{}:8080/longvideoapi/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code if svc_name == "clipapi.prod": url = "http://{}:8080/longvideoapi/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code if svc_name == "distribution.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code if svc_name == "manager.prod": url = "http://{}:8971/dev/test/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code if svc_name == "recommend-queue.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code # def healthcheck_metirc(instance_id,ipaddr,server_name,http_code): # healthcheck_status = Gauge("healthcheck_status", "http_code", ['instance_id', 'server_name', 'ipaddress'], # registry=registry) # # healthcheck_status.labels(instance_id, server_name, ipaddr).set(http_code) # # return Response(generate_latest(registry),mimetype="text/plain") def count_qps(appType, url): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' logstore = 'request-log' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-longvideoapi' topic = "" start_time = int(time.time()) end_time = start_time - 60 req = GetLogsRequest(project, logstore, end_time, start_time, topic, "params.appType={} and requestUri= {} |select requestUri,count(1) as cnt, date_format(logTimestamp/1000,'%Y-%m-%d %H:%i:%S') as date group by requestUri, date order by cnt desc limit 1".format(appType, url) , 30, 0, False) try: res = client.get_logs(req) except : qps = 0 else: if res.body: qps = res.body[0]["cnt"] else: qps = 0 print(int(time.time())-start_time) return qps def count_avg_time(appType ,url): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' logstore = 'request-log' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-longvideoapi' topic = "" start_time = int(time.time()) end_time = start_time - 60 # url = '/longvideoapi/user/idolsUpdated' req = GetLogsRequest(project, logstore, end_time, start_time, topic, "params.appType=%s and requestUri=%s |select requestUri,round(avg(expendTime)) as avgs,count(1) as cnt group by requestUri " %(appType, url), 30, 0, False) try: res = client.get_logs(req) except Exception as e: avgs = 0 else: if res.body: avgs = res.body[0]["avgs"] else: avgs = 0 return avgs def slb_status_code_count(server_name): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' client = LogClient(endpoint, accessKeyId, accessKey) if server_name == "longvideoapi": project = 'longvideoapi-slb-log' logstore = 'slb-access-log' if server_name == "speed": project = 'speed-slb-log' logstore = 'access-log' if server_name == "clip": project = 'clips-slb-log' logstore = 'access-log' if server_name == "commonapi": project = 'commonapi-slb-log' logstore = 'slb-access-log' topic = "" end_time = int(time.time()) start_time = end_time - (end_time - time.timezone)%86400 # url = '/longvideoapi/user/idolsUpdated' req = GetLogsRequest(project, logstore, start_time, end_time, topic, "* |SELECT status ,COUNT(*) as cnt group by status" , 30, 0, False) try: res = client.get_logs(req) except Exception as e: avgs = 0 else: if res.body: return res.body def produce_video_task_status_count(): with session_maker_longvideo() as session: video_progress_count = session.query(produce_video_task).filter(produce_video_task.task_status == 1).count() video_success_count = session.query(produce_video_task).filter(produce_video_task.task_status == 2).count() video_fail_count = session.query(produce_video_task).filter(produce_video_task.task_status == 3).count() end_time = int(time.time()) start_time = end_time - (end_time - time.timezone) % 86400 res = session.query(produce_video_task).filter(produce_video_task.task_status==2).order_by(produce_video_task.id.desc()).first() duration = res.duration submit_timestamp = res.submit_timestamp complete_timestamp = res.complete_timestamp print(res.id,duration,submit_timestamp,complete_timestamp) rate = (duration / (complete_timestamp - submit_timestamp) / 1000) def logs_tts_count(tts_channel, tts_status): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-monitor' logstore = 'monitor-log' topic = "" end_time = int(time.time()) start_time = end_time - (end_time - time.timezone)%86400 req = GetLogsRequest(project, logstore, start_time, end_time, topic, "* and __topic__: producetts and channel:{} and produceStatus:{}| select channel,produceStatus,count(*) as count group by channel,produceStatus".format(tts_channel, tts_status) , 30, 0, False) try: res = client.get_logs(req) except Exception as e: avgs = 0 else: if res.body: return res.body def count_recommend_null(): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' logstore = 'app-recommend-log' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-longvideoapi' topic = "" start_time = int(time.time()) #监控获取时间间隔300s end_time = start_time - 300 query_sql = "* and requestUri:'/longvideoapi/video/distribute/category/videoList/v2' | select count(DISTINCT json_extract(params, '$.machineCode')) as cnt where json_array_length(json_extract(result, '$.data')) = 0" req = GetLogsRequest(project, logstore, end_time, start_time, topic, query_sql, 30, 0, False) try: res = client.get_logs(req) except Exception as e: print(e) avgs = 0 else: if res.body: cnt_300 = res.body[0]["cnt"] else: cnt_300 = 0 return cnt_300 def db_query(sql): HOST = 'rr-bp1x9785e8h5452bi157.mysql.rds.aliyuncs.com' PORT = '3306' DATABASE = 'longvideo' USERNAME = 'devops' PASSWORD = 'devops@123456' conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8") cursor = conn.cursor() cursor.execute(sql) res = cursor.fetchone() return res def count_qps_avgtime(appType): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' logstore = 'request-log' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-longvideoapi' topic = "" start_time = int(time.time()) end_time = start_time - 60 # url = '/longvideoapi/user/idolsUpdated' req = GetLogsRequest(project, logstore, end_time, start_time, topic, "params.appType=%s | select requestUri,count(1) as cnt , avg(expendTime) as avg_time group by requestUri order by requestUri,cnt desc limit 1000" %(appType), 30, 0, False) try: res = client.get_logs(req) except Exception as e: avgs = 0 return res def count_rt_less_time_count(appType, than, less): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' logstore = 'request-log' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-longvideoapi' topic = "" start_time = int(time.time()) end_time = start_time - 60 # url = '/longvideoapi/user/idolsUpdated' req = GetLogsRequest(project, logstore, end_time, start_time, topic, "params.appType=%s and expendtime >%s and expendtime <%s| select requestUri,count(1) as cnt group by requestUri order by requestUri,cnt desc limit 1000" %(appType,than,less), 30, 0, False) try: res = client.get_logs(req) except Exception as e: avgs = 0 return res def app_openapi_qps_avgtime_count(): endpoint = 'cn-hangzhou.log.aliyuncs.com' accessKeyId = 'LTAIWYUujJAm7CbH' accessKey = 'RfSjdiWwED1sGFlsjXv0DlfTnZTG1P' logstore = 'request-log' client = LogClient(endpoint, accessKeyId, accessKey) project = 'wqsd-longvideoapi' topic = "" start_time = int(time.time()) end_time = start_time - 60 url = '/longvideoapi/openapi/video/distribute/category/videoList/v2' req = GetLogsRequest(project, logstore, end_time, start_time, topic, "requestUri=%s| select requestUri,count(1) as cnt , avg(expendTime) as avg_time group by requestUri order by requestUri,cnt desc limit 1000"%(url) , 30, 0, False) try: res = client.get_logs(req) except Exception as e: avgs = 0 return res,url if __name__ == '__main__': # slb_status_code_count("clip") # healthcheck_result = [] # with session_maker() as session: # instance_infos = session.query(InstanceList).filter(InstanceList.status==0).all() # for index in range(len(instance_infos)): # ipaddr = instance_infos[index].ipadd # server_name = instance_infos[index].server_name # http_code = healthcheck(ipaddr,server_name) # result = {"ipaddr": ipaddr, "server_name": server_name, "http_code": http_code} # healthcheck_result.append(result) # print(healthcheck_result) # produce_video_task_status_count() # res = logs_tts_count("aliyun",1) # if res[0]["count"]: # print(res[0]["count"]) # res = logs_tts_count("aliyun", 0) # if res[0]["count"]: # print(res[0]["count"]) # res = logs_tts_count("azure", 1) # if res[0]["count"]: # print(res[0]["count"]) # res = logs_tts_count("aliyun", 0) # if res[0]["count"]: # print(res[0]["count"]) # res = count_recommend_nu # res = count_qps_avgtime(appType=0) # print(res.body) # for i in range(len(res.body)): # url = res.body[i]["requestUri"] # # qps = res.body[i]["cnt"] # avgtime = round(float(res.body[i]["avg_time"]), 2) # print(url,qps ,avgtime) query_sql = ("select round((successCount + processingCount1)/totalCount * 100,2) from " "(select count(*) as totalCount," "sum(case when produce_status in (5,6,7,8) then 1 else 0 end) as successCount," "sum(case when produce_status = 99 then 1 else 0 end) as failCount , " "sum(case when produce_status in(0,1,2,3,4) then 1 else 0 end) as processingCount," "sum(case when produce_status in(0,1,2,3,4) and (rate < 1 or rate is null) then 1 else 0 end) processingCount1," "sum(case when produce_status in(0,1,2,3,4) and rate >= 1 then 1 else 0 end) processingCount2 from " "(select t1.project_id, t1.produce_status, round((t2.last_connect_timestamp - t1.submit_timestamp) / (t1.video_duration/1000), 1) as rate from produce_video_project t1 " "left join produce_video_project_connect_time t2 on t1.project_id = t2.project_id " "where t1.project_id > '20210804000000000111111' and t1.project_id < '20210805000000000111111' and t1.app_type not in (1,13,15)) s1) ss1" ) res = db_query(query_sql) print(res)