December 15, 2017

Automate copying IIS logs to SQL Database using Powershell, Log Parser and Task Scheduler

I decided not to go down the route of writing an SSIS package as mentioned in my previous post

So how else can you do what I wanted to achieve?

Remove all copied logs from the Database server
Copy yesterdays logs from 9 web servers
Import the logs to a SQL Database

This is how I did it:

Step 1 : Create 3 Powershell scripts

Copy IIS files from yesterday:

$Date = Get-Date
$Date = $Date.adddays(-1)
$Date2Str = $Date.ToString("yyyMMdd")

$Files1 = gci "\\server1\c$\inetpub\logs\LogFiles\W3SVC2"
ForEach ($File in $Files1){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "C:\\Logs\\1718\\server1"}
}

$Files2 = gci "\\server2\c$\inetpub\logs\LogFiles\W3SVC2"
ForEach ($File in $Files2){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "C:\\Logs\\1718\\server2"}
}

$Files3 = gci "\\server3\c$\inetpub\logs\LogFiles\W3SVC2"
ForEach ($File in $Files3){
     $FileDate = $File.creationtime
     $CTDate2Str = $FileDate.ToString("yyyyMMdd")
     if ($CTDate2Str -eq $Date2Str) {Copy-Item $File.Fullname "C:\\Logs\\1718\\server3"}
}

Remove all local iis log files:

$ErrorActionPreference = "Stop"

Import-Module Pscx -EA 0

function RemoveLogFiles
{
    Write-Host "Removing log files..."
    Remove-Item ($httpLogPath1)
	Remove-Item ($httpLogPath2)
	Remove-Item ($httpLogPath3)
	Remove-Item ($httpLogPath4)
	Remove-Item ($httpLogPath5)
	Remove-Item ($httpLogPath6)
	Remove-Item ($httpLogPath7)
	Remove-Item ($httpLogPath8)
	Remove-Item ($httpLogPath9)
}

function Main
{	

	[string] $httpLogPath1 = "C:\Logs\1718\server1\*.log"
	[string] $httpLogPath2 = "C:\Logs\1718\server2\*.log"
	[string] $httpLogPath3 = "C:\Logs\1718\server3\*.log"

	[string] $httpLogPath4 = "C:\Logs\1718\server4\W3SVC1\*.log"
	[string] $httpLogPath5 = "C:\Logs\1718\server5\W3SVC1\*.log"
	[string] $httpLogPath6 = "C:\Logs\1718\server6\W3SVC1\*.log"

	[string] $httpLogPath7 = "C:\Logs\1718\server7\W3SVC2\*.log"
	[string] $httpLogPath8 = "C:\Logs\1718\server8\W3SVC2\*.log"
	[string] $httpLogPath9 = "C:\Logs\1718\server9\W3SVC2\*.log"

    RemoveLogFiles 

    Write-Host -Fore Green "Successfully removed log files."
}

Main

Import Website Logs to Database:

$ErrorActionPreference = "Stop"

Import-Module Pscx -EA 0

function ExtractLogFiles(
    [string] $httpLogPath)
{
    If ([string]::IsNullOrEmpty($httpLogPath) -eq $true)
    {
        Throw "The log path must be specified."
    }

    [string] $httpLogArchive = $httpLogPath + "c:\logs\1718\*.log"

    If ((Test-Path $httpLogArchive) -eq $false)
    {
        Write-Host "Creating archive folder for compressed log files..."
        New-Item -ItemType directory -Path $httpLogArchive | Out-Null
    }

    Write-Host "Extracting compressed log files..."

    Get-ChildItem $httpLogPath -Filter "*.zip" |
        ForEach-Object {
            Expand-Archive $_ -OutputPath $httpLogPath

            Move-Item $_.FullName $httpLogArchive
        }
}

