Parsing IIS weblogs

PowerShell Help
1

Hi,
I have a log file as below:
#Software: Microsoft Internet Information Services 7.5
#Version: 1.0
#Date: 2014-06-04 00:00:00
#Fields: date time s-sitename s-computername s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs-version cs(User-Agent) cs(Referer) cs-host sc-status sc-win32-status sc-bytes cs-bytes time-taken
2014-06-04 00:00:00 W3SVC1 DSFPRDAPP814 10.33.183.14 POST /ASP1/QuickOrderEntry.aspx +Z+sn6z7mI3fc+7BaWfj5SOwVzS5JhjWcKQACYTKdJ3HyTzjH+4REuRBPRdrtPbOng5TxlelWM+d%2fvX6SMIkbiLeM918S%2fXx 80 - 170.37.227.10 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E) http://ecldocuments.staples.com/ASP1/QuickOrderEntry.aspx?+Z+sn6z7mI3fc+7BaWfj5SOwVzS5JhjWcKQACYTKdJ3HyTzjH+4REuRBPRdrtPbOng5TxlelWM+d/vX6SMIkbiLeM918S/Xx ecldocuments.staples.com 200 0 54092 70237 358
2014-06-04 00:00:00 W3SVC1 DSFPRDAPP814 10.33.183.14 POST /ASP1/QuickOrderEntry.aspx +Z+sn6z7mI3fc+7BaWfj5SOwVzS5JhjWcKQACYTKdJ3HyTzjH+4REuRBPRdrtPbOng5TxlelWM+d%2fvX6SMIkbiLeM918S%2fXx 80 - 170.37.227.10 HTTP/1.1 Mozilla/4.0+(compatible;+MSIE+8.0;+Windows+NT+5.1;+Trident/4.0;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.4506.2152;+.NET+CLR+3.5.30729;+.NET4.0C;+.NET4.0E) http://ecldocuments.staples.com/ASP1/QuickOrderEntry.aspx?+Z+sn6z7mI3fc+7BaWfj5SOwVzS5JhjWcKQACYTKdJ3HyTzjH+4REuRBPRdrtPbOng5TxlelWM+d/vX6SMIkbiLeM918S/Xx ecldocuments.staples.com 200 0 54245 70511 156
2014-06-04 00:00:00 W3SVC1 DSFPRDAPP814 ::1 GET /ASP1/OrderPricingDetails.aspx OrderId=2037270729 80 - ::1 HTTP/1.1 - - localhost 200 0 23284 83 156

I am trying to search for a string in the file (OrderPricingDetails.aspx), if it is found, i need the the complete row to be stored in a csv with headers. I wrote the below script, but I am not able to get the desired output. Can someone please help, where I am missing.

$LogFolder = “C:\Users\venkatak\Desktop\New_folder\Vijay\staples”
$LogFiles = [System.IO.Directory]::GetFiles($LogFolder, “u_ex140604Copy.log”)
$LogTemp = “.\logs.tmp”

Logs will store each line of the log files in an array

$Logs = @()

Skip the comment lines

$LogFiles | % { Get-Content -ReadCount 1000 $_ | where {$_ -notLike “#[D,F,S,V]*” } | % { $Logs += $_ } }

Then grab the first header line, and adjust its format for later

$LogColumns = ( $LogFiles | select -first 1 | % { Get-Content $_ | where {$_ -Like “#[F]*” } } ) `
-replace “#Fields: “, “” -replace “-”,”” -replace “(”,“” -replace “)”,“”

Temporarily, store the reformatted logs

Set-Content -LiteralPath $LogTemp -Value ( [System.String]::Format(“{0}{1}{2}”, $LogColumns, [Environment]::NewLine, ( [System.String]::Join( [Environment]::NewLine, $Logs) ) ) )

Read the reformatted logs as a CSV file

$Logs = Import-Csv -Path $LogTemp -Delimiter " "

Sample query : Select all unique users

$Logs | Export-Csv AllLogs.csv

Thanks,
-Kalyan

2

Some things to consider

  1. IIS Logs can change their format mid-logfile and write a new header (if someone changed the output format during the current log rotation
  2. You don’t have to write to a .tmp file, just use ConverFrom-CSV instead of Import-CSV
  3. line by line string manipulation in powershell is slow. This can vastly be improved on

The below is a quick work up. Same basic concept. Watch for #Fields rows and parse the header, otherwise, use the header to parse the line.

Loop through a Get-ChildItem -path calling the below function on all the files, dont need to use System.IO at all

function Get-IISLogContent
{
    param ([parameter(Mandatory=$true)][string]$logfile)
    if (-not (test-path $logfile)) { 
        Write-Warning "$logfile does not exist"
        return
    }
    $header=$null
    foreach ($lines in (get-content -path $logfile -ReadCount 1000)) {
        foreach ($line in $lines) {
            if ($line -match '^#Fields: (.*)') { $header = $Matches[1] -split ' ' }
            elseif ($line -notmatch '^#') {
                if ($null -ne $header) {
                    try {
                        $line | ConvertFrom-Csv -Delimiter ' ' -Header $header -ErrorAction stop
                    } catch {
                        Write-Warning "Error Parsing: $line"
                    }
                } else {
                    Write-Warning "Encountered logline before a header line: $line"
                }
            }
        }
    }
}
3 
$log = Get-ChildItem -Path \\path\to\log\file

$col = {@()}.invoke()
switch -Regex -File $log
{
    '#Fields.*' {$fields = $_}
    '\d+.*OrderPricing.*' {$col.add($Matches[0])}
}

$fields = $fields -replace '#Fields:'
$fields = $fields.trim() -split ' '

$col -replace ' ',',' | convertfrom-csv -Header $fields | 
Export-Csv -Path \\path\to\log\iislog.csv -NoTypeInformation
4

Thanks all. Worked perfectly.

-Kalyan