exporter-ex/utils.py

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)