Searching a SQL dataset for a value

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 but to no avail :frowning: :

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

$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


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 "*" }

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

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 "*" }

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 "*" }

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

if ($vm.Name.trimend("").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 ‘$($ and row is $($Row[0])"
Write-Verbose "Trimmed name is $($’’).toupper())"
if ($vm.Name.trimend("").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("").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('')")) {
"$.Name.TrimEnd('').ToUpper() TRUE"
.Name.TrimEnd('').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.