IIS Log Importer – MS SQL

January 28, 2008 at 3:53 am 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.

Advertisements

Entry filed under: Other. Tags: , , , , .

clickfind on Yahoo News clickfind is on www.itwire.com

4 Comments Add your own

  • 1. Jason  |  March 26, 2008 at 5:19 pm

    thanks!!!!
    this is kewl!

    Like

    Reply
  • 2. Australian Search Engine  |  March 28, 2008 at 6:21 am

    Not a worry, glad it helped!.

    Like

    Reply
  • 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″

    Like

    Reply
  • 4. rishi  |  August 17, 2010 at 11:55 am

    works a treat – someone buy the man a drink!!!

    Like

    Reply

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 19 other followers

Archives

Top Rated

Top Clicks

  • None

Blog Stats

  • 168,383 hits

%d bloggers like this: