PowerShell Export-CSV - Missing Columns

Question

This is a follow-up question from PowerShell | EVTX | Compare Message with Array (Like)

I changed the tactic slightly, now I am collecting all the services installed,

$7045 = Get-WinEvent -FilterHashtable @{ Path="1system.evtx"; Id = 7045 } | select 
@{N=’Timestamp’; E={$_.TimeCreated.ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:ssZ')}},
Id, 
@{N=’Machine Name’; E={$_.MachineName}},
@{N=’Service Name’; E={$_.Properties[0].Value}},@{N=’Image Path’;E=$_.Properties[1].Value}},
@{N=’RunAsUser’; E={$_.Properties[4].Value}},@{N=’Installed By’; E={$_.UserId}}

Now I match each object for any suspicious traits and if found, I add a column 'Suspicious' with the value 'Yes'. This is because I want to leave the decision upto the analyst and pretty sure the bad guys might use something we've not seen before.

foreach ($Evt in $7045)
{
if ($Evt.'Image Path' -match $sus)
    {

    $Evt | Add-Member -MemberType NoteProperty -Name 'Suspicious' -Value 'Yes'

    }
}

Now, I'm unable to get PowerShell to display all columns unless I specifically Select them

$7045 | Format-Table

Same goes for CSV Export. The first two don't include the Suspicious Column but the third one does but that's because I'm explicitly asking it to.

$7045 | select * | Export-Csv -Path test.csv -NoTypeInformation
$7045 | Export-Csv -Path test.csv -NoTypeInformation
$7045 | Select-Object Timestamp, Id, 'Machine Name', 'Service Name', 'Image Path', 'RunAsUser', 'Installed By', Suspicious | Export-Csv -Path test.csv -NoTypeInformation

I read the Export-CSV documentation on MS. Searched StackOverFlow for some tips, I think it has something to do with PS checking the first Row and then compares if the property exists for the second row and so on. Thank you

Answer 1

The issue you're experiencing is partially because of how objects are displayed to the console, the first object's Properties determines the displayed Properties (Columns) for all objects.

The bigger problem though, is that Export-Csv will not export those properties that do not match with first object's properties unless they're explicitly added to the remaining objects or the objects are reconstructed, one easy way to achieve this is to use Select-Object as you have pointed out in the question.

Given the following example:

$test = @(
    [pscustomobject]@{
        A = 'ValA'
    }
    [pscustomobject]@{
        A = 'ValA'
        B = 'ValB'
    }
    [pscustomobject]@{
        C = 'ValC'
        D = 'ValD'
        E = 'ValE'
    }
)

• Format-Table will not display the properties B to E:

$test | Format-Table

A
-
ValA
ValA

• Format-List can display the objects properly, this is because each property with it's corresponding value has it's own console line in the display:

PS /> $test | Format-List

A : ValA

A : ValA
B : ValB

C : ValC
D : ValD
E : ValE

• Export-Csv and ConvertTo-Csv will also miss properties B to E:

$test | ConvertTo-Csv

"A"
"ValA"
"ValA"

You have different options as a workaround for this, you could either add the Suspicious property to all objects and for those events that are not suspicious you could add $null as Value.

Another workaround is to use Select-Object explicitly calling the Suspicious property (this works because you know the property is there and you know it's Name).

If you did not know how many properties your objects had, a dynamic way to solve this would be to discover their properties using the PSObject intrinsic member.

using namespace System.Collections.Generic

function ConvertTo-NormalizedObject {
    [CmdletBinding()]
    param(
        [Parameter(ValueFromPipeline, Mandatory)]
        [object[]] $InputObject
    )

    begin {
        $list  = [List[object]]::new()
        $props = [HashSet[string]]::new([StringComparer]::InvariantCultureIgnoreCase)
    }
    process {
        foreach($object in $InputObject) {
            $list.Add($object)
            foreach($property in $object.PSObject.Properties) {
                $null = $props.Add($property.Name)
            }
        }
    }
    end {
        $list | Select-Object ([object[]] $props)
    }
}

Usage:

# From Pipeline
$test | ConvertTo-NormalizedObject | Format-Table
# From Positional / Named parameter binding
ConvertTo-NormalizedObject $test | Format-Table

• Lastly, a pretty easy way of doing it thanks to Select-Object -Unique:

$prop = $test.ForEach{ $_.PSObject.Properties.Name } | Select-Object -Unique
$test | Select-Object $prop

Using $test for this example, the result would become:

A    B    C    D    E
-    -    -    -    -
ValA
ValA ValB
          ValC ValD ValE

Answer 2

Continuing from my previous answer, you can add a column Suspicious straight away if you take out the Where-Object filter and simply add another calculated property to the Select-Object cmdlet:

# create a regex for the suspicious executables:
$sus = '(powershell|cmd|psexesvc)\.exe'
# alternatively you can join the array items like this:
# $sus = ('powershell.exe','cmd.exe','psexesvc.exe' | ForEach-Object {[regex]::Escape($_)}) -join '|'

$7045 = Get-WinEvent -FilterHashtable @{ LogName = 'System';Id = 7045 } | 
        Select-Object Id, 
                      @{N='Timestamp';E={$_.TimeCreated.ToUniversalTime().ToString('yyyy-MM-ddTHH:mm:ssZ')}}, 
                      @{N='Machine Name';E={$_.MachineName}},
                      @{N='Service Name'; E={$_.Properties[0].Value}},
                      @{N='Image Path'; E={$_.Properties[1].Value}},
                      @{N='RunAsUser'; E={$_.Properties[4].Value}},
                      @{N='Installed By'; E={$_.UserId}},
                      @{N='Suspicious'; E={
                        if ($_.Properties[1].Value -match $sus) { 'Yes' } else {'No'} 
                      }}

$7045 | Export-Csv -Path 'X:\Services.csv' -UseCulture -NoTypeInformation

Because you have many columns, this will not fit the console width anymore if you do $7045 | Format-Table, but the CSV file will hold all columns you wanted.
I added switch -UseCulture to the Export-Csv cmdlet, which makes sure you can simply double-click the csv file so it opens correctly in your Excel.

As sidenote: Please do not use those curly so-called 'smart-quotes' in code as they may lead to unforeseen errors. Straighten these ’ thingies and use normal double or single quotes (" and ')