使用 SQL Server 匯入 IIS Logs 進行分析


  1. 說明
    1. 比較 Logs Parser / IIS Logs Parser Studio
  2. 參考資料

筆記如何將 IIS 網頁伺服器所產生的 logs files 匯入 SQL Server 資料表中,用以分析相關資訊。

SQL Server Logo

說明

首先於資料庫建立資料表,建立指定如下,其中包括使用叢集索引,讓資料的寫入效率大為增加並且節省空間,帥啊!

DROP TABLE IF EXISTS dbo.IISLOG
CREATE TABLE dbo.IISLOG (
[DATE] [DATE] NULL,
[TIME] [TIME] NULL,
[s-ip] [VARCHAR] (48) NULL,
[cs-method] [VARCHAR] (8) NULL,
[cs-uri-stem] [VARCHAR] (255) NULL,
[cs-uri-query] [VARCHAR] (2048) NULL,
[s-port] [VARCHAR] (4) NULL,
[s-username] [VARCHAR] (256) NULL,
[c-ip] [VARCHAR] (48) NULL,
[cs(User-Agent)] [VARCHAR] (1024) NULL,
[cs(Referer)] [VARCHAR] (4096) NULL,
[sc-STATUS] [INT] NULL,
[sc-substatus] [INT] NULL,
[sc-win32-STATUS] [BIGINT] NULL,
[time-taken] [INT] NULL,
INDEX cci CLUSTERED COLUMNSTORE
)

接著再將 logs 使用 Bulk Insert 匯入資料表

BULK INSERT dbo.IISLog
  FROM 'D:\Data\Documents\u_ex161214.log'
  WITH (
  CODEPAGE = 65001,
  FIRSTROW = 2,
  FIELDTERMINATOR = ' ',
  ROWTERMINATOR = '\n'
)

其中 CODEPAGE 的部分要記得設置,否則繁體中文環境的會誤判 IIS Logs 中的資料,造成無法正確匯入。

⚠️ SQL Server 2016 (13.x) 之前的 version 不支援 CodePage 65001 (UTF-8 encoding)。

完成後即可以利用 IIS Logs 檢視

比較 Logs Parser / IIS Logs Parser Studio

雖然 SQL Server 需要匯入才能做檢視,而 Logs Parser 僅需載入檔案即可使用較為方便,但使用 T-SQL 能夠更大的操作彈性,因此取捨就看使用者的習慣。

Log Parser 工具在手 紀錄我有 | IIS W3C Logs 分析指南

參考資料

Import and analyze IIS Log files using SQL Server

Log Parser Studio 2.0 is now available