將IIS Log匯入SQL Server進行資料分析

sqlserver iis log

wanjan_chen 2023-01-12 11:44:24 ‧ 1301 瀏覽

分享至

平常很少有機會去查看IIS Log，但如果收到客戶反映系統效能緩慢時，
還是會確認一下IIS Log的資訊。
雖然IIS Log紀錄的訊息非常詳細，但Log內容難以閱讀是一個蠻大的問題：

尤其是當有需要查看IIS Log時，通常得面對異常大量的訊息內容，
分析像上圖這樣的原始資料是一件非常痛苦的事情....

最近學到可以透過將IIS Log匯入SQL Server中，轉換成容易閱讀的表格的方法～
首先先準備好需要的IIS Log檔案，IIS Log預設的存放位置如下：
C:\inetpub\logs\LogFiles\W3SVC1
為了避免後續執行SQL語法時，因為權限不足無法開啟檔案，
可以將Log檔複製到其他路徑下。

我們可以看到Log檔內容中，Fields這一行是標頭，說明每一個欄位紀錄的內容，
且每一個欄位使用半形空白做分隔

開啟SQL Server Management Studio，選擇目標DB後，
執行以下語法：

--如果IISLOG Table已存在，先刪除掉，重新Create當次需要的Table
--如果有需要保存前次內容，請另外使用其他名稱Create Table
DROP TABLE IF EXISTS dbo.IISLOG 

--Create IISLOG Table，欄位根據Log檔中的Fields建立
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] [VARCHAR](48) NULL,
[sc-substatus] [INT] NULL,
[sc-win32-STATUS] [BIGINT] NULL,
[time-taken] [INT] NULL,
INDEX cci CLUSTERED COLUMNSTORE
)

--用 IISLOG Table的格式，將log檔匯入DB中
BULK INSERT dbo.IISLog
FROM 'D:\IISLog\u_ex220810.log' --Log檔位置
WITH (
CODEPAGE = 65001,  --使用CodePage 65001 (UTF-8 encoding)
FIRSTROW = 4,  --指定檔案的前幾行不是資料內容
FIELDTERMINATOR = ' ',  --資料欄位間的分隔記號
ROWTERMINATOR = '\n', --每一行間的分隔記號
MAXERRORS=10000  
--如果原始資料無法匯入時，ERROR值會+1，當ERROR的數量大於指定數目時，本次作業將取消。
--因為IIS Log有可能會包含多筆不符格式的資料，為了不讓作業被取消，可以指定大一點的數字
--(例如IIS服務重啟，那就會再紀錄一次檔案中前4行資料:#Software/#Version/#Date/#Fields)
)

接著我們到目標DB開啟IISLOG Table後，就可以看到Log的內容以表格的方式呈現了～