Best place to start to learn about learning UD and displaying data from SQL Database

Universal Dashboard
1

Hi, I have been using PowerShell for the last three years mainly to automate reporting tasks. Basically I have scheduled PS scripts to update SQL data, extract the data into a CSV file or excel file depending on the report, distributing the reports via email to users and then doing the same if there are any further changes for the day. I came across UD a few days ago and thought of creating a Report Dashboard. This dashboard should display all the available reports (based on sql table), last updated date time (based on another table), allow the user to view the report in the dashboard ( do not prefer grid output) and download the report in certain formats. For example to start with csv, however I could add more like xls, pdf, etc.

My issue is is that I cannot find any documentation or examples that explain how to extract the data from a table using a query and display it. I tried to use a variable (system.data.dataset) with a simple grid following another example without any success.

Please point me in the right direction.

Thanks.

Tesh

2

https://docs.universaldashboard.io/

Should have most of the documentation you need, otherwise scouring this forum will often provide answers to the basics.

For displaying sql data I use Invoke-Sqlcmd2 (due to not needing any additional dependancies).
Heres an example:

New-UDGrid -Title "Test Grid" -DefaultSortColumn "columnname" -PageSize 20 -Headers @("freindlyname1","freindlyname2","freindlyname3") -Properties @("field1","field2","field3") -Endpoint {   
        Invoke-SqlCmd2 -ServerInstance "servername" -Database "dbname" -Query "SELECT * FROM Table" | % {
            [PSCustomObject]@{
                field1 = $_.field1
                field2 = $_.field2
                field3 = $_.field3
            }
        } | Out-UDGridData
    }

You’ll likley need to convert your object to a pscustomobject depending how you pull it through.

1 Like
3

Hello @tesh and a big welcome to the UD forums. I mainly use UD to work with SQL and I got to say the results are amazing with what you can do. So to try and share some of my own personal experiance I have had with UD I developed this blog site and wrote a specific blog on SQL here:-


I hope this helps answer some questions. Peace

3 Likes
4

FYI invoke-sqlcmd2 can output a pscustomobject so no need to pipe it

$SQLsiteParams = @{
    query          = $sitesql
    Serverinstance = $cache:dataSource
    database       = $cache:database
    Credential     = $cache:cred
    As             = "PSObject"
}

$sites = Invoke-Sqlcmd2 @SQLsiteParams
2 Likes
5

Thanks! I cant believe I totally missed this. To be fair I have to pipe in a lot of cases anyway as I often do data transformation and add links etc when displaying in grids and such. But this will come in handy. :slight_smile:

6

I will have to give this a try tonight. Thank you everyone.

7

Thank you guys, I did manage to use my old code with the following:

New-UDGrid -id "Positions" -Title "Hey" -Headers @("Acc","id","true","amnt") -Properties @("acc","id","t","tot") -Endpoint {


  $CallData = get-dataset_test
  $CallData.tables | Select-Object "acc","id","t","tot"  | Out-UDGridData    

} -AutoRefresh -RefreshInterval 20

This is very good as it also updated the data every 20 seconds.

I will really get into this now that I have a good example. Thank you again.

8

Glad to hear you now have a working solution @tesh :+1: I forgot to mention I chucked a whole SQL driven dashboard here:- https://github.com/psDevUK/psUniversalDashboard
This shows various things you can do with SQL and dashboards, and may give you a few new things to think about like synchronising the data via a dashboard. Anyways glad you got a working solution and hopefully that github repo will help you out some more. Peace

1 Like