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!
In an updated article, I discuss increasing import performance the transactionRowCount option.
9 thoughts on “Importing IIS Logs into a SQL Database / Table”
As an update, when importing 8 million records, it takes about 1 & 1/2 hour:
Elements processed: 8435752
Elements output: 8435752
Execution time: 5142.73 seconds (01:25:42.73)
Like this, it is very easy to fill in iisLogs… Very good
Awesome post; mine didn’t work unless I changed localhost to the actual machine name
Great find, I had to change localhost to my actual machine name to get it to work but after that was just fine.
Thanks and good point. Also, you could make some additions to C:\Windows\System32\drivers\etc\hosts to allow any name you want. Happy log viewing!
Still works great, three years later! I have four consoles running in parallel, ~325K records / minute inserted. Thanks!
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.
LikeLiked by 1 person
[…] few years ago I showed how to use Microsoft’s Log Parser tool to take IIS log files and import into a SQL […]
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).