Select values from Array based on name

Question

I have extracted some details of a SQL instance using:

$SQLInstance1 = Get-DbaInstanceProperty -SqlInstance SQL | Select-Object ComputerName, Name, Value  | Where-Object Name -In ("FullyQualifiedNetName", "Edition", "PhysicalMemory", "Processors", "VersionString", "Collation", "IsClustered", "ResourceLastUpdateDateTime")

What's the best way to assign these values to variables from an Array output like this:

ComputerName Name Value

SQL Edition Enterprise Edition (64-bit) SQL PhysicalMemory 1571485
SQL Processors 40
SQL VersionString 11.0.7493.4
SQL Collation Latin1_General_CI_AS
SQL IsClustered True
SQL ResourceLastUpdateDateTime 24/12/2019 9:58:55 PM
SQL FullyQualifiedNetName PTHSQL13.FMG.local

So I would want to assign $ComputerName = ComputerName, $Edition = Edition etc from above which I can then use to update a SQL table $ComputerName

score 1 · Accepted Answer · answered Feb 20 '21 at 03:51

Okay. First you need to convert the results to a Dictionary, and then create a new object using that dictionary of properties.

For example:

$dict = @{ }

(Get-DbaInstanceProperty -SqlInstance localhost | Select-Object ComputerName, Name, Value | Where-Object Name -In ("FullyQualifiedNetName", "Edition", "PhysicalMemory", "Processors", "VersionString", "Collation", "IsClustered", "ResourceLastUpdateDateTime")).GetEnumerator() | % { $dict.Add($_.Name, $_.Value) }

New-Object -TypeName PSObject -Property $dict

$result = New-Object -TypeName PSObject -Property $dict

$result.FullyQualifiedNetName

$result.Processors

The above code should print out both FullyQualifiedNetName value and Processors count.

Abraham Zinala · Answer 2 · 2021-02-20T05:32:31.313

0

Dot Notation is your friend here. Since you're storing an actual object that is serialiezed, you can reference the properties of the stored value using .propertiesname.

[array]SQLInstance1 = Get-DbaInstanceProperty -SqlInstance SQL | Select-Object ComputerName, Name, Value  | Where-Object Name -In ("FullyQualifiedNetName", "Edition", "PhysicalMemory", "Processors", "VersionString", "Collation", "IsClustered", "ResourceLastUpdateDateTime")

($SQLInstance1.ComputerName.Count + $SQLInstance1.Name.Count + $SQLInstance1.Value.Count); $i++){
[pscustomobject]@{
            ComputerName = $($SQLInstance1.ComputerName[$i])
            Name = $($SQLInstance1.Name[$i])
            Value = $($SQLInstance1.Value[$i])

                }
        }

edited Feb 20 '21 at 05:32

answered Feb 20 '21 at 04:32

Abraham Zinala

4,267
3
9
24

So this stores the data like this: Name Value ComputerName {SQL, SQL, SQL, SQL...} Name {Edition, PhysicalMemory, Processors, VersionString...} Value {Enterprise Edition (64-bit), 1571485, 40, 11.0.7493.4...} What would be the best way to extra the values from that? – SteveH Feb 20 '21 at 05:22
`for` loop, just edited but, havent tested it out due to not having anything to test it with. Just index through the list using `for` loop. – Abraham Zinala Feb 20 '21 at 05:33

Select values from Array based on name

2 Answers2