Dashboard with MS SQL Data

PowerShell Universal
1

After watching a few videos and also reading the documentation, I am trying to set up a SQL query example from SQL - PowerShell Universal (ironmansoftware.com)

Unfortunately that doesn’t work at the moment

I did the SQL query checker and the result is OK within normal ISE
but the dashboard shows me “property ‘0’ of zero cannot be read”

This is my code. I just removed the server and database (to see the code here).

New-UDDashboard -Title “test” -Content {

New-UDTable -Title ‘Shows’ -LoadData {
$TableData = ConvertFrom-Json $Body

            $OrderBy = $TableData.orderBy.field
            if ($OrderBy -eq $null)
            {
                $OrderBy = "name"
            }
        
            $OrderDirection = $TableData.OrderDirection
            if ($OrderDirection -eq $null)
            {
                $OrderDirection = 'asc'
            }
        
            $Where = ""
            if ($TableData.Filters) 
            {
                $Where = "WHERE "
        
                foreach($filter in $TableData.Filters)
                {
                    $Where += $filter.column.field + " LIKE '%" + $filter.value + "%' AND "
                }
        
                $Where += " 1 = 1"
            }
        
            $PageSize = $TableData.PageSize 
            # Calculate the number of rows to skip
            $Offset = $TableData.Page * $PageSize
            $Count = Invoke-DbaQuery -SqlInstance %myServer% -Database '%myDB%' -Query "SELECT COUNT(*) as count dbo.vs_Machine shows $Where"
        
            $Data = Invoke-DbaQuery -SqlInstance %myServer% -Database '%myDB%' -Query "SELECT Machine_ID, MachineMAC1, OsFullName, RequestorName FROM dbo.vs_Machine $Where ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
                @{ 
                    Machine_ID = $_.Machine_ID 
                    MachineMAC1 = $_.MachineMAC1 
                    OsFullName = $_.OsFullName 
                    RequestorName  = $_.RequestorName  
                }
            } 
            $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
        } -Columns @(
                New-UDTableColumn -Property Machine_ID 
                New-UDTableColumn -Property MachineMAC1
                New-UDTableColumn -Property OsFullName 
                New-UDTableColumn -Property RequestorName 
        ) -Sort -Filter

the Dashboard Log show me this
[01-31-21 08:43:48 PM] Dashboard configuration complete.

[01-31-21 08:43:51 PM] An error occurred: Das Argument kann nicht an den Parameter “InputObject” gebunden werden, da es NULL ist.

Endpoint: 30d18bfd-402d-4955-8795-8ec05ad7af6b

Session: cbb98501-d6ed-4b9c-9e78-413ba11d86ee

File: C:\ProgramData\UniversalAutomation\Repository\test.ps1

Endpoint Start Line: 1

Endpoint End Line: 52

Stack Trace: bei New-UDTable, C:\Program Files (x86)\Universal\UniversalDashboard\Frameworks\v3\UniversalDashboard.MaterialUI.psm1: Zeile 4100

bei , : Zeile 48

any idea ?

2

Looks like you are missing FROM in your Count query.