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

2021-02-24

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

說明

如果 Logs 在伺服器端已經先壓縮處理，可以先透過以下 PowerShell 使用 Expand-Archive 進行解壓縮：

$folderPath = "D:\Log_Parse\Zip"
$destinationFolder = "D:\Log_Parse\UnZip"

# 先移除目的端的 log 避免重複名稱問題
Remove-Item -Path "D:\Log_Parse\UnZip\*.log"

# Get a list of all ZIP files in the folder
$zipFiles = Get-ChildItem -Path $folderPath -Filter *.zip

# Loop through each ZIP file and extract its contents
foreach ($zipFile in $zipFiles) {
    $dest = Join-Path -Path $destinationFolder -ChildPath $zipFile.BaseName
    Expand-Archive -Path $zipFile.FullName -DestinationPath $destinationFolder -Force
}

首先於資料庫建立資料表，建立指定如下，其中包括使用COLUMNSTORE Index，讓資料的寫入效率大為增加並且節省空間，帥啊！

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] (2048) 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
)

log 當中的 #fields 可以輔助確認所建立的 Table Schema 是否符合 (因為 IIS Logs 可以客製紀錄的欄位範圍)，而在 insert 資料階段，這些 # 開頭的資料列可以移除，以下透過 PowerShell 輔助移除，移除後的版本檔案名稱尾墜加上 _clean。

$logsPath = "D:\logs"
$logs = Get-ChildItem -Path $logsPath -Filter *.log

foreach ($log in $logs) {
    $cleanLogPath = [IO.Path]::Combine($log.DirectoryName, ($log.BaseName + "_clean.log"))
    Get-Content $log.FullName -Encoding UTF8 |
    Where-Object { $_ -notmatch '^#' } |
    Set-Content $cleanLogPath -Encoding UTF8
}

使用平行處理的方式：

$logsPath = "D:\logs"
$logs = Get-ChildItem -Path $logsPath -Filter *.log

# Using runspaces for parallel processing
$runspacePool = [runspacefactory]::CreateRunspacePool(1, [Environment]::ProcessorCount)
$runspacePool.Open()

$scriptblock = {
    param($log)
    $cleanLogPath = [IO.Path]::Combine($log.DirectoryName, ($log.BaseName + "_clean.log"))
    $reader = [System.IO.StreamReader]::new($log.FullName)
    $writer = [System.IO.StreamWriter]::new($cleanLogPath)

    while ($line = $reader.ReadLine()) {
        if ($line -notmatch '^#') {
            $writer.WriteLine($line)
        }
    }

    $reader.Close()
    $writer.Close()
}

$jobs = foreach ($log in $logs) {
    $powershell = [powershell]::Create().AddScript($scriptblock).AddArgument($log)
    $powershell.RunspacePool = $runspacePool
    [PSCustomObject]@{
        Pipe = $powershell
        Status = $powershell.BeginInvoke()
    }
}

foreach ($job in $jobs) {
    $job.Pipe.EndInvoke($job.Status)
    $job.Pipe.Dispose()
}

$runspacePool.Close()
$runspacePool.Dispose()

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

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

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

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

如果有大量的 log 要匯入，可以使用 PowerShell 來協助製作匯入 Script 的批次匯入：

# Step 1: Specify the folder path
$folderPath = "D:\Log_Parse\"

# Step 2: Define a multiline template for the text
$template = @"
BULK INSERT dbo.IISLog
  FROM '{0}'
  WITH (
  CODEPAGE = 65001,
  FIELDTERMINATOR = ' ',
  ROWTERMINATOR = '\n'
)`n`n
"@

# Step 3: Initialize an empty string to store the combined text
$combinedText = ""

# Step 4: Loop over files in the folder
Get-ChildItem *clean.log -Path $folderPath | ForEach-Object {
    $file = $_

    # Check if it's a file (not a folder)
    if ($file.PSIsContainer -eq $false) {
        # Generate text based on the template and append it to the combinedText
        $text = $template -f $file.Name
        $combinedText += $text
    }
}

# Step 5: Create a single output .txt file for all combined text
$outputFilePath = Join-Path -Path $folderPath -ChildPath "bulk_insert_scripts.txt"
$combinedText | Out-File -FilePath $outputFilePath -Encoding UTF8

完成後即可以利用 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