基於python實現cdn紀錄檔檔案匯入mysql進行分析

2022-05-16 16:00:12

一、本文需求背景

週六日出現CDN大量請求，現需要分析其請求頻次與來源，查詢是否存在被攻擊問題。

本文以阿里雲CDN紀錄檔作為輔助查詢資料，其它雲平臺大同小異。

系統提供的離線紀錄檔如下所示：

二、需求落地如下

紀錄檔範例如下所示：

[9/Jun/2015:01:58:09 +0800] 10.10.10.10 - 1542 "-" "GET http://www.aliyun.com/index.html" 200 191 2830 MISS "Mozilla/5.0 (compatible; AhrefsBot/5.0; +http://example.com/robot/)" "text/html"

其中相關欄位的解釋如下：

[9/Jun/2015:01:58:09 +0800]：紀錄檔開始時間。
10.10.10.10：存取IP。
-：代理IP。
1542：請求響應時間，單位為毫秒。
"-"： HTTP請求頭中的Referer。
GET：請求方法。
http://www.aliyun.com/index.html：使用者請求的URL連結。
200：HTTP狀態碼。
191：請求大小，單位為位元組。
2830：請求返回大小，單位為位元組。
MISS：命中資訊。
- HIT：使用者請求命中了CDN邊緣節點上的資源（不需要回源）。
- MISS：使用者請求的內容沒有在CDN邊緣節點上快取，需要向上遊獲取資源（上游可能是CDN L2節點，也可能是源站）。
Mozilla/5.0（compatible; AhrefsBot/5.0; +http://example.com/robot/）：User-Agent請求頭資訊。
text/html：檔案型別。

按照上述欄位說明建立一個 MySQL 表，用於後續通過 Python 匯入 MySQL 資料，欄位可以任意定義

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for ll
-- ----------------------------
DROP TABLE IF EXISTS `ll`;
CREATE TABLE `ll`  (
  `id` int(11) NOT NULL,
  `s_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pro_ip` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dura_time` int(11) NULL DEFAULT NULL,
  `referer` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `method` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `code` int(255) NULL DEFAULT NULL,
  `size` double NULL DEFAULT NULL,
  `res_size` double NULL DEFAULT NULL,
  `miss` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `ua` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `html_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

下載全部紀錄檔之後，使用 Python 批次匯入資料庫中，解析程式碼如下，在提前開始前需要先看一下待提取的每行資料內容。

[11/Mar/2022:00:34:17 +0800] 118.181.139.215 - 1961 "http://xx.baidu.cn/" "GET https://cdn.baidu.com/video/1111111111.mp4" 206 66 3739981 HIT "Mozilla/5.0 (iPad; CPU OS 15_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/15E148 SP-engine/2.43.0 main%2F1.0 baiduboxapp/13.5.0.10 (Baidu; P2 15.1) NABar/1.0" "video/mp4"

初看之下，我們會使用空格進行切片，例如下述程式碼：

import os
# 獲取檔名
my_path = r"C:紀錄檔目錄"
file_names = os.listdir(my_path)
file_list = [os.path.join(my_path, file) for file in file_names]
for file in file_list:
    with open(file, 'r', encoding='utf-8') as f:
        lines = f.readlines()
        for i in lines:
            item_list = i.split(' ')
            s_time = item_list[0]+' '+item_list[1]
            ip = item_list[2],
            pro_ip =item_list[3],
            dura_time =item_list[4],
            referer =item_list[5],
            method =item_list[6],
            url = item_list[7],
            code =item_list[8],
            size =item_list[9],
            res_size =item_list[10],
            miss =item_list[11],
            html_type =item_list[12]
            print(s_time,ip,pro_ip,dura_time,referer,method,url,code,size,res_size,miss,html_type)

執行之後，會發現裡面的開始時間位置，UA位置都存在空格，所以該方案捨棄，接下來使用正規表示式提取。

參考待提取的模板編寫正規表示式如下所示：

[(?<time>.*?)] (?<ip>d{1,3}.d{1,3}.d{1,3}.d{1,3}) (?<pro_ip>.*?) (?<dura_time>d+) "(?<referer>.*?)" "(?<method>.*?) (?<url>.*?)" (?<code>d+) (?<size>d+) (?<res_size>d+) (?<miss>.*?) "(?<ua>.*?)" "(?<html_type>.*?)"

接下來進行迴圈讀取資料，然後進行提取：

import os
import re
import pymysql
# 獲取檔名
my_path = r"C:紀錄檔資料夾"
file_names = os.listdir(my_path)
file_list = [os.path.join(my_path, file) for file in file_names]
wait_list = []
for file in file_list:
    with open(file, 'r', encoding='utf-8') as f:
        lines = f.readlines()
        for i in lines:
            pattern = re.compile(
                '[(?P<time>.*?)] (?P<ip>d{1,3}.d{1,3}.d{1,3}.d{1,3}) (?P<pro_ip>.*?) (?P<dura_time>d+) "(?P<referer>.*?)" "(?P<method>.*?) (?P<url>.*?)" (?P<code>d+) (?P<size>d+) (?P<res_size>d+) (?P<miss>.*?) "(?P<ua>.*?)" "(?P<html_type>.*?)"')
            gs = pattern.findall(i)
            item_list = gs[0]
            s_time = item_list[0]
            ip = item_list[1]
            pro_ip = item_list[2]
            dura_time = item_list[3]
            referer = item_list[4]
            method = item_list[5]
            url = item_list[6]
            code = item_list[7]
            size = item_list[8]
            res_size = item_list[9]
            miss = item_list[10]
            ua = item_list[11]
            html_type = item_list[12]
            values_str = f"('{s_time}', '{ip}', '{pro_ip}', {int(dura_time)}, '{referer}', '{method}', '{url}', {int(code)}, {int(size)}, {int(res_size)}, '{miss}', '{ua}','{html_type}')"
            wait_list.append(values_str)

讀取到資料儲存到 wait_list 列表中，然後操作列表，寫入MySQL，該操作為了防止SQL語句過長，所以每次間隔1000元素進行插入。

def insert_data():
    for i in range(0,int(len(wait_list)/1000+1)):
        items = wait_list[i * 1000:i * 1000 + 1000]
        item_str = ",".join(items)
        inser_sql = f"INSERT INTO ll(s_time, ip, pro_ip, dura_time, referer, method, url,code, size, res_size, miss, ua,html_type) VALUES {item_str}"
        db = pymysql.connect(host='localhost',
                             user='root',
                             password='root',
                             database='logs')
        cursor = db.cursor()
        try:
            cursor.execute(inser_sql)
            db.commit()
        except Exception as e:
            # print(content)
            print(e)
            db.rollback()

最終的結果如下所示：

匯入MySQL之後，就可以按照自己的需求進行排序與查詢了。

三、自定義查詢

可以通過 refer 計算請求次數：

select count(id) num,referer from ll GROUP BY referer ORDER BY num desc

到此這篇關於基於python實現cdn紀錄檔檔案匯入mysql進行分析的文章就介紹到這了,更多相關cdn紀錄檔匯入mysql內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

基於python實現cdn紀錄檔檔案匯入mysql進行分析

目錄

一、本文需求背景

二、需求落地如下

三、自定義查詢

熱門文章