How to convert IIS W3C log file to CSV?

Question

I want to parse IIS log file (in W3C format) to CSV or XLS file in PowerShell or C#.

I Try With this code in PowerShell:

$LogFolder = "C:\iislog\"
$LogFiles = [System.IO.Directory]::GetFiles($LogFolder, "*.log")
$LogTemp = "C:\iislog\end.csv"
# Logs will store each line of the log files in an array
$Logs = @()
# Skip the comment lines
$LogFiles | % { Get-Content $_ | where {$_ -notLike "#[D,F,S,V]*" } | % { $Logs += $_ } }

# Then grab the first header line, and adjust its format for later
$LogColumns = ( $LogFiles | select -first 6 | % { 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 | select -Unique csusername

But this code, not delimiter columns and print each row to one column in CSV (when open end.csv with excel).

How I can fix this problem?

I want the columns to separate from one another in the output file.

What is wrong with your powershell code? – Daniel W. Nov 27 '18 at 14:07 — Daniel W., Nov 27 '18 at 14:07

score 2 · Accepted Answer · answered Nov 27 '18 at 14:26

My quick and dirty approach to reading these logs in PowerShell uses a custom function. Mostly it is just a matter of using ConvertFrom-CSV and manipulating the first few lines of the IIS log file format to satisfy the cmdlets expectations.

function ConvertIISLogFrom-CSV{

    [cmdletbinding()]
    param(
        [parameter(ValueFromPipelineByPropertyName=$true, Mandatory=$true)]
        [Alias("FullName")]
        [string]$File
    )
    process{
        Get-Content $file |  Where-Object{$_ -notmatch "^#[DSV]"} | ForEach-Object{$_ -replace '^#Fields: '} | ConvertFrom-Csv -Delimiter " "
    }
}

Get-ChildItem $path -Filter "ex*" | 
    Sort-Object creationdate -Descending | 
    Select -Last 1  |
    ConvertIISLogFrom-CSV | 
    Where-Object {$_."cs-username" -eq "username" -and $_."x-fullpath" -like "*error*"} |
    Select-Object date,time,"c-ip","cs-username","x-session","x-fullpath" |
    Format-Table -AutoSize

The cmdlet will read a file and effectively drop the first few lines of comments. We purposely leave the #fields line, from the initial filterm as that contains the column header. After we just get rid of #fields which leaves us with a proper CSV format.

Using the above you can just change the $path to be a location that contains logs. What follows after that is mostly an example to show integration with other PowerShell filtering and cmdlets.

Since we are making PowerShell objects you can use whatever export options your would like with the data. Pipe into Export-CSV and you are good to go.

Nice one +1, a for me more logic name would be `ConvertFrom-W3CIISLog` for the function. In post processing you use fixed property names which might not be present in the headers. — , Nov 27 '18 at 15:32
Indeed. That is why I said _What follows after that is mostly an example to show integration .._. Of course you would need to know the proper names of what you are dealing with. — Matt, Nov 27 '18 at 15:52

How to convert IIS W3C log file to CSV?

1 Answers1