Searching a SQL dataset for a value

Welcome Forums General PowerShell Q&A Searching a SQL dataset for a value

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 7 years ago.

  • Author
  • #5727

    Points: 0
    Rank: Member

    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 🙁 :

    $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.

The topic ‘Searching a SQL dataset for a value’ is closed to new replies.