PowerShell Cmdlet to convert audit log XML files to CSV

[florianf]

For a project I needed to convert Ontap Audit Log XML files to CSV. As I couldn't find any existing solutions, I wrote the following small PowerShell Cmdlet.

I'd be glad to receive feedback or suggestions for improvements.

An example XML audit event log is attached and the resulting CSV file.

Just paste the Cmdlet code below in PowerShell and then use the following command to convert an XML file (replace the filepath with the path to your file)

Convert-EventXmlToCsv -Path "c:\tmp\audit_svm_trinidad_nas_D2017-07-26-T06-54-13_0000000000.xml"

Cmdlet Code:

<#
.SYNOPSIS
Converts NetApp XML Audit Event Log Files to CSV
.DESCRIPTION 
Converts NetApp XML Audit Event Log Files to CSV
.EXAMPLE
Convert-EventXmlToCsv -Path "c:\tmp\audit_svm_trinidad_nas_D2017-07-26-T06-54-13_0000000000.xml"
#>
function Convert-EventXmlToCsv {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)][System.IO.FileInfo]$Path,
        [Parameter(Mandatory = $false)][System.IO.FileInfo]$OutputPath
         )

        if (!$OutputPath) {
            $OutputPath = Join-Path $Path.Directory.FullName "$($Path.BaseName).csv" 
        }

        Write-Host "Importing XML from $Path"
        $EventXml=[xml](Get-Content -Path $Path)

        $Headers = @()

        $EventCount = $EventXml.events.event.count
        $Counter = 1

        $Events = foreach ($Event in $EventXml.events.event) {
            Write-Progress -Activity "Converting XML to CSV" -PercentComplete ([int]($Counter/$EventCount*100))
            $Counter++
            $TimeCreated = $event.system.timecreated.systemtime
            $ProviderName = $event.system.provider.name
            $ProviderGuid = $event.system.provider.guid
            $Output = $event.system | ConvertTo-Csv | ConvertFrom-Csv
            $Output.timecreated = [DateTime]$TimeCreated
            $Output.Provider = $ProviderName
            $Output | Add-Member -MemberType NoteProperty -Name ProviderGuid -Value $ProviderGuid
            foreach ($AttributeName in $event.EventData.Data.Name) {
                $Output | Add-Member -MemberType NoteProperty -Name $AttributeName -Value ($event.eventdata.data | ? { $_.Name -eq $AttributeName } | % { $_."#text" })
            }
            $Headers += $Output.PSObject.properties | ? { $_.MemberType -eq "NoteProperty" } | % { $_.Name }
            $Headers = $Headers | Select-Object -Unique
            Write-Output $Output
        } 

        Write-Progress -Activity "Converting XML to CSV completed" -Completed

        Write-Host "Writing CSV"
        $Events | Select-Object -Property $Headers | Export-Csv -NoTypeInformation -Path $OutputPath -Delimiter ";"
        Write-Host "Output written to $OutputPath"
} 

[krispetrovic]

Your PowerShell Cmdlet for converting Ontap Audit Log XML files to CSV follows a similar approach to what I discuss in my blog post on XML to CSV conversion. You effectively flatten the hierarchical XML structure by extracting key fields, like TimeCreated and ProviderName, and writing them into a CSV format. The process of dynamically generating headers and ensuring all relevant data is included aligns well with the principles from my post. For improvement, consider adding logic for handling more complex XML structures, as flexibility is key when dealing with varied audit logs.

If you are interested in more information contact me.