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.
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)
LikeLike
Like this, it is very easy to fill in iisLogs… Very good
LikeLike
Awesome post; mine didn’t work unless I changed localhost to the actual machine name
LikeLike
Great find, I had to change localhost to my actual machine name to get it to work but after that was just fine.
LikeLike
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!
LikeLike
Still works great, three years later! I have four consoles running in parallel, ~325K records / minute inserted. Thanks!
LikeLike
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 […]
LikeLike
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).
LikeLike