Can you build a hashtable dynamically from a variable with multiple data rows in powershell?

Question

I am trying to convert the below code to a PowerShell function as I will be using it multiple times in other scripts. I am trying to figure out how I can allow the user to simply pass a SQL Query (a String) to this script that I will turn into a function and have my hash table $row define the keys dynamically based on the column headers and then assign the values when multiple rows are returned by my SQL Query variable $QueryResults. Also, I will have two common variables in each hash table, which are "DateTime" and "ServerName" so those will have to be added as well.

$endpoint = "myPowerBIEndpoint"

$Query = "
myQueryThatReturnsMultipleRecords
        "

$GetDate = (Get-Date).AddMinutes(-1)

while($true)
{
        if((Get-Date) -gt $GetDate)
        {
            #Only run the below every 5 minutes
            $GetDate = (Get-Date).AddMinutes(5)

            #Get list of server to iterate through
            $ServerList = Invoke-DbaQuery -SqlInstance "" -Database "" -Query "
            EXEC [dbo].[myProc] @MonitorType" -SqlParameters @{MonitorType = "AGHealth"}

        }
        
        $ServerResults = @() # Results from each of the servers will be stored here

        foreach ($Server in $ServerList)
        {

            $QueryResults = Invoke-DbaQuery -SqlInstance $ServerList.Cname -Database "" -Query $Query

            $DateTime = Get-Date -DisplayHint Datetime -Format "MM/dd/yyyy HH:mm:ss"

            foreach ($object in $QueryResults)
            {
                Write-Host 'Building Payload for' $object.replica_server_name
                $row = @{
                "DateTime" = $DateTime
                "Replica_Server" = $object.replica_server_name
                "Log_Send_Queue_Size_mb" = $object.total_log_send_queue_size_mb
                "Redo_Queue_Size_mb" = $object.total_redo_queue_size_mb
                "Sync_Health" = $object.synchronization_health
                "Is_Primary_Replica" = $object.is_primary_replica
                "AG_Name" = $object.ag_name
                "ServerName" = $Server.Cname
                }
                $ServerResults += $row
            }
        }

    $payload = @{ "rows" = $ServerResults }

    Write-Host 'Invoking Rest Method'
    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json $payload)

    (ConvertTo-Json $payload)

    sleep 60
}

"allow the user to simply pass a SQL Query" - doesn't Invoke-DbaQuery already solve this problem? — Mathias R. Jessen
– Mathias R. Jessen, Commented May 18, 2021 at 11:57
That is not the question. I need to know how to convert a SQL Query invoked by Invoke-DbaQuery into a hash table. The above code will do exactly that, but as you can see I am hardcoding my $row hash table keys. To clarify further, how do I take the results of $QueryResults (which will change) and build my $row hash table similar to how I have it now, but be done dynamically. — Data Dill
– Data Dill, Commented May 18, 2021 at 12:00

Zilog80 · Accepted Answer · 2021-05-18 13:58:17Z

1

As Invoke-DbaQueryis somehow a wrapper of Invoke-SqlCmd, we can use the schema information stored in the returned PSObject [DataRow] for each row. Each one has a Table member filled by the command describing the resulting schema for the row. In this [DataTable]Table member, we have the [DataColumnCollection]Columns which describes each column of the row as [DataColmun]. We can then use that to feed a hash with the ColumnName member as a key and use the indexed array Item of the row for this ColumnName as value.

The specific corresponding PowerShell code, we add DateTime and ServerName distinctively because they are not coming from the [DataRow] itself :

                $row = @{}
                $object.Table.Columns | %{ $row.Add($_.ColumnName, $object.Item($_.ColumnName) ) }
                $row.Add("DateTime", $DateTime)
                $row.Add("ServerName", $Server.Cname)

The resulting script :

$GetDate = (Get-Date).AddMinutes(-1)

while($true)
{
        if((Get-Date) -gt $GetDate)
        {
            #Only run the below every 5 minutes
            $GetDate = (Get-Date).AddMinutes(5)

            #Get list of server to iterate through
            $ServerList = Invoke-DbaQuery -SqlInstance "" -Database "" -Query "
            EXEC [dbo].[myProc] @MonitorType" -SqlParameters @{MonitorType = "AGHealth"}

        }
        
        $ServerResults = @() # Results from each of the servers will be stored here

        foreach ($Server in $ServerList)
        {

            $QueryResults = Invoke-DbaQuery -SqlInstance $ServerList.Cname -Database "" -Query $Query

            $DateTime = Get-Date -DisplayHint Datetime -Format "MM/dd/yyyy HH:mm:ss"

            foreach ($object in $QueryResults)
            {
                Write-Host 'Building Payload for' $object.replica_server_name
                $row = @{}
                $object.Table.Columns | %{ $row.Add($_.ColumnName, $object.Item($_.ColumnName) ) }
                $row.Add("DateTime", $DateTime)
                $row.Add("ServerName", $Server.Cname)
                $ServerResults += $row
            }
        }

    $payload = @{ "rows" = $ServerResults }

    Write-Host 'Invoking Rest Method'
    Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json $payload)

    (ConvertTo-Json $payload)

    sleep 60
}

edited May 18, 2021 at 13:58

answered May 18, 2021 at 13:12

Zilog80

2,5722 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Data Dill Over a year ago

That works flawlessly. I almost had the answer, but your method is much simpler to understand and uses less foreach loops. Thanks!

Zilog80 Over a year ago

@DylanJanszen If it's ok, i'll edit to add some explanations of what we're doing here.

Data Dill Over a year ago

Absolutely! Go ahead.

Data Dill Over a year ago

This might require a new post, but can I reliably do the same with the results from 'Invoke-Command' of a powershell cmdlet such as 'Get-WinEvent' assuming it returns an array? I can access the properties by piping the psobject to 'Get-Member -MemberType NoteProperty', but not sure how to access the values.

Zilog80 Over a year ago

You can do the same with cmdlet like Get-WinEvent. This one returns [EventLogConfiguration[]], an array of [EventLogConfiguration] objects. So you can do

$Logs=@{}; $(Get-WinEvent -ListLog "Microsoft-Windows-Dhcp*" -ComputerName localhost | %{ $Logs.Add($_.LogName, $_.MaximumSizeinBytes) })

for example, and you get an [Hashtable] in $Logs indexed by LogName with their maximum size as value.

|

Collectives™ on Stack Overflow

Can you build a hashtable dynamically from a variable with multiple data rows in powershell?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related