Gather information about computers from multiple CSV files

Question

I created a script to import several CSV files from various sources and one CSV file with a list of systems in it. the script searches each CSV file to see if the system exist in the file and if it does then write the property information to a variable. The challenge that I have ran into is there are 26,000 systems to search for and so far the script has been running for over 24 hours and just now over half way through. Any ideas on how to speed this up?

$Final = @()

#Import CSV files
$Systems = Import-Csv C:\Projects\Master.csv
$vCenter = Import-Csv C:\Projects\vcenter.csv
$Storage = Import-Csv C:\Projects\Storage.csv
$SCCM = Import-Csv C:\Projects\SCCM.csv
$Database = Import-Csv C:\Projects\Database.csv
$OldAD = Import-Csv C:\Projects\AD_Old.csv
$ADprod = Import-Csv C:\Projects\AD.csv

Write-Host "Import Complete!"
$N = 0

foreach ($System in $Systems)
{
    Write-Host "Line "  $N

    $Sys = New-Object System.Object
    $Sys | Add-Member -type NoteProperty -name "System Name" -value $System.Name

    #############################
    #Database information Compare
    #############################
    If ($Database.Name -contains $System.Name)
    {
        #Get the system information from the CSV file being compared
        $Domain = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "DomainName" -ExpandProperty "DomainName"
        $SQLin = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "SQLInstance" -ExpandProperty "SQLInstance"
        $Instance = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "Instance" -ExpandProperty "Instance"
        $OS = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "OS" -ExpandProperty "OS"
        $SQLver = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "SQLVersion" -ExpandProperty "SQLVersion"
        $SQLsp = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "SQLServicePack" -ExpandProperty "SQLServicePack"
        $SQLed= $Database | Where-Object { $_.Name -eq $System.name } | Select-object "SQLEdition" -ExpandProperty "SQLEdition"
        $OSsp = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "ServicePack" -ExpandProperty "ServicePack"
        $Arch = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "SystemArchitecture" -ExpandProperty "SystemArchitecture"
        $IP = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "IP" -ExpandProperty "IP"
        $Env = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "Environment" -ExpandProperty "Environment"
        $DBname = $Database | Where-Object { $_.Name -eq $System.name } | Select-object "DBName" -ExpandProperty "DBName"

        #Create a new record in the object for the System with the information from the CSV file.
        $Sys | Add-Member -type NoteProperty -name "Domain Name" -value $Domain -force
        $Sys | Add-Member -type NoteProperty -name "SQL Instance" -value $SQLin -force
        $Sys | Add-Member -type NoteProperty -name "Database Instance" -value $Instance -force
        $Sys | Add-Member -type NoteProperty -name "Database Name" -value $DBname -Force
        $Sys | Add-Member -type NoteProperty -name "Operating System" -value $OS -force
        $Sys | Add-Member -type NoteProperty -name "SQL Version" -value $SQLver -force
        $Sys | Add-Member -type NoteProperty -name "SQL Service Pack" -value $SQLsp -Force
        $Sys | Add-Member -type NoteProperty -name "SQL Edition" -value $SQLed -Force
        $Sys | Add-Member -type NoteProperty -name "Operating System Service Pack" -value $OSsp-Force
        $Sys | Add-Member -type NoteProperty -name "System Architecture" -value $Arch -Force
        $Sys | Add-Member -type NoteProperty -name "IP Address" -value $IP -Force
        $Sys | Add-Member -type NoteProperty -name "Environment" -value $Env -Force
        $Sys | Add-Member -type NoteProperty -name "In Database File" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In Database File" -value "No"
    }

    #############################
    #SCCM information Compare
    #############################
    If ($SCCM.Name -contains $System.Name)
    {
        #Get the system information from the CSV file being compared
        $IP = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "IP" -ExpandProperty "IP"
        $OS = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "OS" -ExpandProperty "OS"
        $Vendor = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Vendor" -ExpandProperty "Vendor"
        $Model = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Model" -ExpandProperty "Model"
        $Serial = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Serial" -ExpandProperty "Serial"
        $ServicePack = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Service Pack" -ExpandProperty "Service Pack"
        $OSBuild = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "OS deployed on" -ExpandProperty "OS deployed on"
        $Architecture = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Architecture" -ExpandProperty "Architecture"
        $LBTime = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Last Boot Time" -ExpandProperty "Last Boot Time"
        $LHW = $SCCM | Where-Object { $_.Name -eq $System.name } | Select-object "Last H W Scan" -ExpandProperty "Last H W Scan"


        #Create a new record in the object for the System with the information from the CSV file.
        $Sys | Add-Member -type NoteProperty -name "IP Address" -value $IP -force
        $Sys | Add-Member -type NoteProperty -name "Vendor" -value $Vendor -force
        $Sys | Add-Member -type NoteProperty -name "Operating System" -value $OS -force
        $Sys | Add-Member -type NoteProperty -name "Model" -value $Model -force
        $Sys | Add-Member -type NoteProperty -name "Serial Number" -value $Serial -force
        $Sys | Add-Member -type NoteProperty -name "Operating System Service Pack" -value $ServicePack -Force
        $Sys | Add-Member -type NoteProperty -name "Operating System Build" -value $OSBuild -Force
        $Sys | Add-Member -type NoteProperty -name "System Architecture" -value $Architecture -Force
        $Sys | Add-Member -type NoteProperty -name "SCCM - Last Boot Time" -value $LBTime -Force
        $Sys | Add-Member -type NoteProperty -name "SCCM - Last Hardware Scan" -value $LHW -Force
        $Sys | Add-Member -type NoteProperty -name "In SCCM" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In SCCM" -value "No"
    }

    #############################
    #Solarwinds information Compare
    #############################
    If ($Solarwinds.Name -contains $System.Name)
    {
        #Get the system information from the CSV file being compared
        $IP = $Solarwinds | Where-Object { $_.Name -eq $System.name } | Select-object "IP" -ExpandProperty "IP"
        $Vendor = $Solarwinds | Where-Object { $_.Name -eq $System.name } | Select-object "Vendor" -ExpandProperty "Vendor"
        $MachineType = $Solarwinds | Where-Object { $_.Name -eq $System.name } | Select-object "MachineType" -ExpandProperty "MachineType"
        $Device = $Solarwinds | Where-Object { $_.Name -eq $System.name } | Select-object "DeviceType" -ExpandProperty "DeviceType"
        $City = $Solarwinds | Where-Object { $_.Name -eq $System.name } | Select-object "City" -ExpandProperty "City"
        $Region = $Solarwinds | Where-Object { $_.Name -eq $System.name } | Select-object "Region" -ExpandProperty "Region"

        #Create a new record in the object for the System with the information from the CSV file.
        $Sys | Add-Member -type NoteProperty -name "IP Address" -value $IP -force
        $Sys | Add-Member -type NoteProperty -name "Vendor" -value $Vendor -force
        $Sys | Add-Member -type NoteProperty -name "Machine Type" -value $MachineType -force
        $Sys | Add-Member -type NoteProperty -name "Device Type" -value $Device -force
        $Sys | Add-Member -type NoteProperty -name "City" -value $City -force
        $Sys | Add-Member -type NoteProperty -name "Region" -value $Region -Force
        $Sys | Add-Member -type NoteProperty -name "In Solarwinds" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In Solarwinds" -value "No"
    }

    #############################
    #Storage information Compare
    #############################
    If ($Storage.Name -contains $System.Name)
    {
        #Get the system information from the CSV file being compared
        $Role = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "Role" -ExpandProperty "Role"
        $IP = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "IP" -ExpandProperty "IP"
        $Serial = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "SerialNumber" -ExpandProperty "SerialNumber"
        $State = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "Physical/Virtual" -ExpandProperty "Physical/Virtual"
        $City = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "City" -ExpandProperty "City"
        $Model = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "Model" -ExpandProperty "Model"
        $Region = $Storage | Where-Object { $_.Name -eq $System.name } | Select-object "Region" -ExpandProperty "Region"

        #Create a new record in the object for the System with the information from the CSV file.
        $Sys | Add-Member -type NoteProperty -name "Function" -value $Role -force
        $Sys | Add-Member -type NoteProperty -name "IP Address" -value $IP -force
        $Sys | Add-Member -type NoteProperty -name "Serial Number" -value $Serial -Force
        $Sys | Add-Member -type NoteProperty -name "Physical or Virtual" -value $State -force
        $Sys | Add-Member -type NoteProperty -name "City" -value $City -force
        $Sys | Add-Member -type NoteProperty -name "Model" -value $Model -force
        $Sys | Add-Member -type NoteProperty -name "Region" -value $Region -Force
        $Sys | Add-Member -type NoteProperty -name "In Storage File" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In Storage File" -value "No"
    }

    #############################
    #vCenter information Compare
    #############################
    If ($vCenter.Name -contains $System.Name)
    {
        $OS = $vCenter | Where-Object { $_.Name -eq $System.name } | Select-object "OS" -ExpandProperty "OS"
        $IP = $vCenter | Where-Object { $_.Name -eq $System.name } | Select-object "IP" -ExpandProperty "IP"
        $DNS = $vCenter | Where-Object { $_.Name -eq $System.name } | Select-object "DNS" -ExpandProperty "DNS"
        $Notes = $vCenter | Where-Object { $_.Name -eq $System.name } | Select-object "Notes" -ExpandProperty "Notes"
        $Contact = $vCenter | Where-Object { $_.Name -eq $System.name } | Select-object "Contact" -ExpandProperty "Contact"
        $Function = $vCenter | Where-Object { $_.Name -eq $System.name } | Select-object "Function" -ExpandProperty "Function"

        $Sys | Add-Member -type NoteProperty -name "Operating System" -value $OS -force
        $Sys | Add-Member -type NoteProperty -name "IP Address" -value $IP -force
        $Sys | Add-Member -type NoteProperty -name "DNS Name" -value $DNS
        $Sys | Add-Member -type NoteProperty -name "vCenter - Notes" -value $Notes -force
        $Sys | Add-Member -type NoteProperty -name "vCenter - Contact" -value $Contact -force
        $Sys | Add-Member -type NoteProperty -name "vCenter - Function" -value $Function -force
        $Sys | Add-Member -type NoteProperty -name "Is Virtual" -value "Yes" -Force
        $Sys | Add-Member -type NoteProperty -name "In vCenter" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In vCenter" -value "No"
        $Sys | Add-Member -type NoteProperty -name "Is Virtual" -value "No" -Force
    }

    #############################
    #Old AD information Compare
    #############################
    If ($OldAD.Name -contains $System.Name)
    {
        #Get the system information from the CSV file being compared
        $Description = $OldAD | Where-Object { $_.Name -eq $System.name } | Select-object "Description" -ExpandProperty "Description"
        $DNS = $OldAD | Where-Object { $_.Name -eq $System.name } | Select-object "DNSname" -ExpandProperty "DNSname"
        $LastLoc = $OldAD | Where-Object { $_.Name -eq $System.name } | Select-object "Last Known Location" -ExpandProperty "Last Known Location"
        $LastLog = $OldAD | Where-Object { $_.Name -eq $System.name } | Select-object "Last Logon Date" -ExpandProperty "Last Logon Date"
        $OS = $OldAD | Where-Object { $_.Name -eq $System.name } | Select-object "OS" -ExpandProperty "OS"
        $Contain = $OldAD | Where-Object { $_.Name -eq $System.name } | Select-object "Container" -ExpandProperty "Container"

        #Create a new record in the object for the System with the information from the CSV file.
        $Sys | Add-Member -type NoteProperty -name "Description" -value $Description -force
        $Sys | Add-Member -type NoteProperty -name "DNS Name" -value $DNS -force
        $Sys | Add-Member -type NoteProperty -name "Old Active Directory - Last Known Location" -value $LastLoc -Force
        $Sys | Add-Member -type NoteProperty -name "Old Active Directory - Last Logon" -value $LastLog -force
        $Sys | Add-Member -type NoteProperty -name "Operating System" -value $OS -force
        $Sys | Add-Member -type NoteProperty -name "Old Active Directory - Container" -value $Contain -force
        $Sys | Add-Member -type NoteProperty -name "In Old Active Directory File" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In Old Active Directory File" -value "No"
    }

    #############################
    #Production AD information Compare
    #############################
    If ($ADprod.Name -contains $System.Name)
    {
        #Get the system information from the CSV file being compared
        $Description = $ADprod | Where-Object { $_.Name -eq $System.name } | Select-object "Description" -ExpandProperty "Description"
        $LastLoc = $ADprod | Where-Object { $_.Name -eq $System.name } | Select-object "Last Known Location" -ExpandProperty "Last Known Location"
        $LastLog = $ADprod | Where-Object { $_.Name -eq $System.name } | Select-object "Last Logon Date" -ExpandProperty "Last Logon Date"
        $OS = $ADprod | Where-Object { $_.Name -eq $System.name } | Select-object "OS" -ExpandProperty "OS"
        $Contain = $ADprod | Where-Object { $_.Name -eq $System.name } | Select-object "Container" -ExpandProperty "Container"

        #Create a new record in the object for the System with the information from the CSV file.
        $Sys | Add-Member -type NoteProperty -name "Active Directory - Description" -value $Description -force
        $Sys | Add-Member -type NoteProperty -name "Production Active Directory - Last Known Location" -value $LastLoc -Force
        $Sys | Add-Member -type NoteProperty -name "Production Active Directory - Last Logon" -value $LastLog -force
        $Sys | Add-Member -type NoteProperty -name "Operating System" -value $OS -force
        $Sys | Add-Member -type NoteProperty -name "Production Active Directory - Container" -value $Contain -force
        $Sys | Add-Member -type NoteProperty -name "In Production Active Directory File" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In Production Active Directory File" -value "No"
    }

    $Final += $Sys
    $N++
}

