阿里云日志服务添加监控并通过webhook写入数据库

1.背景

线上存在接口调用频繁的现象，疑似刷单，现把调用接口过多的用户及ip存入数据库，当做风控管理。

2.日志处理

目的：通过userId和ip来聚合接口（url），统计这个接口一分钟被调用的次数

日志示例

查询聚合语句

* and 'userId'| SELECT regexp_extract_all(content, 'userId=\d{6}') as userid,regexp_extract_all(content, 'ip=\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}') as ip,count_if(content like '%/api/xxxxx/xxx/xxxxx%') as count_api GROUP BY userid,ip ORDER BY count_api DESC limit 3

解释：

regexp_extract_all：找到匹配的字符串

as ：重命名

count_if：从content字段统计满足指定条件的日志条数

limit：限制前三个

统计结果示例

保存为告警

3.webhook编写

CREATE TABLE `black_list` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `userid` varchar(255) DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `count` varchar(255) DEFAULT NULL,
  `conditions` varchar(500) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

#!/usr/bin/python
# -*- coding: utf-8 -*-
import json
import re
import pymysql
import requests
from flask import request, Flask  # flask模块


# Flask通用配置
app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False


def GetData(): # 接收数据
    postdata = request.get_data()  # 获取POST请求的原始数据
    return postdata

@app.route('/webhook-dhaisudhahdashdee', methods=['POST'])
def FormatDate():
    data = GetData()
    a = data.decode().replace('[', '').replace(']', '').replace(' \n\n ', ',').replace('"ip=', '').replace('"userId=',
                                                                                                        '').replace(
        '",userid', ',userid').replace(',count_api', ',count_api').replace('""', ']"').replace('内容":"',
                                                                                               '内容":"[').replace(
        '",count_api', ',count_api')  #解码并进行格式化
    count_list = []
    ip_list = []
    userid_list = []
    try:
        b = json.loads(a)
        c = b['告警内容']
        count_api = re.findall(r'count_api:\w+', c)
        ip = re.findall('ip:\w+.\w+.\w+.\w+', c)
        userid = re.findall(r'userid:\w+', c)
        for i in count_api:
            count_list.append(i.split(':')[1])
        for i in ip:
            ip_list.append(i.split(':')[1])
        for i in userid:
            userid_list.append(i.split(':')[1])
        for i in range(0,3):
            if int(count_list[i]) >500:
                result_id = CloudFlareBlockIp(ip_list[i])
                SaveDate(userid_list[i],ip_list[i],b['国家'],count_list[i],b['接口'],b['触发条件'],result_id)

        return "OK"
    except TypeError:
        print("参数错误")

def SaveDate(userid,ip,country,count,api,conditions,result_id):  #保存数据至mysql
    conn = pymysql.connect(host="10.0.9.18", user="root", passwd="123123aa~", db="blacklist")
    cursor = conn.cursor()
    sql = "insert into black_list (userid,ip,country,count,api,conditions,result_id) values('%s','%s','%s','%s','%s','%s','%s')" %(userid,ip,country,count,api,conditions,result_id)
    cursor.execute(sql)
    cursor.close()
    conn.commit()
    conn.close()
def CloudFlareBlockIp(ip):
    url = "https://api.cloudflare.com/client/v4/zones/xxxxxxxxxxxxx/firewall/access_rules/rules"
    headers = {"X-Auth-Email": "xxxxxxx@hunxxxxxxxx.com", "X-Auth-Key": "xxxxxxxxxxxxxx",
               "Content-Type": "application/json"}
    data = {"mode": "block", "configuration": {"target": "ip", "value": ip}}
    proxies = {"http": None, "https": None}
    req = requests.post(url, headers=headers, data=json.dumps(data), timeout=300, proxies=proxies)
    req = json.loads(req.text)
    result_id = req['result']['id']
    return result_id

if __name__ == '__main__':
    app.run(debug=False, host='10.0.3.189', port=8251)

删除Cloudflare里黑名单的ip，（ip在两个小时候解禁）

import requests
import pymysql


def RemoveCloudflareBlockIp(result_id):
    headers = {"X-Auth-Email": "xxxxxxx@xxxxxxxxxxxoup.com", "X-Auth-Key": "xxxxxxxxxxxxxxxxxxxxx",
               "Content-Type": "application/json"}
    proxies = {"http": None, "https": None}
    data = {"cascade":"none"}
    url = 'https://api.cloudflare.com/client/v4/zones/xxxxxxxxxxxx/firewall/access_rules/rules/'+ result_id
    requests.delete(url=url,headers=headers,data=data,proxies=proxies)
def GetAllData():
    conn = pymysql.connect(host="10.0.9.18", user="root", passwd="123123aa~", db="blacklist")
    cursor = conn.cursor()
#查找大于2小时且大于三小时的数据
    sql = "SELECT result_id FROM `black_list` where time_to_sec(TIMEDIFF(NOW(), create_time))>7200 and time_to_sec(TIMEDIFF(NOW(), create_time))<10800"
    try:
        cursor.execute(sql)
        result = cursor.fetchall()
        for row in result:
            RemoveCloudflareBlockIp(row[0])
        cursor.close()
        conn.commit()
    except:
        print('Error: unable to fecth data')
    conn.close()


if __name__ == '__main__':
    GetAllData()

最后的最后，把上面删除Cloudflare里黑名单的脚本放入crontab里每小时跑一下。

* */1 * * * python3 /usr/local/webhook/remove-cf-ip.py