0

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"
Improve this question
3
  • 1
    Take a look at powershell Jobs. Start for each system a job Commented Oct 19, 2016 at 14:36
  • 1
    This seems like a good fit for CodeReview.SE. You should really give some sample data for this if you have any scrubbed. I think there is somethings that can be definitely be done to speed this up. Commented Oct 19, 2016 at 15:43
  • 1
    CodeReview: link. My answer shows how to speedup the code thousands of times. Commented Oct 19, 2016 at 20:24

2 Answers 2

Reset to default
2

Please keep in mind that every time you're doing Where-Object you are process the entire CSV (or its contents). You can simplify your lookups to one statement.

For example, your DB section can be simplified to the following. This will reduce to number of loops(or iteration) over $Database CSV from 12 to 1.

    #############################
    #Database information Compare
    #############################

    $DBLookup = $Database | Where-Object { $_.Name -eq $System.Name} | Select-Object DomainName,SQLInstance,Instance,OS,SQLVersion,SQLServerPack,SQLEdition,ServicePack,SystemArchitecture,IP,Environment,DBName

    if($DBLookup -ne $null)
    {
        $Sys | Add-Member -type NoteProperty -name "Domain Name" -value $DBLookup.DomainName -force
        $Sys | Add-Member -type NoteProperty -name "SQL Instance" -value $DBLookup.SQLInstance -force
        $Sys | Add-Member -type NoteProperty -name "Database Instance" -value $DBLookup.Instance -force
        $Sys | Add-Member -type NoteProperty -name "Database Name" -value $DBLookup.DBname -Force
        $Sys | Add-Member -type NoteProperty -name "Operating System" -value $DBLookup.OS -force
        $Sys | Add-Member -type NoteProperty -name "SQL Version" -value $DBLookup.SQLversion -force
        $Sys | Add-Member -type NoteProperty -name "SQL Service Pack" -value $DBLookup.SQLServicePack -Force
        $Sys | Add-Member -type NoteProperty -name "SQL Edition" -value $DBLookup.SQLEdition -Force
        $Sys | Add-Member -type NoteProperty -name "Operating System Service Pack" -value $DBLookup.ServicePack -Force
        $Sys | Add-Member -type NoteProperty -name "System Architecture" -value $DBLookup.SystemArchitecture -Force
        $Sys | Add-Member -type NoteProperty -name "IP Address" -value $DBLookup.IP -Force
        $Sys | Add-Member -type NoteProperty -name "Environment" -value $DBLookup.Environment -Force
        $Sys | Add-Member -type NoteProperty -name "In Database File" -value "Yes"
    }
    Else
    {
        $Sys | Add-Member -type NoteProperty -name "In Database File" -value "No"
    }

You can do the same for other sections. This should help.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

For start use where method than piped to where-object.

$Domain = $Database | Where-Object { $_.Name -eq $System.name } ...

will be 

$Domain = $Database.Where{ $_.Name -eq $System.name } ...
Improve this answer

2 Comments

While I don't know for sure I can't imagine that this is going to make much of a noticeable change. OP needs to have at least PowerShell 3 or 4 for that to work. There are other more obvious changes that should be made first.
I don't have a time to analyse the script but I test it on my on scripts and it give from 3 to 8 times quicker filtering for files with 30-40,000 to more than 100,000 lines (in my case transactions logs). And filtering is by 2 criteria. With CSV it should be even faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.