Export-csv - correlating columns and variable number of items per cell

Okay so, I’m trying to pull data from 100’s of text files → grab part of the text file that has their asset tag → grab a string ("log4j-core*.jar) → and then output those two items to a csv in separate columns. This script I made would work if the two items were 1:1 (file:string found) but it doesn’t work for my case. The problem is that the text files can have any number of that string so I need to find a way to relate the two so that, for example: when there is 5 matched strings in 1 file, it outputs 1 asset : 5 matched strings in ONE row. My current script is just outputting them 1:1. See below. I replaced any sensitive info with random numbers.

If you need clarification on any part of my script or want me to add comments, let me know.

$files = Get-ChildItem -Path '\\123.456.789\Log4J\logs' -filter '*.txt'

$version = @()
$asset = @()

foreach ($f in $files) 
   {if ((get-content -Path $f.FullName) -like "*log4j-core*") 
      {$asset += $f.Name -split "_scanlog.txt" ; $version += Get-Content -Path $f.fullname | Out-String -Stream | Select-String "log4j-core"}}

If ($asset.Count -gt $version.Count) {
    $limit = $asset.Count
} Else {
    $limit = $version.Count
}

$csv = For ($i = 0; $i -lt $limit; $i++) {
    New-Object -TypeName psobject -Property @{
        'Log4j Version' = $(If ($version[$i]) { $version[$i] })
        'Asset tag' = $(If ($asset[$i]) { $asset[$i] })
    }
}

$csv | Export-Csv -Path C:\Users\12341234\Desktop\log4j\test.csv -NoTypeInformation

Dan,
Welcome to the forum. :wave:t4:

What’s your actually question?

There are two ways of dealing with such a situation. Either you will have one asset multiple times if it has the search pattern multiple times. Or you have to put all search hits in one asset in one cell of your CSV.

BTW: In your foreach loop you’re querrying the content of each file twice. That’s unnecessary and time consuming. :wink:
And BTW: You can provide Select-String directly with a file path. You don’t have to read the content in advance. :wink:
And BTW: Please do not use backticks in scripts. That’s considered very bad style.

Olaf,

I see. I think I would prefer this format:

What changes would I need to make to my script so that they’re correlated like that?

Sorry about the backticks, querying 2x, I am still quite the novice with powershell. And the select-string bit is good to know, thank you Olaf.

BTW, I am about to leave work for the day but I will be replying again in the morning. I’ll remove the backticks for you before I leave for the day.

Thank you again!

I’d rather go for the other one because it would be easier to process further steps later on if needed. :wink: Anyway …

Because you did not share any example of your input files I don’t know what you’re actually after and how the results would look like in your environment.

I’d start with something like this:

Get-ChildItem -Path '\\123.456.789\Log4J\logs' -Filter '*.txt' |
ForEach-Object {
    $SearchHitList = Select-String -Path $_.FullName -SimpleMatch 'log4j-core' -AllMatches
    if ($SearchHitList) {
        [PSCustomObject]@{
            FileName    = $_.FullName
            HitCount    = $SearchHitList.Count
            Version     = $SearchHitList.Line -join ','
            LineNumbers = $SearchHitList.LineNumber -join ','
        }
    }
    $SearchHitList = ''
} |
Export-Csv -Path 'C:\Users\12341234\Desktop\log4j\test.csv' -NoTypeInformation

I’d recommend to get the output of Select-String for ONE file where you know it has the patterns you’re looking for and investigate what properties are useful for you.

Select-String -Path 'partticular file' -SimpleMatch 'log4j-core' -AllMatches | 
    Select-Object -Property *

Olaf, I’m going to do my best to try to describe precisely what it is that I’m after. I’ve attached one of the input files (with sensitive info obfuscated).

So if you look at the file you’ll see the computer name is in the name of the file. Within the contents of the file you’ll see a list of file paths. Most of the file paths are irrelevant because I didn’t write the batch file and it only searches for the keyword “log4j” instead of "log4j-core*.jar. However, in two of the lines it matches the desired string “log4j-core*.jar”, indicating two log4j jar files on the computer (if you’re curious, the versions of log4j that are vulnerable and of concern are 2.0 - 2.16 = both of the files in this case). The script will search through and return 1) the asset tag and 2) those two paths.

Now, the two full paths themselves aren’t particularly useful to me, they’re more there for documentation; I just want to be able to browse through the output CSV and see which assets contain any vulnerable versions.

Now as far as the format goes, I made a sample CSV that corresponds to the text file above. It actually won’t let me upload multiple files in one reply so I’m going to have to double post. Sorry!

See attached for the sample csv. This is precisely what I’m looking for. Primarily because it would be fewer lines and also because this is what my boss requested specifically. I do, however, like the idea of being able to customize it further later on!

Dan,
first of all - please stop posting pictures of code or text. When you ask someone for help you should try to make it as easy as possible to help you. If I have to type a lot of text to be able to reproduce a part of your situation I don’t like to help you anymore. :wink: If you post code or sample data poast them as text and format them as code.

Did you try the code suggestion I posted before? Did it work? If not - what exactly did not work?

What exactly will your boss do with the data he’s asking you to provide? In doubt YOU are the expert and it is your job to explain, why it is pointless to have data you cannot re-use later on. :wink:

You may take a look at the module from Doug Finke ImportExcel. It’ll allow you to create Excel-Sheet directly without beeing limitted to the CSV format. You may watch this video to learn more about. It is a little bit longer but I think it is worth it.

1 Like