Comparing two arrays in powershell and not using a database

Question

I am looking to compare 2 arrays, whilst I have a solution that uses a database as a way to compare data. I would like to limit the use of the database and do this purely within powershell in memory. Here is the scenario. I have a file which contains a list of servers and I can do a lookup on the database to exclude servers that have been decommissioned. What I will like to end up with is a new array which consists of servers that have not been decommissioned after doing a lookup on the database. I can achieve this by running the below, but I will like to know if there is a better way to do this purely in powershell, for example using a simple hash table etc.

Create a csv file which the contents can be see below.

server1,6789  
server2,6789  
server3,9999  

The table on the database is called server_list with the definition below.

create table server_list  
(  
servername varchar(500),  
decommissioned varchar(1)  
)  
  
  
insert into server_list  
select 'server1,6789','N'  
union all  
select 'server2,6789','N'  
union all  
select 'server3,9999','Y'  

The powershell script below will import the data onto a table called import_Table, but this doesn't work well because the data in the file has a comma delimited port number which is not a seperate column. This is one limitation with the script, works well if the port number and comma are removed.

#Read csv file   
#open connection to database using bulkcopy  
#convert array to data table  
#bulkload data into table  
#note: column sequence and data types should match  
  
function Get-Type   
{   
    param($type)   
   
$types = @(   
'System.Boolean',   
'System.Byte[]',   
'System.Byte',   
'System.Char',   
'System.Datetime',   
'System.Decimal',   
'System.Double',   
'System.Guid',   
'System.Int16',   
'System.Int32',   
'System.Int64',   
'System.Single',   
'System.UInt16',   
'System.UInt32',   
'System.UInt64')   
   
    if ( $types -contains $type ) {   
        Write-Output "$type"   
    }   
    else {   
        Write-Output 'System.String'   
           
    }   
}  
  
  
function Out-DataTable   
{   
    [CmdletBinding()]   
    param([Parameter(Position=0, Mandatory=$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject)   
   
    Begin   
    {   
        $dt = new-object Data.datatable     
        $First = $true    
    }   
    Process   
    {   
        foreach ($object in $InputObject)   
        {   
            $DR = $DT.NewRow()     
            foreach($property in $object.PsObject.get_properties())   
            {     
                if ($first)   
                {     
                    $Col =  new-object Data.DataColumn     
                    $Col.ColumnName = $property.Name.ToString()     
                    if ($property.value)   
                    {   
                        if ($property.value -isnot [System.DBNull]) {   
                            $Col.DataType = [System.Type]::GetType("$(Get-Type $property.TypeNameOfValue)")   
                         }   
                    }   
                    $DT.Columns.Add($Col)   
                }     
                if ($property.Gettype().IsArray) {   
                    $DR.Item($property.Name) =$property.value | ConvertTo-XML -AS String -NoTypeInformation -Depth 1  
                }     
               else {   
                    $DR.Item($property.Name) = $property.value   
                }   
            }     
            $DT.Rows.Add($DR)     
            $First = $false   
        }   
    }    
        
    End   
    {   
        Write-Output @(,($dt))   
    }   
   
} #Out-DataTable  
  
$file="C:\temp\sql_list.csv"  
$dbserver="DESKTOP-OIM9QM0\DEV2"  
$database="Test"  
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=$dbserver;Integrated Security=SSPI;Initial Catalog=$database");  
$cn.Open()  
$bc = new-object ("System.Data.SqlClient.SqlBulkCopy") $cn  
$bc.BatchSize = 10000;  
$bc.BulkCopyTimeout = 1000  
$bc.DestinationTableName = "import_table"  
  
$data = Import-Csv $file -Header 'servername' | Out-DataTable  
  
$bc.WriteToServer($data)  
  
$final_list = Invoke-Sqlcmd -serverInstance "localhost\DEV2" -Database "Test" -Query "select servername from import_table where servername in (select servername from server_list where decommissioned = 'N')"  

What I want to end up with is $final_list which is an array that contains a list of all my servers,port number which passed the lookup, that is not decommissioned servers.

Is the method shown above the best way to do this or is there a better solution ?

Answer 1

Adding this at the end of you script might work (pick one that matches your requirements!):

# Export the disabled servers to a CSV
$data = @{}
Import-Csv $file -Header 'servername','port' |
    Foreach-Object {
        $data[$_.servername] = $_.port
    }
$final_list |
    ForEach-Object{
        if ($data.ContainsKey($_)){
            [PSCustomObject]@{
                servername = $_
                port = $data.$_
            }
        }
    } | Export-Csv SomePathNameForDisabledServers -NoTypeInformation

# OR ------
# create an array of PSCustomObjects
$data = @{}
$disabled = @()
Import-Csv $file -Header 'servername','port' |
    Foreach-Object {
        $data[$_.servername] = $_.port
    }
$final_list |
    ForEach-Object{
        if ($data.ContainsKey($_)){
            $disabled += [PSCustomObject]@{
                        servername = $_
                        port = $data.$_
                    }
        }
    }

# OR create an array of strings
$data = @{}
$disabled = @()
Import-Csv $file -Header 'servername','port' |
    Foreach-Object {
        $data[$_.servername] = $_.port
    }
$final_list |
    ForEach-Object{
        if ($data.ContainsKey($_)){
            $disabled += "{0},{1}" -f $_, $data.$_
        }
    }    

Answer 2

Let's say your CSV contains the names A, B, C, D, and E. In this code sample the $filtered_list will contain B, D, and E:

$file = "c:\junk\cs.csv"
$decomissioned_list = "A","C"


$data = @{}
Import-Csv $file -Header 'connection_string' |
    Foreach-Object {
        $data[$_.connection_string]  = $false
    }
# find servers that have NOT been decommissioned
$decomissioned_list |
    ForEach-Object{
        if ($data.ContainsKey($_)){
            $data.$_ = $true        # mark as decommissioned
        }
    }
$filtered_list = $data.GetEnumerator() |
                    ForEach-Object{
                        if (-not $_.Value){
                            [PSCustomObject]@{
                                servername = $_.Key
                            }
                        }
                    }

Again, if you need only a simple array of names you can just return the key instead of a PSCustomObject.

I think I missed the "NOT" part of your description about what the filtered list should contain in my earlier code example.