IIS Logs to SQL Database using Powershell and Log Parser

Our IIS logs are ridiculously large, every time I am asked to find out some stats it takes me ages copying the files to my machine and running some log parser SQL on it.

So I thought it’s time to insert them into a SQL Database. I adapted a script which I mostly found here and used the following to do the import. At first I was getting the following error which I couldn’t figure out how to solve SQL table column “csUsername” date type is not compatible with SELECT clause item “csUsername” (type INTEGER)

PowershellSqlerror

This was being caused because some of the log files had the username as +12345678 or 12345678+ therefore no longer being an INTEGER and not being able to be imported.

To fix this look at line 27 of the following Powershell script to force csUsername to be a VARCHAR rather than an INTEGER

$ErrorActionPreference = "Stop"

Import-Module Pscx -EA 0

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 = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
            + ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=<Database Server>;Database=<Database>;Trusted_Connection=yes;"

    [string[]] $parameters = @()

    $parameters += $query
    $parameters += "-i:W3C"
    $parameters += "-e:-1"
    $parameters += "-o:SQL"
    $parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function Main
{
    $httpLogPath = "<Path to your logs>\*.log"

    ImportLogFiles $httpLogPath

    Write-Host -Fore Green "Successfully imported log files."
}

Main

Now I just need to write an SSIS package which will update the database with the IIS log files from 3 server and two sites (daily), so I’ll be back to update you on how I got along.

Okay Automate copying IIS logs to SQL Database using Powershell, Log Parser and Task Scheduler post is now written, continue reading to find out how

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s