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)

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