powershelldbatools

Issue creating dynamic Menu when only 1 result - using DBATools

I'm banging my head on this issue and can't seem to figure out what I'm doing wrong, or why I get the results.

I'm using DBATools to gather SQL instances on a remote server (excluding SSRS if there).

Portion of the script is:

            Get-DBATools
            
            $SQLInstInfo = Find-DbaInstance -computername $Computer | Where { ($_.BrowseReply -NE $Null) }
            
            $menu = @{ }
            for ($i = 1; $i -le $SQLInstInfo.BrowseReply.SQLInstance.count; $i++)
            {
                Write-Color "`n $i. ", "$($SQLInstInfo.BrowseReply.SQLInstance[$i - 1]) " -Color White, Green
            
                $menu.Add($i, ($SQLInstInfo.BrowseReply.SQLInstance[$i - 1]))
            }
            
            [int]$ans = $(Write-Color "`nType the ", "Number ", "of the ", "SQL Instance ","desired: " -Color White, Green, White, Yellow, White -NoNewline; Read-Host)
            $Instance = $menu.Item($ans)

Previously I was just pulling the Instance info from registry, but that wasn't providing proper instance info if it was a SQL Failover Cluster, so I've swapped to using DBATools.

Anyway, if there are multiple instances, it works perfectly.

multi instances

But if there's only one instance, I get just the first letter of the instance name.

single instance

I'm just not seeing what I've done wrong. I've tried several things, but nothing is panning out. I'm hoping someone can point me in the right direction.

I know if I take out the [$i - 1], then for a single instance it would work, but when there are multiple instances, it would stick them all together. I guess I could do a If/Then setup to check amount of instances first, and then do it with/without [$i - 1], but that just seems illogical.

Any insight or advice is most appreciated.

Solution

  • PowerShell likes to "flatten" pipeline output, so when Find-DbaInstance only returns 1 instance, the resulting variable value is no longer an array.

    Use the array sub-expression operator @(...) to ensure the value assigned to $SQLInstInfo is an array regardless of how many instances Find-DbaInstance ... |Where-Object ... returns:

    $SQLInstInfo = @(Find-DbaInstance -computername $Computer | Where { $null -ne $_.BrowseReply })

    Additionally you want to change the loop to iterate over $SQLInstInfo rather than the enumerated expression $SqlInstInfo.BrowseReply.SQLInstance - which will also get flattened when only a single value is resolved:

    for ($i = 1; $i -le $SQLInstInfo.Count; $i++) {
    $instanceName = $SQLInstInfo[$i - 1].BrowseReply.SQLInstance
    Write-Color "`n ${i}. ", "${instanceName} " -Color White, Green

    $menu.Add("$i", $instanceName)
}