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 ?