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