Discuss this Article

This post is using PowerShell Universal v1.3 and Universal Dashboard v3.0.2. Universal Dashboard is now part of PowerShell Universal.

By default, Universal Dashboard’s New-UDTable will render all the data that is passed to the -Data parameter. The paging, sorting and filtering work within the user’s browser. Although this is a good user experience when there is a small set of data, it can take a long time to download and render when working with a large set of data. In this post, we’ll look at how to use the server-side processing capabilities of New-UDTable to page, sort and filter data within a SQL server rather than within a client’s browser.

Interacting with SQL

In my example, I’ll be using a Microsoft SQL database to store and query data. I’ll be taking advantage of the dbatools module to create the database, import and query the data.

First, I’ll install the dbatools module and create a database.

Install-Module dbatools -Scope CurrentUser -Force
New-DbaDatabase -SqlInstance localhost\MSSQLSERVER -Name 'podcasts' 

ComputerName       : LAPTOP-496LAUK8
InstanceName       : MSSQLSERVER
SqlInstance        : LAPTOP-496LAUK8
Name               : podcasts
Status             : Normal
IsAccessible       : True
RecoveryModel      : Full
LogReuseWaitStatus : Nothing
SizeMB             : 16
Compatibility      : Version150
Collation          : SQL_Latin1_General_CP1_CI_AS
Owner              : LAPTOP-496LAUK8\adamr
LastFullBackup     : 01/01/0001 00:00:00
LastDiffBackup     : 01/01/0001 00:00:00
LastLogBackup      : 01/01/0001 00:00:00

Now that we have a database, let’s insert some data into the table. Let’s use the Import-DbaCsv tool to load a CSV into the database. I have a CSV of podcasts shows with the name of their hosts.

name,host
99% Invisible,Roman Mars
BiggerPockets Money Podcast,BiggerPockets
Bootstrapped Web, Bootstrapped Web
Criminal,Phoebe Judge
Daily Sales Tips,Scott Ingram
Mixergy,Andrew Warner
The Jordan Harbinger Show,Jordan Harbinger
Marketing School,Neil Patel and Eric Siu
Startup Therapy,Startups.com
Sawbones,Justin McElroy and Dr. Sydnee McElroy
Stuff You Should Know,iHeartRadio
Science Vs,Gimlet
The Michelle Obama Podcast,Higher Ground
Crime Junkie,audiochuck

Using this CSV we can import that data into our database. We’ll have Import-DbaCsv create the table for us.

Import-DbaCsv -Csv .\podcasts.csv -SqlInstance localhost\MSSQLSERVER -Database podcasts -Table shows -Truncate -AutoCreateTable 

ComputerName  : LAPTOP-496LAUK8
InstanceName  : MSSQLSERVER
SqlInstance   : LAPTOP-496LAUK8
Database      : podcasts
Table         : shows
Schema        : dbo
RowsCopied    : 14
Elapsed       : 140.26 ms
RowsPerSecond : 101
Path          : C:\Users\adamr\Desktop\podcasts.csv

We can then validate that our data was imported by using the Invoke-DbaQuery command.

Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database podcasts -Query "SELECT * FROM SHOWS"

name                        host
----                        ----
99% Invisible               Roman Mars
BiggerPockets Money Podcast BiggerPockets
Bootstrapped Web             Bootstrapped Web
Criminal                    Phoebe Judge
Daily Sales Tips            Scott Ingram
Mixergy                     Andrew Warner
The Jordan Harbinger Show   Jordan Harbinger
Marketing School            Neil Patel and Eric Siu
Startup Therapy             Startups.com
Sawbones                    Justin McElroy and Dr. Sydnee McElroy
Stuff You Should Know       iHeartRadio
Science Vs                  Gimlet
The Michelle Obama Podcast  Higher Ground
Crime Junkie                audiochuck

Now that we have our database inflated with data, let’s look at how to integrate that will Universal Dashboard’s New-UDTable.

Adding a Table to a Dashboard

Within the administrator console for PowerShell Universal, let’s create a new dashboard. You can click Dashboard \ Dashboards and then click the add button. I’ll be creating a podcasts dashboard for this example.

Once the dashboard has been added, you’ll have a default dashboard up and running. Next, let’s add a table to the dashboard. We’ll use the New-UDTable cmdlet to define a table based on our shows table.

$Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database podcasts -Query "SELECT * FROM SHOWS" | ForEach-Object {
    @{ 
        name = $_.name 
        host = $_.host
    }
}
New-UDTable -Data $Data

This basic table will show all the data from the the shows table in our database. If we had a large table, the query could take a long time, the download of the data to the client could take a long time and then the rendering in JavaScript could take a long time. Due to this limitation, the next section will discuss how to use server-side processing to implement this table.

Server-side processing

Server-side processing relies on using the data storage method to enable better control of the data that is retrieved and sent to the client. Rather than sending all the data, we will only send the data that the client is currently viewing.

Rather than querying the data before creating the New-UDTable, we will take advantage of the LoadData parameter that will be called to dynamically load data after the table has rendered. The $Body variable contains a JSON string with information about the current state of the table.

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

    <# $Body will contain
        filters: []
        orderBy: undefined
        orderDirection: ""
        page: 0
        pageSize: 5
        properties: (2) ["name", "host"]
        search: ""
        totalCount: 0
    #>

    $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query 'SELECT * FROM shows' | ForEach-Object {
        @{ 
            name = $_.name 
            host = $_.host
        }
    } 
    $Data | Out-UDTableData -Page $TableData.page -TotalCount $Data.Count -Properties $TableData.properties
} -Columns @(
    New-UDTableColumn -Property 'name'
    New-UDTableColumn -Property 'host'
)

One thing that you’ll notice is that the table loads and displays a spinner as the data is retrieved from the server.

Sorting

Now that we have data in our table. Let’s setup sorting. Sorting is required for proper paging and will allow users to click the header of each column. We will need to enable sorting on the New-UDTable and each column that we wish to allow the user to sort. I’m also going to select a default column to sort if one is not selected. We will also want to retrieve the sort direction and pass that to SQL.

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

    $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT * FROM shows ORDER BY $OrderBy $OrderDirection" | ForEach-Object {
        @{ 
            name = $_.name 
            host = $_.host
        }
    } 
    $Data | Out-UDTableData -Page $TableData.page -TotalCount $Data.Count -Properties $TableData.properties
} -Columns @(
    New-UDTableColumn -Property 'name' -Sort
    New-UDTableColumn -Property 'host' -Sort
) -Sort

Paging

Now that we have sorting configured, we can setup proper paging. Most databases require sorting to be enabled to page correctly.

We will retrieve the page from the $TableData and provide that to our SQL query along with the page size. We will also need to perform a separate query to calculate the total number of records since we will only be returning the page size of records and cannot rely on the array’s count any longer.

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

    $PageSize = $TableData.PageSize 
    # Calculate the number of rows to skip
    $Offset = $TableData.Page * $PageSize

    $Count = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT COUNT(*) as count FROM shows"

    $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT * FROM shows ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
        @{ 
            name = $_.name 
            host = $_.host
        }
    } 
    $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
} -Columns @(
    New-UDTableColumn -Property 'name' -Sort
    New-UDTableColumn -Property 'host' -Sort
) -Sort

Now that we have paging configured in our SQL query, it will work from our table as well.

Filter

Finally, we will look at how to enable filtering. Filtering allows you to enter text into a column’s header to filter the rows based on the text entered. Filtering is a little more complicated because we can have a filter defined for each column. The filters property of the $TableData hashtable will include an array of filters to process. Each filter will have a property name and value.

We will construct a WHERE clause based on the filters provided and pass it to both our counting query as well as our results query.

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.id + " 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 localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT COUNT(*) as count FROM shows $Where"

    $Data = Invoke-DbaQuery -SqlInstance localhost\MSSQLSERVER -Database 'podcasts' -Query "SELECT * FROM shows $Where ORDER BY $orderBy $orderdirection OFFSET $Offset ROWS FETCH NEXT $PageSize ROWS ONLY" | ForEach-Object {
        @{ 
            name = $_.name 
            host = $_.host
        }
    } 
    $Data | Out-UDTableData -Page $TableData.page -TotalCount $Count.Count -Properties $TableData.properties
} -Columns @(
    New-UDTableColumn -Property 'name' -Sort -Filter
    New-UDTableColumn -Property 'host' -Sort -Filter
) -Sort -Filter -Paging

Now that we have filtering in place, you’ll be able to type into the filter boxes for columns to search through rows.

Conclusion

In this post, we looked at how to create a server-side table in PowerShell Universal Dashboard v3. Although we used SQL in this example, you could use the same technique for querying any data source that supports sorting, paging and filtering.