IIS Log Importer – MS SQL
January 28, 2008 at 3:53 am Australian Search Engine 4 comments
Looking for a way to import your IIS W3C format logs into MS SQL?
Microsoft has a utility available that makes the import very easy. The tool can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=890cd06b-abf8-4c25-91b2-f8d975cf8c07&displaylang=en
To run the import, run the program after download and install, this will open up a command prompt, at the command prompt is where you give it the commands for the import, following is an example;
LogParser “SELECT * INTO webLog FROM ex020111.log” -i:W3C -o:SQL -server:192.168.0.1 -database:yourDatabaseName -driver:”SQL Server” -username:yourSQLUsername -password:yourSQLPassword -createTable:ON
Running the above command will create a new table in your database with all the entries from the log file. Once it is in your database you can move it to another table, from where you can work with the data.
Entry filed under: Other. Tags: IIS, Import, log, MS SQL, tool.
Australian Search Engine
websitedesignaustralia
Taco Fleur
kyliewrites
cazazz
Jim Petrakis
1.
Jason | March 26, 2008 at 5:19 pm
thanks!!!!
this is kewl!
2.
Australian Search Engine | March 28, 2008 at 6:21 am
Not a worry, glad it helped!.
3.
mark | June 24, 2008 at 4:22 am
use sql server 2008 and go with the page level compression (not as good as winzip but pretty darn close)
try this powershell script:
dir *.log > c:\mark.txt
$files = get-content “c:\mark.txt”
foreach ($file in $files)
{
[string] $SQLParam = “d:\local\apps\logparser\logparser.exe “”"
$SQLParam += “select date, time, cs-username, s-computername, cs-method, cs-uri-stem, cs-uri-query, sc-status, sc-substatus, sc-win32-status, sc-bytes, cs-bytes, time-taken, cs-host, cs(User-Agent) as cs-User-Agent, cs(Referer) as cs-Referer, ‘myapp’ as application INTO IISLog from \\web1\d$\Common\IISLogs\www.myapp.com\W3SVC433992111\” + $file + “”" -i:IISW3C -o:SQL -server:sqlbox -database:IISLogs2008″
4.
rishi | August 17, 2010 at 11:55 am
works a treat – someone buy the man a drink!!!