Importing IIS Logs into a SQL Database / Table

1. Download the Log Parser tool from Microsoft here. I know, it’s old but works great.

2. Dump your IIS log files somewhere (ie: c:\temp\logs).

3. Run this in cmd:

C:\Program Files (x86)\Log Parser 2.2>logparser “SELECT * INTO iisLogs FROM c:\temp\logs\*.log ” -i:iisw3c -o:SQL -server:localhost -database:webLogs -username:sa -password:yourpass -createTable: ON

if you’re on 32bit, run Log Parser will be in this folder:

C:\Program Files\Log Parser 2.2>logparser “SELECT * INTO iisLogs FROM c:\temp\logs\*.log ” -i:iisw3c -o:SQL -server:localhost -database:webLogs -username:sa -password:yourpass -createTable: ON

Now you have a table w/ tons of data!

Update:

In an updated article, I discuss increasing import performance the transactionRowCount option.

Importing IIS Logs into a SQL Database / Table

9 thoughts on “Importing IIS Logs into a SQL Database / Table

  1. As an update, when importing 8 million records, it takes about 1 & 1/2 hour:

    Statistics:
    ———–
    Elements processed: 8435752
    Elements output: 8435752
    Execution time: 5142.73 seconds (01:25:42.73)

    Like

  2. WAyne says:

    Copying and pasting the above didn’t work right out of the box for me. Had to delete and replace the double-quotes to correct an error with message “Error: detected extra argument “*” after query”. The paste must have seen the quote as a different codepage than the standard ANSI (8520?) code used within my DOS window.

    I’m running 64bit Win7 Pro SP1 Eng.

    Liked by 1 person

  3. Ronen Ariely says:

    I recommend to compare the time to simple Trascat-SQL solution (based on the procedure of: (1) clean the file first. (2) Create new file (3) import the file using simple BULK INSERT. I wrote some information in a (not)short post as answer to a question in the forums. It covers the use of Transact-SQL to works with IIS log files directly or import them to SQL Server: http://ariely.info/Blog/tabid/83/EntryId/212/Parse-and-import-IIS-log-files-using-Transact-SQL.aspx

    @WLSCAUDILL SAYS, you should not change the host file for this simple task in my opinion, but simply understand how the tool works. The name “localhost” can be used when this is how you connect to the server, but in fact you can use the same name as you use when you connect from your application or from SQL Operations Studio (SOS) or from SQL Server Management Studio (SSMS).

    Like

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