Export IIS Log to SQL Server

Did some research on how to analyse IIS logs. Desktop software doesn’t work with 700M log files. Instead I found LogParser did a better job by exporting the log to SQL server database. It’s then flexible to generate any kind of report.


Step 1. Create SQL Server database:

CREATE TABLE [dbo].[iislog](
[LogFilename] [varchar](255) NULL,
[RowNumber] [int] NOT NULL,
[EntryTime] [datetime] NULL,
[ServerIpAddress] [varchar](255) NULL,
[Method] [varchar](255) NOT NULL,
[UriStem] [varchar](255) NOT NULL,
[UriQuery] [varchar](255) NOT NULL,
[Port] [int] NULL,
[Username] [varchar](255) NULL,
[ClientIpAddress] [varchar](255) NULL,
[UserAgent] [varchar](255) NULL,
[HttpStatus] [int] NULL
)

Step 2. Create PowerShell script:

$ErrorActionPreference = “Stop”

function ImportLogFiles(
[string] $httpLogPath)
{
If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
{
Throw “The log path must be specified.”
}

[string] $logParser = “${env:ProgramFiles(x86)}” `
+ “\Log Parser 2.2\LogParser.exe”

[string] $query = `
“SELECT” `
+ ” LogFilename” `
+ “, RowNumber” `
+ “, TO_TIMESTAMP(date, time) AS EntryTime” `
+ “, s-ip AS ServerIpAddress” `
+ “, cs-method AS Method” `
+ “, cs-uri-stem AS UriStem” `
+ “, cs-uri-query AS UriQuery” `
+ “, s-port AS Port” `
+ “, cs-username AS Username” `
+ “, c-ip AS ClientIpAddress” `
+ “, cs(User-Agent) AS UserAgent” `
+ “, sc-status AS HttpStatus” `
+ ” INTO Intranet” `
+ ” FROM $httpLogPath\*.log”

[string] $connectionString = “Driver={SQL Native Client};Server=myserver;Database=mydatabase;Trusted_Connection=yes;”

[string[]] $parameters = @()

$parameters += $query
$parameters += “-i:W3C”
$parameters += “-o:SQL”
$parameters += “-oConnString:$connectionString”

Write-Debug “Parameters: $parameters”

Write-Host “Importing log files to database…”
& $logParser $parameters
}

function Main
{
[string] $httpLogPath = “C:\iislog”

ImportLogFiles $httpLogPath

Write-Host -Fore Green “Successfully imported log files.”
}

Main