Technology Toolbox

Your technology Sherpa for the Microsoft platform

Jeremy Jameson - Founder and Principal

Search

Search

Import website (IIS) logs into SQL Server using Log Parser and PowerShell

This past weekend, while researching some errors reported on the TechnologyToolbox.com website, I wanted to analyze the IIS logs -- for example, to see how many requests have come from specific IP addresses (such as those suspected of attempting to hack the site).

As I described in a previous post, TechnologyToolbox.com currently uses Google Analytics to provide numerous metrics and track trends. However, Google Analytics doesn't always provide the answer you are looking for. In fact, if an HTTP request results in an error (typically as a result of a failed hack attempt), then the Google Analytics script may never called and the request won't even be logged in that system (depending on the specific error and how the website is configured to handle errors).

Fortunately, when I setup the TechnologyToolbox.com website at WinHost, I turned on the option to make the raw IIS logs available. Consequently, there's a hidden folder (only available via FTP and only by the website owner) in which WinHost dumps a zip file each day containing the raw IIS log file from the previous day.

I've used Microsoft Log Parser to analyze IIS log files in the past (on client projects). For quick, ad hoc analysis of a small amount of data, executing queries directly against the log file via the command line is tolerable.

However, if you are going to repeatedly perform a number of queries against, say, a few hundred thousand log entries, then it is worth the effort to first import this data into a database.

Note that Log Parser includes an option to automatically create a table in a specified database and subsequently import the data from the log files. However, I prefer to create the table beforehand -- in order to tweak the column names, combine the "date" and "time" fields into a single DATETIME column, and add some "NOT NULL" constraints.

Here is the script I use to create the table in SQL Server:

CREATE TABLE dbo.WebsiteLog
(
    LogFilename VARCHAR(255) NOT NULL,
    RowNumber INT NOT NULL,
    EntryTime DATETIME NOT NULL,
    SiteName VARCHAR(255) NOT NULL,
    ServerName VARCHAR(255) NOT NULL,
    ServerIpAddress VARCHAR(255) NOT NULL,
    Method VARCHAR(255) NOT NULL,
    UriStem VARCHAR(255) NOT NULL,
    UriQuery VARCHAR(255) NULL,
    Port INT NOT NULL,
    Username VARCHAR(255) NULL,
    ClientIpAddress VARCHAR(255) NOT NULL,
    HttpVersion VARCHAR(255) NOT NULL,
    UserAgent VARCHAR(255) NOT NULL,
    Cookie VARCHAR(255) NULL,
    Referrer VARCHAR(255) NULL,
    Hostname VARCHAR(255) NOT NULL,
    HttpStatus INT NOT NULL,
    HttpSubstatus INT NOT NULL,
    Win32Status INT NOT NULL,
    BytesFromServerToClient INT NOT NULL,
    BytesFromClientToServer INT NOT NULL,
    TimeTaken INT NOT NULL
)

To import the TechnologyToolbox.com log files into a SQL Server database (CaelumDW), I whipped up a PowerShell script to automatically:

  1. Extract (a.k.a. unzip) the log files in the /httplog folder (which I periodically FTP from the Production environment) and subsequently move the zip files to the /httplog/Archive folder.
  2. Import the log files using the LogParser utility.
  3. Remove the log files from the /httplog folder (to avoid inserting duplicate data the next time the script is run).

Here is the script in hopes it helps others who wish to import their log files for subsequent analysis.

Note
In the past, I've worked on several "clickstream data warehousing" projects where we performed this kind of import as the first step in the ETL process (prior to transforming the data into a star schema and building OLAP cubes against them). This simple WebsiteLog table is certainly not meant to serve as a replacement for a more sophisticated analytics solution. Rather I'm sharing it here for those wishing to do "quick and dirty" analysis of their data.

Import Website Log Files.ps1

$ErrorActionPreference = "Stop"

Import-Module Pscx -EA 0

function ExtractLogFiles(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."    
    }
    
    [string] $httpLogArchive = $httpLogPath + "\Archive"
    
    If ((Test-Path $httpLogArchive) -eq $false)
    {
        Write-Host "Creating archive folder for compressed log files..."
        New-Item -ItemType directory -Path $httpLogArchive | Out-Null
    }
    
    Write-Host "Extracting compressed log files..."
    
    Get-ChildItem $httpLogPath -Filter "*.zip" |
        ForEach-Object {
            Expand-Archive $_ -OutputPath $httpLogPath
            
            Move-Item $_.FullName $httpLogArchive
        }
}

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-sitename AS SiteName" `
            + ", s-computername AS ServerName" `
            + ", 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-version AS HttpVersion" `
            + ", cs(User-Agent) AS UserAgent" `
            + ", cs(Cookie) AS Cookie" `
            + ", cs(Referer) AS Referrer" `
            + ", cs-host AS Hostname" `
            + ", sc-status AS HttpStatus" `
            + ", sc-substatus AS HttpSubstatus" `
            + ", sc-win32-status AS Win32Status" `
            + ", sc-bytes AS BytesFromServerToClient" `
            + ", cs-bytes AS BytesFromClientToServer" `
            + ", time-taken AS TimeTaken" `
        + " INTO WebsiteLog" `
        + " FROM $httpLogPath\*.log"
        
    [string] $connectionString = "Driver={SQL Server Native Client 10.0};" `
        + "Server=BEAST;Database=CaelumDW;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 RemoveLogFiles(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."    
    }
    
    Write-Host "Removing log files..."    
    Remove-Item ($httpLogPath + "\*.log")
}
    
function Main
{
    [string] $httpLogPath = "C:\inetpub\wwwroot\www.technologytoolbox.com\httplog"

    ExtractLogFiles $httpLogPath

    ImportLogFiles $httpLogPath
    
    RemoveLogFiles $httpLogPath
        
    Write-Host -Fore Green "Successfully imported log files."
}

