Category: Powershell
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.
IIS Logs to SQL Database using Powershell and Log Parser
Our IIS logs are ridiculously large, every time I am asked to find out some stats it takes me ages copying the files to my machine and running some log parser SQL on it.
So I thought it’s time to insert them into a SQL Database. I adapted a script which I mostly found here and used the following to do the import. At first I was getting the following error which I couldn’t figure out how to solve SQL table column “csUsername” date type is not compatible with SELECT clause item “csUsername” (type INTEGER)
This was being caused because some of the log files had the username as +12345678 or 12345678+ therefore no longer being an INTEGER and not being able to be imported.
To fix this look at line 27 of the following Powershell script to force csUsername to be a VARCHAR rather than an INTEGER
$ErrorActionPreference = "Stop"
Import-Module Pscx -EA 0
function ImportLogFiles(
[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 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 $httpLogPath"
[string] $connectionString = "Driver={SQL Server Native Client 11.0};" `
+ "Server=<Database Server>;Database=<Database>;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
}
function Main
{
$httpLogPath = "<Path to your logs>\*.log"
ImportLogFiles $httpLogPath
Write-Host -Fore Green "Successfully imported log files."
}
Main
Now I just need to write an SSIS package which will update the database with the IIS log files from 3 server and two sites (daily), so I’ll be back to update you on how I got along.
Okay Automate copying IIS logs to SQL Database using Powershell, Log Parser and Task Scheduler post is now written, continue reading to find out how
Not so many... 