question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked RichMatheisen-8856 answered

Comparing two arrays in powershell and not using a database

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 ?

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered MrFlinstone-1451 commented

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.$_
         }
     }    
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for the snippet of code provided, this has given me a better understanding and perhaps a reason to try out a different approach.

The new approach is this.

  1. Get a list of decommissioned servers from the database and load it into an array/custom object.

  2. Import the csv file into an array.

  3. Remove any matches from the csv file import that matches the decommissioned list and save this into a new array/custom object.

  4. My preference is to not use a database for comparison, hence this approach.

As per the details above, table file etc. I couldn't get this to work.

  $data = @{}
  Import-Csv $file -Header 'connection_string' |
      Foreach-Object {
          $data[$_.connection_string]  
      }
  $filtered_list = $decomissioned_list |
      ForEach-Object{
          if (!($data.ContainsKey($_))){
              [PSCustomObject]@{
                  servername = $_
              }
          }
      }





0 Votes 0 ·
RichMatheisen-8856 avatar image RichMatheisen-8856 MrFlinstone-1451 ·

On line #4 you've only supplied the key to create in the $data hash table. You have to supply a value, too. If you only want to use the hash to check if a key exists then just assign an arbitrary value; something like an empty string "", the digit 1, etc.

A connection string usually has more to it than a server name, though. Or have you just used 'connection_string' as a column name and it only holds the name of the server?

Depending on how you use the $filtered_list, if it only contains the name of servers you can replace lines 9-11 with just $_ . By using a PSCustomObject you'd have to reference the property name (servername) to get at the name of the server. I used it in my example because I returned two properties.

0 Votes 0 ·
MrFlinstone-1451 avatar image MrFlinstone-1451 RichMatheisen-8856 ·

I have done that whilst testing, still cannot get it to work.

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.