获取昨天的慢查询SQL自动创建 Jira 的子任务

在获取前一天 MySQL 慢查的情况下，进一步做告警（https://www.cnblogs.com/klvchen/articles/13677698.html）
通过 xlsx 获取到 sqlhash，SQL 等字段。

创建路径

mkdir -p /data/slow_sqlhash4jira/
cd /data/slow_sqlhash4jira/
mkdir base_image images 

代码

cd /data/slow_sqlhash4jira/images

app.py

from openpyxl import load_workbook
import datetime
import os
import dingtalk
import jira_util

# 需要修改的配置文件
#xls_patch = "d:\\Documents\\xls\\"

xls_patch = "/export_xlsx/"

oneday = datetime.timedelta(days=1)

today = datetime.date.today()
yesterday = today - oneday

parent_key = 'FCYP-9086'


def get_xls_filename():
    filename = xls_patch + "rm-wz9lzm065d7c4b4a0_mysql_slow_sql_" + str(yesterday) + ".xlsx"
    # print(filename)
    return filename


def get_xls_info(filename):
    if os.path.exists(filename):
        workbook = load_workbook(filename)

        sheet = workbook.active

        row_length = sheet.max_row

        sum = 0

        for i in range(2, row_length + 1):
            sql_cell = sheet['F' + str(i)].value
            sql_hash_cell = sheet['I' + str(i)].value
            sql_db_name = sheet['O' + str(i)].value
            sql_max_execute_time = sheet['H' + str(i)].value
            sql_execute_count = sheet['M' + str(i)].value
            sql_max_parse_count = sheet['G' + str(i)].value
            sql_max_return_count = sheet['E' + str(i)].value
            description = "库名: {} \n最大执行时间: {} 秒 \n解析SQL最大行数: {}\n返回SQL总行数: {}\n执行次数: {}\nSQL: \n{}".format(sql_db_name, sql_max_execute_time, sql_max_parse_count, sql_max_return_count, sql_execute_count, sql_cell)

            result = jira_util.search_and_create_task(sql_hash_cell, description, parent_key)
            if result:
               sum += 1

        if sum > 0:
            dingtalk.send_msg(sum)
    else:
        print("xlsx 不存在")


if __name__ == '__main__':
    # get_redis()
    filename = get_xls_filename()
    get_xls_info(filename)

dingtalk.py

import json
import requests
# 需要自己替换 token
url = 'https://oapi.dingtalk.com/robot/send?access_token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxd464'


def send_msg(msg):
    parameter = {
        "msgtype": "text",
        "text": {
            "content": "云服务 -- 昨天新增线上MySQL慢查数量 %s ,已添加到 Jira ~" % msg
        },
    }
    headers = {
        'Content-Type': 'application/json'
    }
    requests.post(url, data=json.dumps(parameter), headers=headers)

jira_util.py

from jira import JIRA
# 通过jira域名和账户密码登录,需要自己替换用户名和账号
jira = JIRA('http://jira.bokevip.com/', basic_auth=('xxxxxxxx', 'xxxxxxxx'))


# 通过 JQL 查找
# 标签为 "SQL慢查询",
# 解决结果:未解决
# 经办人: None
def search_task():
    temp_list = []
    mysql_slow = jira.search_issues('project=FCYP and Labels=SQL慢查询  and resolution = null and assignee = null')
    for issue in mysql_slow:
        temp_list.append(issue.fields.summary)
    return temp_list


# 创建子任务 issue
def create_sub_task(summary, description, parent_key):

    issue_dict = {
        'project': {'key': 'FCYP'},
        'summary': summary,
        'description': description,
        'labels': ['SQL慢查询'],
        'issuetype': {'name': 'Sub-task'},
        'parent': {'key': parent_key}
    }
    new_issue = jira.create_issue(fields=issue_dict)


# 判断是否需要创建子任务
def search_and_create_task(summary, description, parent_key):
    slow_task_list = search_task()
    if summary not in slow_task_list:
        create_sub_task(summary, description, parent_key)
        return 1
    return 0


if __name__ == '__main__':
    # summary = '74b7f594f61f6f2dd14a13d71c997ed6'
    # description = 'Look into this one'
    # parent_key = 'FCYP-9086'
    #create_sub_task(summary, description, parent_key)
    slow_task_list = search_task()
    print(slow_task_list)

创建基础镜像

cd base_image

# 配置项目依赖
vi requirements.txt 
certifi==2020.12.5
chardet==4.0.0
et-xmlfile==1.0.1
idna==2.10
jdcal==1.4.1
openpyxl==3.0.5
redis==3.5.3
requests==2.25.1
urllib3==1.26.2
jira==2.0.0

# 创建 Dockerfile
vi Dockerfile 
FROM python:3.6
WORKDIR /data

RUN echo "Asia/Shanghai" > /etc/timezone
RUN cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
COPY requirements.txt ./
RUN pip install -r requirements.txt -i https://mirrors.aliyun.com/pypi/simple/

docker buid -t slow_sqlhash_base4jira:20210119.1 .

创建业务镜像

cd /data/slow_sqlhash4jira/images

vi Dockerfile 
FROM slow_sqlhash_base4jira:20210119.1
WORKDIR /data

COPY . ./


docker build -t slow_sqlhash4jira:0.1 .

创建 docker-compose

cd /data/slow_sqlhash4jira

vi docker-compose.yml 
version: '3.4'
services:
  slow_sqlhash4jira: 
    image: slow_sqlhash4jira:0.4
    command: python app.py 
    #command: sleep 3600
    volumes:
      - /data/export_xlsx:/export_xlsx

# 启动
docker-compose up -d

效果：