Filtering and processing computers listed in a CSV file

Question

I am trying to make my script run a lot faster. I have a csv that has about 330k of lines on it that I plan on running my script with and it has been taking an extremely long time. I just did ran the script and it 50 mins to generate the new csv. What the code does is it adds 2 more fields and filters only the administrators and The windows operating systems. Below are before and after pictures of the output.

$csv = Import-Csv 'U:\Local Group Members.csv' |
Where-Object {($_.Name0 -eq "administrators") -and ($_.caption0 -match "Microsoft Windows 10 Enterprise|Microsoft Windows 7 Enterprise|Microsoft Windows 7 Professional|Microsoft Windows 8 Enterprise|Microsoft Windows 8 Pro|Microsoft Windows 8.1 Enterprise|Microsoft Windows 8.1 Pro")} |
Select-Object "Netbios_name0", "Name0", "Account0","category0","Domain0","Unique Account Name","Type0","caption0", "Excluded"
#Modify each line
Foreach ($row in $csv) {
If ($row.Type0 -eq 'Domain') { 
    $row."Unique Account Name" = "$($row.Domain0) - $($row.Account0)" 
    If ($row."Unique Account Name" -in @('ACCOUNTS - DODSCAN'.'ACCOUNTS - Domain Admins','ACCOUNTS - LADM_WS_Admins','ACCOUNTS - Tech Enterprise'))
         {$row."Excluded" = "True"}
    Else {$row."Excluded" = "False"}        
}
Else {
    $row."Unique Account Name" = "$($row.Netbios_name0) - $($row.Account0)"
    If ($row."Account0" -in @('esrxadm1n_esi','#Update','medco_tech','medco_admin'))
         {$row."Excluded" = "True"}
    Else {$row."Excluded" = "False"}
}
    Write-Host $row."Unique Account Name"
    Write-Host $row."Excluded"    
}

#Export CSV
$csv | Export-Csv U:\$(get-date -f yyyy-MM-dd-hh-mm)-TestOutput1.csv -NoTypeInformation 

Can someone help me make my script faster?

Answer 1

As far as performance goes there are a few places to make changes.

Make better use of the pipeline

You read in the csv to a variable then process. Convert the script to process these records directly. There would be performance increases this way. Insanely brief example...

Import-Csv 'U:\Local Group Members.csv' |
   Where-Object {($_.Name0 -eq "administrators")}
   Select-Object "Netbios_name0", "Name0" | 
   ForEach-Object {
       # Something happens. 
} | Export-Csv ....

Wasted console output

I cannot imagine you are checking ~1000's of lines of console output. You are wasting processing time but putting that data to console. Remove the write-host lines. Even if you have this running as a task.

Where-Object is slow

This is really easy to use but is not very performance friendly. I can tell you have at least PowerShell 3.0 but if you have at least v4 then you have access to the where and foreach methods of collections. The syntax as you will see is very similar but there should be performance boons here as well. Example:

(Import-Csv -Path C:\temp\devices.csv).where({$_.DeviceType -ne "BlackBerry"})

I think you are already doing this but order your conditions in order of least likely to most likely. PowerShell will not evaluate the RHS of -and if the LHS is already false.

---

I tried to improve the logic in your regex but it was at a cost of performance everytime. As redundant as that string is it seems to be a faster choice given other options.