Parsing IIS weblogs

Tagged: 

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Venkata Kalyan Venkata Kalyan 7 months, 2 weeks ago.

  • Author
    Posts
  • #38100
    Profile photo of Venkata Kalyan
    Venkata Kalyan
    Participant

    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

  • #38103
    Profile photo of TomBaker
    TomBaker
    Participant

    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"
                    }
                }
            }
        }
    }
    
    
  • #38105
    Profile photo of random commandline
    random commandline
    Participant
    $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
    
  • #38201
    Profile photo of Venkata Kalyan
    Venkata Kalyan
    Participant

    Thanks all. Worked perfectly.

    -Kalyan

You must be logged in to reply to this topic.