3
\$\begingroup\$

I am managing large CSV files (files ranging from 750 Mb to 10+ Gb), parsing their data into PSObjects, then processing each of those objects based on what is required.

I wrote the following script to churn through these files line by line, filter based on one of the data fields, then close the file. The script works but I feel that it could be faster. For instance, it took 4.5 hours to parse a 389k line csv file. Taking that filesize and timeline, it would take over two and a half days to work through just the sorting and filtering of the data!

Before anyone suggests "use a database!", I'm 100% with you and have started the RFC for adding a database server to our network. Unfortunately our CAB only meets quarterly and this wasn't deemed an emergency. So, I'm left without a database solution for at least 2-3 months.

Anyhow, here is the code:

[void][reflection.assembly]::LoadWithPartialName("Microsoft.VisualBasic")

$source = Get-FileName "C:\users\$env.username\Downloads"

$reader = New-Object Microsoft.VisualBasic.FileIO.TextFieldParser $source

$reader.SetDelimiters(",")

While(!$reader.EndOfData)
{
    $line = $reader.ReadFields()    

    $details = [ordered]@{
                            "Plugin ID" = $line[0]
                            CVSS = $line[2]
                            Risk = $line[3]
                            Host = $line[4]
                            Protocol = $line[5]
                            Port = $line[6]
                            Name = $line[7]
                            Description = $line[9]
                            Solution = $line[10]
                            "Plugin Output" = $line[12]
                         }


    $pluginID = $line[0]
    $risk = $line[3]

    if ($risk -eq "Critical" -or $risk -eq "High" -or $risk -eq "Medium" -or $risk -eq "Low")
    {
        $allVulns += New-Object PSObject -Property $details 
    }
    else
    {
        # Filters data into objects based on their plugin ID
        Switch ($pluginID)
        {
            11936 # OS Identification
            {
                $11936 += New-Object PSObject -Property $details
                break
            }
            14272 # Open Ports
            {
                $14272 += New-Object PSObject -Property $details
                break
            }
            20811 # Software Inventory
            {
                $20811 += New-Object PSObject -Property $details
                break
            }
            54615 # Device Type
            {
                $54615 += New-Object PSObject -Property $details
                break
            }
            66334 # Missing OS Patches
            {
                $66334 += New-Object PSObject -Property $details
                break
            }
        }
    }
}

# Close the read file
$reader.Close()

Is there a faster, more efficient way for this code to execute?

Thanks!

EDIT: Some sample data by request! This is two of the plugins I'm parsing that don't have endless amounts of Plugin Output data.

Plugin ID,CVE,CVSS,Risk,Host,Protocol,Port,Name,Synopsis,Description,Solution,See Also,Plugin Output
11936,,,None,GCAB-L7-449096R,tcp,0,OS Identification,It is possible to guess the remote operating system.,"Using a combination of remote probes (e.g., TCP/IP, SMB, HTTP, NTP,
SNMP, etc.), it is possible to guess the name of the remote operating
system in use. It is also possible sometimes to guess the version of
the operating system.",n/a,,"
Remote operating system : Microsoft Windows 7 Enterprise Service Pack 1
Confidence level : 100
Method : SMB


The remote host is running Microsoft Windows 7 Enterprise Service Pack 1"
14272,,,None,GCAB-L7-449096R,udp,53125,netstat portscanner (SSH),Remote open ports are enumerated via SSH.,"This plugin runs 'netstat' on the remote machine to enumerate open
ports.

See the section 'plugins options' about configuring this plugin.",n/a,https://en.wikipedia.org/wiki/Netstat,Port 53125/udp was found to be open
\$\endgroup\$
  • \$\begingroup\$ Can you post a small sample of the CSV file? \$\endgroup\$ – Adamar Sep 10 '16 at 12:44
  • \$\begingroup\$ Added sample data to the original post. \$\endgroup\$ – Tchotchke Sep 12 '16 at 15:25
2
\$\begingroup\$

It might be a little hard to make a suggestion about this as I cannot see how the data is being used beyond this. However I can point out the large performance flaw in your logic.

