Searching a SQL dataset for a value

PowerShell Help
1

by Dwayne Dibbley at 2013-03-27 08:56:51

I have the following code that pulls a list of currently managed nodes from our operations manage database, then i am generating a list of all vm’s and looping through to find if the vm name is found in the sql list, it sort of works but still give false results. I have tried the -contains and dataset.select but to no avail :frowning: :

$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = "Server=SQL;Database=openview;Integrated Security=True"
$Connection.Open()

$Query = "SELECT SUBSTRING(a.object_text,CHARINDEX(‘n = "',a.object_text)+5,(CHARINDEX('Certif',a.object_text) - CHARINDEX('n = "’,a.object_text)-9)) as NodeName FROM sto_ov_managednode a ORDER BY NodeName"

$Command = New-Object System.Data.SqlClient.SqlCommand
$Command.Connection = $Connection
$Command.CommandText = $Query

$Reader = $Command.ExecuteReader()

$OMDataTable = New-Object System.Data.DataTable
$OMDataTable.Load($Reader)

$OMDataTable
#$sqlConnection.Close()

Set-PowerCLIConfiguration -DefaultVIServerMode Multiple -Confirm:$false | out-null

Connect-VIServer -server VCS1,VCS2| out-null

$vms = Get-vm | where { $.PowerState -eq “PoweredOn” -and $.Guest.OSFullName -like "Windows"} | select Name | where { $.Name -like "*fred.com" }

foreach ($vm in $vms) {
foreach ($Row in $OMDataTable.Rows)
{
$result = "FALSE"
if ($vm.Name.trimend(".diti.lr.net").toupper() -eq $($Row[0]))
{
$result = "TRUE"
break
}
}
Write-Host $vm.Name.trimend(".fred.com").toupper() $result
}

Thanks
by DonJ at 2013-03-27 09:37:59
-Contains matches entire objects - so you’d have to compare it to a complete row. That’s not usually practical.

So… a couple of observations. In this:


$vms = Get-vm | where { $.PowerState -eq “PoweredOn” -and $.Guest.OSFullName -like "Windows"} | select Name | where { $.Name -like "*fred.com" }


I guess I’m not clear why you didn’t just do this:


$vms = Get-vm | where { $.PowerState -eq “PoweredOn” -and $.Guest.OSFullName -like "Windows" -and $.Name -like "*fred.com" }


And I’m guessing you’re mainly asking about this:


if ($vm.Name.trimend(".diti.lr.net").toupper() -eq $($Row[0]))


Looking at this statically, I’d say your logic is sound. But I don’t really know what the data looks like. If you add $VerbosePreference=‘Continue’ to the top of your script, and then:


Write-Verbose "Name is ‘$($vm.name) and row is $($Row[0])"
Write-Verbose "Trimmed name is $($vm.name.trimend(’.diti.lr.net’).toupper())"
if ($vm.Name.trimend(".diti.lr.net").toupper() -eq $($Row[0]))


It’s a little easier, perhaps to see what’s going wrong.

That said, datasets aren’t really well-designed for this exact purpose. It might be easier to get your SQL result, and then just loop through it once, building an array of string objects.


$names = @()
foreach ($Row in $OMDataTable.Rows)
$names += $Row[0]
}


You could then use:


foreach ($vm in $vms) {
$vmname = $vm.Name.trimend(".diti.lr.net").toupper()
if ($names -contains $vmname) {}
}


So instead of monkeying around with the dataset, you’ve just got basic strings, which is what you’re really trying to compare. So long as (in my example) $vmname is a string, and $names includes only string objects, then -contains should work reliably.
by MasterOfTheHat at 2013-03-27 09:39:56
Not where I can test it, but try this:
$vms | ForEach-Object {
if ($OMDataTable.Select(NodeName = "$.Name.TrimEnd('.diti.lr.net')")) {
"$.Name.TrimEnd('.diti.lr.net').ToUpper() TRUE"
}
else
{
"$.Name.TrimEnd('.diti.lr.net').ToUpper() FALSE"
}
}
Also, it would be helpful to know what kind of output you’re getting. That may help us figure out what’s going on.