How to periodically send IIS Logs with custom fields to SQL Server database

Recently I wanted to push IIS logs to SQL database for analytics purpose.

Following are the steps I used

Step 1: Configurations at IIS side

Add custom fields as shown below

Step 2: Create a table in SQL Server database with following columns

Step 3: Construct following PowerShell scripts

a. RemoveLogs.ps1 - This will remove existing logs in working directory and clean up for new logs

$ErrorActionPreference = "Stop"
 
Import-Module Pscx -EA 0
 
function RemoveLogFiles
{
    Write-Host "Removing log files..."
    Remove-Item ($httpLogPath1)
}
 
function Main
{   
 
    [string] $httpLogPath1 = "E:\TempLogs\*.log"
     
    RemoveLogFiles 
 
    Write-Host -Fore Green "Successfully removed log files."
}

b. CopyLogs.ps1 - This will copy Logs from IIS log file location to our logs directory. I'm copying everything created during last two days

$Date = Get-Date
$Date = $Date.adddays(-2)
$Date2Str = $Date.ToString("yyyMMdd")
 
$Files1 = gci "\\IIS-01\LogFiles\W3SVC4"
ForEach ($File in $Files1){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "E:\TempLogs"}
}

c. CopyLogsToDB.ps1 - This will import logs to our SQL Server database table

$ErrorActionPreference = "Stop"
 
Import-Module Pscx -EA 0
 
function ImportLogFilesServer(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."
    }
 
    [string] $logParser = "${env:ProgramFiles(x86)}" `
        + "\Log Parser 2.2\LogParser.exe "
 
    [string] $query = `
        [string] $query = `
        "SELECT" `
            + " LogFilename" `
            + ", RowNumber" `
            + ", TO_TIMESTAMP(date, time) AS EntryTime" `
            + ", s-ip AS ServerIpAddress" `
            + ", cs-method AS Method" `
            + ", cs-uri-stem AS UriStem" `
            + ", cs-uri-query AS UriQuery" `
            + ", s-port AS Port" `
            + ", username  AS Username" `
            + ", c-ip AS ClientIpAddress" `
            + ", cs(User-Agent) AS UserAgent" `
            + ", pageurl AS Referrer" `
            + ", sc-status AS HttpStatus" `
            + ", sc-substatus AS HttpSubstatus" `
            + ", sc-win32-status AS Win32Status" `
            + ", time-taken AS TimeTaken" `
            + ", storename  AS StoreName" `
        + " INTO IISLog" `
        + " FROM $httpLogPath"

 
    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=DB-01;Database=IISLogs;Trusted_Connection=yes;"
 
    [string[]] $parameters = @()
 
    $parameters += $query
    $parameters += "-i:W3C"
    $parameters += "-e:-1"
    $parameters += "-o:SQL"
    $parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"
 
    Write-Debug "Parameters: $parameters"
 
    Write-Host "Importing log files to database..."
    $logParser 
    $parameters
    & $logParser $parameters

}
 
 
function Main
{
    [string] $httpLogPath = "E:\TempLogs\*.log"
 

    ImportLogFilesServer $httpLogPath
 
#Repeat above for all other server
 
    Write-Host -Fore Green "Successfully imported log files."
}
 
Main

Step 4: Apply a schedule using Task Scheduler

This will upload latest logs including custom fields to SQL Server database.