Home > Net >  PowerShell Export-CSV - Missing Columns
PowerShell Export-CSV - Missing Columns

Time:12-27

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

CodePudding user response:

The issue you're experiencing is partially because of how objects are displayed on the console, the first object's properties will determine the properties (columns) displayed on the console. The bigger problem though, is that Export-Csv will not export those properties that are not part of the first object on the pipeline unless you explicitly use Select-Object as you have pointed out in the question.

Example:

$test = @(
    [pscustomobject]@{
        Col = 'Val'
    }
    [pscustomobject]@{
        Col = 'Val'
        Col2 = 'Val2'
    }
    [pscustomobject]@{
        Col3 = 'Val3'
        Col4 = 'Val4'
        Col5 = 'Val4'
    }
)
  • Format-Table will not display Col2 to Col5:
PS /> $test | Format-Table

Col
---
Val
Val
  • Format-List will display the objects as they are:
PS /> $test | Format-List

Col : Val

Col  : Val
Col2 : Val2

Col3 : Val3
Col4 : Val4
Col5 : Val4
  • Export-Csv and ConvertTo-Csv will also miss Col2 to Col5:
PS /> $test | ConvertTo-Csv

"Col"
"Val"
"Val"

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). Another workaround if you did not know how many properties your objects had would be to loop over them and discover their properties using .PSObject.Properties.Name:

$test | ForEach-Object -Begin {
    $props = [System.Collections.Generic.HashSet[string]]::new(
        [System.StringComparer]::InvariantCultureIgnoreCase
    )
} -Process {
    foreach($i in $_.PSObject.Properties.Name)
    {
        $null = $props.Add($i)
    }
} -End {
    $test | Select-Object ([object[]]$props) | Format-Table
}

CodePudding user response:

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 ')

  • Related