Building arrays with +=

You have multiple lines of code that go like this:

$20811 += New-Object PSObject -Property $details

What is actually happening there is PowerShell is destroying the array $20811 and creating a new one that is one element larger to house the data on the right hand side. For small operations this performance hit is negligible. However you will certainly feel it when you get into the thousands of elements. You are rebuilding new arrays with every operation.

Consider array lists

I would instead just create all of the custom objects in one pass into one large variable instead. Then you could use Where-Object to process the groups of rows as you see fit. Or you can still keep them as separate objects. Either way I would use an arraylist instead.

$11936 = New-Object System.Collections.ArrayList

# truncated ....
11936 # OS Identification
{
    [void]($11936.Add($details))
    break
}

The [void] cast suppresses the output created from the Add method

This should be significantly faster.

Read the CSV as a database

There are methods to read the CSV as a database as well. The one I tested was using the Microsoft.ACE.OLEDB.12.0 provider. This method allows you to use SQL statements against the CSV file. I don't really have the time to properly benchmark this but this should be faster than your current method as well. Consider a simple example using mock "employee" data:

@"
id,first_name,last_name,email,ip_address
1,Edward,Richards,erichards0@businessweek.com,201.133.112.30
2,Jimmy,Scott,jscott1@clickbank.net,103.231.149.144
3,Marilyn,Williams,mwilliams2@chicagotribune.com,52.180.157.43
4,Frank,Morales,fmorales3@google.ru,218.175.165.205
5,Chris,Watson,cwatson4@ed.gov,75.251.1.149
6,Albert,Ross,aross5@abc.net.au,89.56.133.54
7,Diane,Daniels,ddaniels6@washingtonpost.com,197.156.129.45
8,Nancy,Carter,ncarter7@surveymonkey.com,75.162.65.142
9,John,Kennedy,jkennedy8@tumblr.com,85.35.177.235
10,Bonnie,Bradley,bbradley9@dagondesign.com,255.67.106.193
"@ | Set-Content .\test.csv 

$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Cameron';Extended Properties='Text;HDR=Yes;FMT=Delimited';")
$cmd=$conn.CreateCommand()
$cmd.CommandText="Select * from test.csv where first_name like '%n%'"
$conn.open()
$data = $cmd.ExecuteReader()

$data | ForEach-Object{
    [pscustomobject]@{
        id=$_.GetValue(0) 
        first_name=$_.GetValue(1) 
        last_name=$_.GetValue(2)
        ip_address=$_.GetValue(4)
    }
}

$cmd.Dispose()
$conn.Dispose()

I have a SQL statement in there that will return all the records where the first name has a "n" in it. Using the field indecies we build a custom psobject that gets sent down the pipe.

Note that the source in the connection string is the folder that contains the csv file. And the table in the SQL statement is the CSV file name.

\$\endgroup\$
  • \$\begingroup\$ Once all the arrays are created I call a different script for each object and parse the various columns for whatever data the plugin captures (see the original post for recently added sample data). The actual creation of the reports is of acceptable speed and certainly a lesser concern at the moment for me. I am going to modify the script to use your suggestion of an ArrayList though. I've only used PS for about a month so I'm still learning. This script was put together because the C-level people needed something to look at and it fell to me to give them something. \$\endgroup\$ – Tchotchke Sep 12 '16 at 15:28
  • \$\begingroup\$ Reading the CSV as s database via OleDb seems to very smart performance wise. Chrissy LeMaire used the same technique to import a CSV to a SQL server DB: blog.netnerds.net/2015/01/… \$\endgroup\$ – Adamar Sep 12 '16 at 16:04
  • \$\begingroup\$ @Matt, thanks for the suggestion of .Add()! Sped the code up from 4.5 hours to a little over 100 seconds! I have some downstream changes to make now but those are easy-peasy. Thanks again! \$\endgroup\$ – Tchotchke Sep 13 '16 at 16:23
  • \$\begingroup\$ @Tchotchke Glad to help \$\endgroup\$ – Matt Sep 13 '16 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.