Main
Note
The script assumes you have installed Log Parser to the default location (on a 64-bit environment). You'll also need to update the log file path and connection string (unless you just happen to have a SQL Server named BEAST and a database named CaelumDW).

After running the script, you should see something like this:

PS C:\NotBackedUp\Public\Toolbox\PowerShell> & ".\Import Website Log Files.ps1"
Creating archive folder for compressed log files...
Extracting compressed log files...
Importing log files to database...

Statistics:
-----------
Elements processed: 210943
Elements output:    210943
Execution time:     155.13 seconds (00:02:35.13)

Removing log files...
Successfully imported log files.

Comments

  1. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    March 8, 2012 12:21 AM
    mark
    does your script work in powershell 2.0? i keep getting getting errors about syntax error: extra token(s) after query
  2. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    March 8, 2012 3:00 PM
    Jeremy Jameson
    Gravatar
    @Mark,

    Yes, this works on PowerShell 2.0. I run this on a server with Windows Server 2008 R2 SP1.

    PS U:\> Get-Host


    Name : ConsoleHost
    Version : 2.0
    ...
  3. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    October 8, 2012 12:18 AM
    Mo
    Gravatar
    Great post!

    Could you assist with the following error?

    Thanks
    =======================================

    ForEach-Object : The term 'Expand-Archive' is not recognized as the name of a cmdlet, function, script file, or operabl
    e program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At C:\scripts\Archive.ps1:24 char:23
    + ForEach-Object <<<< {
    + CategoryInfo : ObjectNotFound: (Expand-Archive:String) [ForEach-Object], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException,Microsoft.PowerShell.Commands.ForEachObjectCommand
  4. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    October 8, 2012 5:08 PM
    Jeremy Jameson
    Gravatar
    @Mo

    You need to install the PowerShell Community Extensions from CodePlex (http://pscx.codeplex.com).
  5. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    November 19, 2012 5:30 PM
    Horst
    Gravatar
    Hello Jeremy,

    Great post, thank you very much.
    I have saved a lot of time, to implement such function :-)))
    Did you have also some sql queries, to get some usefull reports?

    Thanks for your help,
    Horst
  6. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    November 5, 2013 11:05 PM
    kwasi Denkyira
    Gravatar
    I am using your script here. It is extracting ok but the extracted folder is empty. Any Idea why?
  7. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    August 26, 2014 11:42 AM
    Matt
    Gravatar
    Hi Jeremy, Thanks for this, great post! I've been using this along with some other bits and pieces to try and grab specific event IDs out of event logs and load them into a SQL server database.

    It mostly seems to works, except I get an error saying:

    SQL table column "evtStrings" data type is not compatible with SELECT cause
    item "Strings" (type STRING)

    evtStrings is a column I created to save this data, the Strings item is crucial, if I look at the XML in event viewer its the bit that contains the <EventData><Data Name...></EventData> (so I do need it!)

    I initially had the type of this coilmn astext, but have tried with a few others as well and just can't seem to get it to work.

    Any suggestions? Sorry I'm no a SQL guy, just a SysAdmin!

    Thanks

    Matt
  8. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    September 4, 2014 2:43 AM
    jack
    Gravatar
    thanks for the script. However, is there any way to iterate through a folder path to find all .log files? We are downloading our logs from Azure and each instance has a unique path. I would like to enter a root path and have log parser look through all the sub folders.
  9. # FileZilla Logs to SQL Server via PowerShell &amp;laquo; Shawn Melton

    November 30, 2014 5:00 PM
    Pingback/TrackBack
    FileZilla Logs to SQL Server via PowerShell &amp;laquo; Shawn Melton
  10. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    April 6, 2015 7:13 PM
    Armand
    Gravatar
    Thank you so much Jeremy,

    I had one or two problems installing pscx (Which is very important for this deployment!), however once I got that working I pretty much just ran the scripts as you described and everything was neatly imported to a SQL Table! No more batch file processing!
  11. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    June 22, 2015 8:43 PM
    Armand
    Gravatar
    Hi guys, anyone know if its possible to recursively import log files from sub directories? There seems to be an issue because its in W3C format it does not seem to support the -recurse parameter. Any workarounds are also appreciated. Thanks
  12. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    November 8, 2015 9:33 PM
    DanS
    Gravatar
    Your solution works great, is there a way to filter the amount of IIS logs from being pushed into SQL... Such as adding a WHERE clause - Where cs-uri-stem is not like '%svc%' . I was trying to add something like that to the PowerShell script, but kept getting the following error:

    Cannot convert value "WHERE cs-uri-stem not like '%svc%'" to type "System.Int32". Error: "Input string was not in a correct format."
  13. # re: Import website (IIS) logs into SQL Server using Log Parser and PowerShell

    May 18, 2016 5:41 PM
    Marc Waanders
    Gravatar
    This works very well when written to localhost.
    When I use external SQL instance the logparser keeps looping through the lines in the logfile. Does anybody have a clue why this is happening?
  14. # IIS Logs to SQL Database using Powershell and Log Parser | Not so many...

    December 7, 2017 1:26 PM
    Pingback/TrackBack
    IIS Logs to SQL Database using Powershell and Log Parser | Not so many...

Add Comment

Optional, but recommended (especially if you have a Gravatar). Note that your email address will not appear with your comment.
If URL is specified, it will be included as a link with your name.

To prevent spam from being submitted, please select the following fruit: Grapes

Cherries
Grapes
Pear
Apple
Watermelon
Strawberry
 
Please add 2 and 3 and type the answer here: