How to import IIS Logs in SQLServer [Part 2]

In the first part of this two-part-tutorial, we learned how to import a SINGLE IIS Log File into
a SQLServer Table without having to worry about the creation of the table.

In the real-world-scenario, you can face with multiple Web Sites (from a Web Farm) and importing
multiple logs one by one could be a boring work.

Why don’t we let the Computer doing that ?

Step 1. Creating Script to import multiple Logs

As first step, my idea is to write a “simple” script (in VBScript) to let cycle thru a specified
physical directory (normally C:\Windows\system32\Logs\W3SVC1) and running the command I’ve explained
in the first part.

After each “import” step, we can choose to delete the file if there NOT errors.

   1: Option Explicit

   2:  

   3: Call Main()

   4:  

   5: Sub Main()

   6:  

   7:     Dim cmdArgs, logFiles, logFile, logFolder, fso, isImported

   8:     Dim currentLogFileName, currentExtension, fullLogFilePath

   9:     Dim allowedExtension

  10:     

  11:     allowedExtension = "log"    

  12:     

  13:     Set cmdArgs = WScript.Arguments

  14:     logFolder = cmdArgs(0)

  15:     

  16:     Set fso = CreateObject("Scripting.FileSystemObject")

  17:     

  18:     If Not IsNull(logFolder) Then

  19:         If Not IsEmpty(logFolder) Then                        

  20:             

  21:             Set logFiles = GetFiles(logFolder)

  22:             

  23:             currentLogFileName = GetCurrentLogFile()

  24:             currentLogFileName = GetFullPath(logFolder, currentLogFileName)

  25:  

  26:             WScript.Echo "Current Log File " + currentLogFileName

  27:             

  28:             For Each logFile in logFiles

  29:                 

  30:                 fullLogFilePath = logFile 'GetFullPath(logFolder, logFile)

  31:             

  32:                 WScript.Echo "Analyzing File Name: " + logFile + vbCrLf

  33:                 

  34:                 currentExtension = Right(logFile,3)

  35:             

  36:                 If currentLogFileName <> logFile And currentExtension=allowedExtension Then

  37:                                     

  38:                 

  39:                     isImported = RunLogParser(fullLogFilePath)

  40:                     

  41:                     If IsImported Then

  42:                     

  43:                         DeleteFile(fullLogFilePath)

  44:                         

  45:                     End If

  46:                 End If

  47:             

  48:             Next

  49:             

  50:             

  51:         End If

  52:     End If

  53:     

  54:     Set fso = Nothing

  55:  

  56: End Sub

  57:  

  58: Function GetFullPath(folderPath, fileName)

  59:  

  60:     If Right(folderPath,1) <> "\" Then

  61:         

  62:         folderPath = folderPath + "\"

  63:     

  64:     End If

  65:     

  66:     GetFullPath = folderPath + fileName

  67:  

  68: End Function

  69:  

  70: Function GetFiles(strFolder) 

  71:     Dim fso, fld 

  72:       Set fso = CreateObject("Scripting.FileSystemObject")

  73:       Set fld = fso.GetFolder(strFolder)

  74:       Set GetFiles = fld.Files

  75:     Set fso = Nothing

  76:     Set fld = Nothing

  77: End Function

  78:  

  79: Function DeleteFile(strFileName)

  80:     Dim fso, fil 

  81:     Set fso = CreateObject("Scripting.FileSystemObject")

  82:       Set fil = fso.getfile(strFileName)

  83:       fil.Attributes = 0

  84:       fil.Delete

  85:     Set fil = Nothing

  86:     Set fso = Nothing

  87: End Function

  88:  

  89: Function GetCurrentLogFile()

  90:  

  91:     Dim currentDate, currentYear, currentMonth, currentDay, currentLogFileName

  92:     

  93:     currentDate = Now()

  94:     

  95:     currentYear = Right(CStr(Year(currentDate)),2)

  96:     currentMonth = CStr(Month(currentDate))

  97:     If Month(currentDate) < 10 Then

  98:         currentMonth = "0" & currentMonth        

  99:     End If

 100:     'currentMonth = Month(currentDate)

 101:     currentDay = CStr(Day(currentDate))

 102:     If Day(currentDate) < 10 Then

 103:         currentDay = "0" & currentDay

 104:     End If    

 105:     

 106:     currentLogFileName = "ex" + currentYear + currentMonth + currentDay + ".log"

 107:     

 108:     GetCurrentLogFile = currentLogFileName

 109:     

 110:  

 111: End Function

 112:  

 113: Function RunLogParser(logFile)

 114:  

 115:     Dim oShell, iRC, ShellString

 116:  

 117:     WScript.Echo "Importing File " + logFile + vbCrLf

 118:     

 119:     ShellString = """C:\Program Files\Log Parser 2.2\LogParser.exe"" ""SELECT * INTO IISLogs FROM " + logFile + """ " + " -i:W3C -o:SQL -server:DEV-S04 -database:Logs -driver:""SQL Server"" -username:euclidlogs -password:euclidlogspassword -createTable:OFF "

 120:  

 121:     'WScript.Echo vbTab + "Shell String " + ShellString + vbCrLf

 122:     

 123:     Set oShell =  WScript.CreateObject("WScript.Shell")

 124:     

 125:     iRC = oShell.Run(Shellstring, 1, True)        

 126:     

 127:     RunLogParser = (iRC=0)

 128:     'RunLogParser = False

 129: End Function

Moreover, the script filters for all files with a given file extensions (“.log” in my case).

Step 2. Calling the VBScript script from a Batch one

In order to let it schedule easily (in case you need to Archive / Cycle the logs) from the Windows Task Scheduler,
you can put the call in a simple batch script as below:

   1: @echo off

   2:  

   3: pushd "C:\AdminScripts\Maintenance Scripts"

   4:  

   5: cscript IISLogImporter.vbs C:\WINDOWS\system32\LogFiles\W3SVC1\

   6:  

   7: popd

Conclusions:

A production Web / Web Farm generates Gigabytes of Logs over the time and they’re useless if you can’t analyze them.

Moreover, Web Servers’ disk space is not infinite so you need to archive such logs; what’s better than a Database server ?

Logging DIRECTLY into the Database (from IIS) could lead to a slowdown of the whole set of hosted applications.

The scripts above will help you to do so once every few weeks or every day.