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 datetime import pymysql from sqlalchemy.sql import func from model_longvideo import produce_video_project from datetime import date, timedelta from DBSession import session_maker client = AcsClient('LTAI4GBWbFvvXoXsSVBe1o9f', 'kRAikWitb4kDxaAyBqNrmLmllMEDO3', 'cn-hangzhou') from prometheus_client import Gauge,Counter, generate_latest 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 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 elif svc_name == "commonapi.prod": url = "http://{}:8080/commonapi/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "speed.prod": url = "http://{}:8080/longvideoapi/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "clipapi.prod": url = "http://{}:8080/longvideoapi/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "distribution.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "manager.prod": url = "http://{}:8971/dev/test/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "recommend-queue.prod": url = "http://{}:8080".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "recommend-api.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "recommend-rov.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "measure-queue.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "message-queue.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "search-material.prod": url = "http://{}:8089/v1/search/test".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "spider.prod": url = "http://{}:9990/version".format(ipaddr) try: res = requests.post(url, timeout=5) except Exception as e: return -1 return res.status_code elif svc_name == "video-job.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "combine-job.prod": url = "http://{}:8080/healthcheck".format(ipaddr) res = requests.post(url, timeout=5) return res.status_code elif svc_name == "recommend-server.prod": url = "http://{}:8080/healthcheck".format(ipaddr) # res = requests.post(url, timeout=5) return 200 else: return 200 # 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 db_query_devlop(sql): HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com' PORT = '3306' DATABASE = 'devops' 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.fetchall() return res def db_query_prod(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_avg_max(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*15) # url = '/longvideoapi/user/idolsUpdated' req = GetLogsRequest(project, logstore, end_time, start_time, topic, "params.appType=%s | select requestUri,count(1) as cnt , max(expendTime) as max_time ,round(avg(expendTime),2) 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: print(e) return res def error_cnt(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 req = GetLogsRequest(project, logstore, end_time, start_time, topic, "params.appType=%s and (resultCode:-111 or resultCode:-990 or resultCode:-994 or resultCode:-666 or resultCode:-222)| SELECT COUNT(*) as cnt, resultCode group by resultCode" %(appType), 30, 0, False) try: res = client.get_logs(req) except Exception as e: print(e) 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 def produce_video_data(): start_time = (int(time.strftime("%Y%m%d", time.localtime()))) * 1000000000000000 end_time = (int(time.strftime("%Y%m%d", time.localtime())) + 1) * 1000000000000000 # start_time = (int(datetime.strftime("%Y%m%d",datetime.date.today())) print(start_time, end_time) sum_sql = ("select count(*) as totalCount " "from produce_video_project t1 " " where t1.project_id > %s and t1.project_id < %s and t1.app_type not in (1,13,15) "% (start_time,end_time) ) res = db_query(sum_sql) return def update_request_url_list(): HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com' PORT = '3306' DATABASE = 'devops' USERNAME = 'devops' PASSWORD = 'devops@123456' conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8") cursor = conn.cursor() print("5m定时任务开始") appType = ['0', '4', '5', '6', '12', '15'] for index in range(len(appType)): type = appType[index] res = count_avg_max(type) for i in range(len(res.body)): url = res.body[i]["requestUri"] cnt = res.body[i]["cnt"] max_time = res.body[i]["max_time"] avg_time = res.body[i]["avg_time"] sql = "UPDATE `reuqest_url_list` set `qps`=%s, `max_time`=%s, `avg_time`=%s WHERE `request_url` ='%s' and `apptype`=%s" % ( cnt, max_time, avg_time, url, type) cursor.execute(sql) conn.commit() for i in range(len(res.body)): url = res.body[i]["requestUri"] cnt = res.body[i]["cnt"] sql = "UPDATE `reuqest_url_list` set `rt_1`=%s WHERE `request_url` ='%s' and `apptype`=%s" % ( cnt, url, type) cursor.execute(sql) conn.commit() res = count_rt_less_time_count(type, 200, 500) for i in range(len(res.body)): url = res.body[i]["requestUri"] cnt = res.body[i]["cnt"] sql = "UPDATE `reuqest_url_list` set `rt_2`=%s WHERE `request_url` ='%s' and `apptype`=%s" % ( cnt, url, type) cursor.execute(sql) conn.commit() res = count_rt_less_time_count(type, 500, 1000) for i in range(len(res.body)): url = res.body[i]["requestUri"] cnt = res.body[i]["cnt"] sql = "UPDATE `reuqest_url_list` set `rt_3`=%s WHERE `request_url` ='%s' and `apptype`=%s" % ( cnt, url, type) cursor.execute(sql) conn.commit() res = count_rt_less_time_count(type, 1000, 10000) for i in range(len(res.body)): url = res.body[i]["requestUri"] cnt = res.body[i]["cnt"] sql = "UPDATE `reuqest_url_list` set `rt_4`=%s WHERE `request_url` ='%s' and `apptype`=%s" % ( cnt, url, type) cursor.execute(sql) conn.commit() def produce_video_task_cnt(): start_time = int(time.strftime("%Y%m%d", time.localtime())) * 1000000000000000 end_time = int(time.strftime("%Y%m%d%H%M", time.localtime())) * 100000000000 query_sql = ("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(5,6,7,8) and (rate < 0.5 or rate is null) then 1 else 0 end) processingCount1," "sum(case when produce_status in(5,6,7,8) and rate >= 0.5 and rate < 0.7 then 1 else 0 end) processingCount2 ," "sum(case when produce_status in(5,6,7,8) and rate >= 0.7 and rate < 1 then 1 else 0 end) processingCount3 ," "sum(case when produce_status in(5,6,7,8) and rate >= 1 and rate < 1.5 then 1 else 0 end) processingCount4 ," "sum(case when produce_status in(5,6,7,8) and rate >= 1.5 and rate < 2 then 2 else 0 end) processingCount5 ," "sum(case when produce_status in(5,6,7,8) and rate >2 then 1 else 0 end) processingCount6 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 > %s and t1.project_id < %s and t1.app_type not in (1,13,15)) s1 " %(start_time ,end_time) ) res = db_query(query_sql) return res def produce_video_ratio_cnt(): HOST = 'rm-bp1k5853td1r25g3n690.mysql.rds.aliyuncs.com' PORT = '3306' DATABASE = 'devops' USERNAME = 'devops' PASSWORD = 'devops@123456' conn = pymysql.connect(host=HOST, user=USERNAME, password=PASSWORD, database=DATABASE, charset="utf8") cursor = conn.cursor() start_time = int((date.today() + timedelta(days=-1)).strftime("%Y%m%d")) * 1000000000000000 # end_time = int(date.today().strftime("%Y%m%d")) * 1000000000000000 end_time = int((date.today() + timedelta(days=0)).strftime("%Y%m%d")) * 1000000000000000 sql = ("select count(*) as totalCount, " "sum(case when rate < 0.5 then 1 else 0 end) as r1," "sum(case when (rate >= 0.5 and rate < 0.7) then 1 else 0 end) as r2," "sum(case when (rate >= 0.7 and rate < 1) then 1 else 0 end) as r3," "sum(case when (rate >= 1 and rate < 1.5) then 1 else 0 end) as r4," "sum(case when (rate >= 1.5 and rate < 2) then 1 else 0 end) as r5," "sum(case when rate >= 2 then 1 else 0 end) as r6 from " "(select project_id, round( (produce_done_timestamp - submit_timestamp) / (video_duration/1000),1) as rate from " "(select project_id, draftbox_id,video_duration,submit_timestamp,produce_done_timestamp from " "(select project_id, draftbox_id,video_duration,submit_timestamp,produce_done_timestamp from " "produce_video_project where project_id > %s and project_id < %s and app_type not in (1,13,15) and produce_status in (5,6,7,8) order by video_duration desc limit 100000) t1 group by draftbox_id) t2) t3"%(start_time, end_time) ) y_date = (date.today() + timedelta(days=-1)).strftime("%Y%m%d") res = db_query(sql) if res[0]: cnt = res[0] else: cnt = 0 if res[1]: r1 = res[1] else: r1 = 0 if res[2]: r2 = res[2] else: r2 = 0 if res[3]: r3 = res[3] else: r3 = 0 if res[4]: r4 = res[4] else: r4 = 0 if res[5]: r5 = res[5] else: r5 = 0 if res[6]: r6 = res[6] else: r6 = 0 sql = ("insert into produce_video_ratio (`cnt`,`r1`,`r2`,`r3`,`r4`,`r5`,`r6`,`date`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"%(cnt, r1, r2, r3, r4, r5, r6, y_date) ) cursor.execute(sql) conn.commit() # if __name__ == '__main__':