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):
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.
Not so many... 
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.
Thanks David.. Blogs written at a point in time occasionally need updating.