function ImportLogFilesServer1(
    [string] $httpLogPath1)
{
    If ([string]::IsNullOrEmpty($httpLogPath1) -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 sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
			+ ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath1"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=;Database=;Trusted_Connection=yes;"

    [string[]] $parameters = @()

	$parameters += $query
    $parameters += "-i:W3C"
	$parameters += "-e:-1"
	#$parameters += "-recurse:-1"
    $parameters += "-o:SQL"
	$parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function ImportLogFilesServer2(
    [string] $httpLogPath2)
{
    If ([string]::IsNullOrEmpty($httpLogPath2) -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 sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
			+ ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath2"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=;Database=;Trusted_Connection=yes;"

    [string[]] $parameters = @()

	$parameters += $query
    $parameters += "-i:W3C"
	$parameters += "-e:-1"
	#$parameters += "-recurse:-1"
    $parameters += "-o:SQL"
	$parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function ImportLogFilesServer3(
    [string] $httpLogPath3)
{
    If ([string]::IsNullOrEmpty($httpLogPath3) -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 sIp" `
            + ", cs-method AS csMethod" `
            + ", cs-uri-stem AS csUriStem" `
            + ", cs-uri-query AS csUriQuery" `
            + ", s-port AS sPort" `
			+ ", TO_STRING(cs-username) AS csUsername" `
            + ", c-ip AS cIp" `
            + ", cs(User-Agent) AS csUserAgent" `
            + ", cs(Referer) AS csReferer" `
            + ", sc-status AS scStatus" `
            + ", sc-substatus AS scSubstatus" `
            + ", sc-win32-status AS scWin32Status" `
            + ", time-taken AS timeTaken" `
        + " INTO IisLogs" `
        + " FROM $httpLogPath3"

    [string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
        + "Server=;Database=;Trusted_Connection=yes;"

    [string[]] $parameters = @()

	$parameters += $query
    $parameters += "-i:W3C"
	$parameters += "-e:-1"
	#$parameters += "-recurse:-1"
    $parameters += "-o:SQL"
	$parameters += "-createTable:ON"
    $parameters += "-oConnString:$connectionString"

    Write-Debug "Parameters: $parameters"

    Write-Host "Importing log files to database..."
    & $logParser $parameters
}

function Main
{
	[string] $httpLogPath1 = "C:\Logs\1718\Server1\*.log"
	[string] $httpLogPath2 = "C:\Logs\1718\Server2\*.log"
	[string] $httpLogPath3 = "C:\Logs\1718\Server3\*.log"

#Repeat above for all other server

    ImportLogFilesServer1 $httpLogPath1
    ImportLogFilesServer2 $httpLogPath2
    ImportLogFilesServer3 $httpLogPath3	

#Repeat above for all other server

    Write-Host -Fore Green "Successfully imported log files."
}

Main

Finally you need to setup some scheduled tasks:

There’s a few little bits here which wouldn’t work for me without the following arguments:

Powershell.exe in Program/script:

-ExecutionPolicy Bypass -file “C:\powershell iis scripts\remove all iis log files.ps1” in Add arguments (optional):

task

The ‘Copy IIS files from yesterday’ script required some work with our server and storage team to add a service account to be able to run batch scripts without being logged on, this is in the Local Group Policy, This was due to being on a domain and the policy was set to not allow me to set this for my user. More details to come on this, its time consuming to explain in great detail.

3 comments

December 18, 2017 - 1:00 pm Pingback: IIS Logs to SQL Database using Powershell and Log Parser | Not so many...
December 22, 2022 - 1:54 pm David

I used your code as the basis for a IISlog parse/loader, so many thanks for that. It worked perfectly for 6 months. They changed the format/ codepage somehow so that the code for the import failed

Kept on getting “Error setting locale info for codepage 65001: The data area passed to a system call is too small.”

Only way to fix it was to change -i:W3C to -i:IISW3C (line 122 on your code) and change RowNumber to LogRow in the select query (line 150)

Just check and see if your code still works. I am still not certain what the route cause was.

- December 22, 2022 - 2:57 pm Raymond Reid
  
  Thanks David.. Blogs written at a point in time occasionally need updating.

Share this:

Like this:

Related

3 comments

Leave a Reply Cancel reply