使用python爬取ELK数据，监控并保存至MySql

想要通过python写一个可以监控ELK中账号注册的失败率，每过一段时间更新一次数据库中的账号，查找超过4个小时没有注册的账号。通过搬砖，从其他博主那里参考借鉴，基本完成功能，具体代码如下：

# -*- coding:utf-8 -*-

from bs4 import BeautifulSoup
import re
import pymysql
from elasticsearch import Elasticsearch
import requests
import csv
import os
import numpy as np
import datetime
import pandas as pd
from pymysql import connect
from dateutil.parser import parse

now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
old = (datetime.datetime.now() - datetime.timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
diff = (datetime.datetime.now() - datetime.timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
# print(old)
# print(now)
# print(diff)
es = Elasticsearch(hosts="http://124.251.74.70:9200/")#筛选目的网址
# print(es.info)

query_json = {
    “kibana控制台筛选语句”
}

#拿到数据
query = es.search(index="bj-sip_register", body=query_json, scroll='1m')
# print(query["hits"]['hits'])
results = query['hits']['hits']  # es查询出的结果第一页
# print(results)
databases = 'ouyu'
table = 'ouyu'
table_out = 'out_time'
db = pymysql.connect("HOST", user='root', passwd='root123', charset='utf8')  # 连接数据库

#定义操作类
class DBHelper:
    def __init__(self):  # 初始化
        self.db = pymysql.connect("HOST", user='root', passwd='root123', charset='utf8')
        self.cursor = self.db.cursor(cursor=pymysql.cursors.DictCursor)
        self.cursor.execute("CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARSET utf8 COLLATE utf8_general_ci;".format(databases))
        self.db.select_db(databases)
        self.cursor.execute("CREATE TABLE if NOT EXISTS {}(ouyu_number varchar(20), reg_time varchar(50), action_time varchar(50))".format(table))
        self.cursor.execute(" DROP TABLE if EXISTS {}".format(table_out))
        self.cursor.execute("CREATE TABLE {}(ouyu_number varchar(20), reg_time varchar(50))".format(table_out))
        self.db.commit()

    # 执行modify(修改)相关操作
    def execute_modify_mysql(self, sql, parm=None):  # 实现一个插入,修改
        self.cursor.execute(sql, parm)
        data = self.cursor.fetchall()
        self.db.commit()
        return data

    # 执行modify(修改)相关操作
    def execute_modify_many_mysql(self, sql, parm=None):  # 实现多个插入,修改
        self.cursor.executemany(sql, parm)
        data = self.cursor.fetchall()
        self.db.commit()
        return data
    
    def __del__(self):  # 结束
        self.cursor.close()
        self.db.close()

SQL = DBHelper()
x = SQL.execute_modify_mysql('select ouyu_number from {}'.format(table))
# print(x)
#判断数据库中是否有本次拿到的数据
if x:
    for each in results:
        numbers = each["_source"]['from']
        reg_time = each["_source"]['time']
        # print(numbers, reg_time)
        r = SQL.execute_modify_mysql('''select * from {} where ouyu_number like "{}";'''.format(table, numbers))
        if r:
            sql = "update {} set  reg_time=%s, action_time=%s WHERE ouyu_number like %s".format(table)
            SQL.execute_modify_mysql(sql, (reg_time, now, numbers))
        else:
            sql = "insert into {} (ouyu_number, reg_time, action_time) VALUE (%s, %s, %s) ".format(table)
            SQL.execute_modify_mysql(sql, (numbers, reg_time, now))
else:
    for each in results:
        numbers = each['_source']['from']
        reg_time = each["_source"]['time']
        # print(numbers, reg_time)
        r = SQL.execute_modify_mysql('''select * from {} where ouyu_number like "{}";'''.format(table, numbers))
        if r:
            sql = "update {} set  reg_time=%s, action_time=%s WHERE ouyu_number like %s".format(table)
            SQL.execute_modify_mysql(sql, (reg_time, now, numbers))
        else:
            sql = "insert into {} (ouyu_number, reg_time, action_time) VALUE (%s, %s, %s) ".format(table)
            SQL.execute_modify_mysql(sql, (numbers, reg_time, now))

old_time = SQL.execute_modify_mysql('''select reg_time from {};'''.format(table))
#判断数据库是否为空 
for time in old_time :
    d1 = datetime.datetime.strptime(now, '%Y-%m-%d %H:%M:%S')
    d2 = datetime.datetime.strptime(time["reg_time"], '%Y-%m-%d %H:%M:%S')
    d3 = datetime.datetime.strptime(diff, '%Y-%m-%d %H:%M:%S')
    delta_rand= d1 - d2
    delta_fix = d1 - d3
    if delta_rand >= delta_fix :
        # print("out_time")
        L = SQL.execute_modify_mysql('''select ouyu_number , reg_time from {} where reg_time like "{}";'''.format(table, time["reg_time"]))

        for row in L :
            row_number = row["ouyu_number"]
            row_time = row ["reg_time"]

            # print(row_time)
            # print(row_number)
            r = SQL.execute_modify_mysql('''select * from {} where ouyu_number like "{}";'''.format(table_out, row_number))
            if r:
                sql = "update {} set  reg_time=%s WHERE ouyu_number like %s".format(table_out)
                SQL.execute_modify_mysql(sql, (row_time, row_number))
            else:
                sql = "insert into {} (ouyu_number, reg_time) VALUE (%s, %s) ".format(table_out)
                SQL.execute_modify_mysql(sql, (row_number, row_time))
    else:pass

测试得到的结果基本符合要求，网上大多博客都不太符合要求，所以直接写一个来完成该功能。可以继续拓展监控功能，例如将信息推送到微信或者公众号。