Write-Host "Compare Complete"

\$\begingroup\$ Cross-posted from Stack Overflow \$\endgroup\$
– Mathieu Guindon
Oct 19, 2016 at 20:27 — Mathieu Guindon, Oct 19, 2016 at 20:27

wOxxOm · Accepted Answer · 2016-10-19 17:43:01Z

Currently your code recreates $Final array in each iteration, scans each of the CSV files entirely in each iteration for each field using Where with a scriptblock(!), copies the $Sys object on each field (NoteProperty) addition.

All of the above are extremely expensive operations. Instead do the following:

Build hashtables from the source CSV files with the name field as a key, thus providing almost instant lookup instead of scanning the entire CSV array.
Build a hashtable of properties to add to the final object, then convert it to a custom object in just one operation at the end of the foreach body.
Use foreach statement as an expression so that its output is accumulated in the final array automatically without recreating the array in each iteration.

# import CSV as hashtables

$Systems = Import-Csv C:\Projects\Master.csv

function Index-Csv([string]$file) {
    $csv = @{}
    foreach ($rec in (Import-Csv $file)) {
        $csv[$rec.name] = $rec
    }
    $csv
}

$vCenter = Index-Csv C:\Projects\vcenter.csv
$Storage = Index-Csv C:\Projects\Storage.csv
$SCCM = Index-Csv C:\Projects\SCCM.csv
$Database = Index-Csv C:\Projects\Database.csv
$OldAD = Index-Csv C:\Projects\AD_Old.csv
$ADprod = Index-Csv C:\Projects\AD.csv

