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.

Answer 1

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.