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"