# process

$Final = foreach ($System in $Systems) {
    $Sys = @{"System Name" = $System.Name}

    if ($rec = $Database[$System.Name]) {
        $Sys += @{
            "Domain Name"                   = $rec.DomainName
            "SQL Instance"                  = $rec.SQLInstance
            "Database Instance"             = $rec.Instance
            "Database Name"                 = $rec.DBName
            "Operating System"              = $rec.OS
            "SQL Version"                   = $rec.SQLVersion
            "SQL Service Pack"              = $rec.SQLServicePack
            "SQL Edition"                   = $rec.SQLEdition
            "Operating System Service Pack" = $rec.ServicePack
            "System Architecture"           = $rec.SystemArchitecture
            "IP Address"                    = $rec.IP
            "Environment"                   = $rec.Environment
            "In Database File"              = "Yes"
        }
    } else {
        $Sys += @{"In Database File" = "No"}
    }

    #########################################
    # other CSV are processed just the same #
    #########################################

    [PSCustomObject]$Sys # PowerShell 3.0
    #New-Object PSObject -property $Sys # PowerShell 2.0
}

The above code shows only $Database, the other CSV tables are processed exactly the same.

It's also possible to speedup CSV import a lot by parsing the file directly. — wOxxOm, Oct 19, 2016 at 20:18
I changed the code to the above recommendations but, when I go to export the $final object to CSV I get: — texnoob, Oct 19, 2016 at 23:34
'IsReadOnly IsFixedSize IsSynchronized Keys Values SyncRoot Count FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 10 FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 10 FALSE FALSE FALSE System.Collections.Hashtable+KeyCollection System.Collections.Hashtable+ValueCollection System.Object 10' — texnoob, Oct 19, 2016 at 23:35
Looks like you have PowerShell2.0 and didn't notice the commented out line #New-Object PSObject -property $Sys # PowerShell 2.0. Remove the first # to uncomment it and remove the preceding line which is only for PS3.0. Always read the code posted online thoroughly before running it. — wOxxOm, Oct 20, 2016 at 2:11
Thanks for all the help. That has worked for the export the only issue that I'm running into now is the other areas are not being written to the object, the only parts that are being written are if it exists in the file or not example the Operating System is not being written. — texnoob, Oct 23, 2016 at 16:25

Stack Exchange Network

current community

your communities

more stack exchange communities

Gather information about computers from multiple CSV files

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged time-limit-exceeded csv powershell join or ask your own question.

Hot Network Questions

Gather information about computers from multiple CSV files

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged time-limit-exceeded csv powershell join or ask your own question.

Related

Hot Network Questions