I have a simple dashboard that contains a table and am trying to populate it with data returned by the dbatools Get-DBADatabase query which I am running on the server itself, not from a remote PC.
The query runs fine in the PS IDE but in my dashboard, the table does not populate.
I can see the connection to SQL in SSMS using the ID that I`m logged into the server with (which is a server level sysadmin) but when I check the details of the connection, it appears to be hanging on “select suser_sname()”
Does anyone have any ideas on what I can try to fix this please?
Cheers
Hello @Badgerface I use SQL and UD and wrote a blog on it here:-
I hope this helps answer your issue 
@psDevUK Cheers, will take a look but at first glance, that’s likely beyond my SQL scripting abilities at the moment.
That’s why I prefer using dbatools as it does all the hard SQL scripting work for me 
@Badgerface i use dbatools in my dashboard to pull and show data and backup and restore databases without any issues.
i used mine from a remote machine and it works flawlessly. maybe if you post your command you are using we then can identify what you are missing.
1 Like
@wsl2001
As I said it’s a very simple, work in progress dashboard:
Import-Module UniversalDashboard
Import-Module dbatools
$Dashboard = New-UDDashboard -Content{
New-UDTable -Id "Table 1" -Title "Databases" -Header @("Name", "SizeMB") -Endpoint {
Get-DBADatabase -SqlInstance localhost | Select-Object "Name", "SizeMB" | Out-UDTableData -Property @("Name", "SizeMB")
}
}
Get-UDDashboard | Stop-UDDashboard
Start-UDDashboard -Adminmode -Dashboard $Dashboard -Port 10000
I have also tried using the -SQLCredential switch and authenticated using either a domain or SQL login but get the same result, the table is shown but is never populated.
If I run “whoami” in the PS ISE, it returns Domain\UserID as I would expect and as I said previously, the Get-DBADatabase returns results if run in the ISE.
Cheers
Indeed that’s very simple… one think that can make problems is if the data (Name and SizeMB) is a complex type … check this by using GetType() on the properties … it should be a string and one decimal number … kr
@augustin.ziegler We’re straying very close to the limits of my PS skills here so apologies if I have misunderstood your advice.
I ran this:
$Test = (Get-DBADatabase -SqlInstance localhost | Select-Object Name, SizeMB)
$Test.Gettype().fullname
$Test | Get-Member
Which gave me this result:
System.Object[]
TypeName: Selected.Microsoft.SqlServer.Management.Smo.Database
Name MemberType Definition
---- ---------- ----------
Equals Method bool Equals(System.Object obj)
GetHashCode Method int GetHashCode()
GetType Method type GetType()
ToString Method string ToString()
Name NoteProperty string Name=master
SizeMB NoteProperty double SizeMB=7.375
As you mentioned one item should be a string and Name has that Definition, I removed SizeMB from the query and tried running the dashboard again but still no data was populated.
Cheers
That’s exactly what I was talking about … 
Very beginning question but can you please provide a screenshot … in order to see if the table is empty/not even visible or an error occurs…
@augustin.ziegler OK here is a screenshot of the dashboard.
As you can see, the page has finished loading, the Title and Headers are present but the table remains stubbornly empty. I have not seen any error messages at all.
Cheers
I think the problem is not directly in UD component … maybe the command is not returning any data … try to output the item count …
@Badgerface is the problem you are using “localhost” as the name of the server, and yet if you visit this on a remote machine this too will look at “localhost” as in itself for the name…please place the actual server name in there if you are really using localhost…
1 Like
@augustin.ziegler
Executing this query from the ISE:
Get-DBADatabase -SqlInstance localhost | Select-Object "Name", "SizeMB" | Measure-Object
returns this:
Count : 4
Average :
Sum :
Maximum :
Minimum :
Property :
And just to check:
Get-DBADatabase -SqlInstance localhost | Select-Object Name
returns:
Name
----
master
tempdb
model
msdb
@psDevUK
I`m RDP’d into the server and running the queries from there
@Badgerface here is a working example
Import-Module dbatools -Force
Import-Module UniversalDashboard -Force
$Schedule1 = New-UDEndpointSchedule -Every 1 -Day
$DBs = New-UDEndpoint -Schedule $Schedule1 -Endpoint {
$Cache:DBs = Get-DBADatabase -SqlInstance localhost | Select-Object "Name", "SizeMB"
}
$Dashboard = New-UDDashboard -Content{
New-UDTable -Id "Table 1" -Title "Databases" -Header @("Name","SizeMB") -Endpoint {
$Cache:DBs | Out-UDTableData -Property @("Name","SizeMB")
}
}
Get-UDDashboard | Stop-UDDashboard
Start-UDDashboard -Endpoint @($DBs) -Dashboard $Dashboard -Port 10000
@wsl2001 Thank you for that example.
Unfortunately, it’s not working for me so I`m beginning to think it’s an issue with the configuration of the server I’m running it on (Server 2109 (fully updated) running SQL 2019 Developer edition)
The confusing part is that the query runs fine in the ISE 
The dashboard is being run locally on the server as I don`t have a desktop/laptop client in the domain (homelab) yet and that causes issues with SQL auth which I have not found a way around, so I guess the obvious thing to do is to set one up in the domain and try again.
Thank you to @augustin.ziegler, @psDevUK and @wsl2001 for all your help.
1 Like
Just in case anyone stumbles into the same issue (lets be honest it’ll probably be me again in a few weeks/months )…unfortunately, the example provided above did not work for me, I still got the unpopulated table with headers, but the following code does work (again a very simple and rough example):
Import-Module dbatools
Import-Module UniversalDashboard
$Databases = Get-DBADatabase -SqlInstance ContosoSQLServer| Select-Object "Name", "SizeMB" | Sort-Object Name
$Dashboard = New-UDDashboard -Content{
New-UDTable -Id "Table 1" -Title "Databases" -Header @("Name","SizeMB") -Endpoint {
$Databases | Out-UDTableData -Property @("Name","SizeMB")
}
}
Get-UDDashboard | Stop-UDDashboard
Start-UDDashboard -Dashboard $Dashboard -Port 10000
1 Like
