Searching a SQL dataset for a value

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

  • Author
    Posts
  • #5727

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

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

You must be logged in to reply to this topic.