Issue creating dynamic Menu when only 1 result - using DBATools

Question

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.

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

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.

Answer 1

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)
}

Answer 2

In case anyone stumbles across this...after using the provided answer (which worked great for what I was doing) I was having issues using the original $Instance = $menu.Item($ans)

I finally figured out a way to get it to work properly on all sql instances, so I could use the $Instance variable in many other places.

Final working code for this section of my script:

            $SQLInstInfo = @(Find-DbaInstance -computername $Computer | Where { $null -ne $_.BrowseReply })
            $menu = @{ }
            
            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)
            }
            
            [int]$ans = $(Write-Color "`nType the ", "Number ", "of the ", "SQL Instance ","desired: " -Color White, Green, White, Yellow, White -NoNewline; Read-Host)
        
            $Instance = $SQLInstInfo[$ans - 1].BrowseReply.SQLInstance