Cannot get sql query result to create a new table?

[qy2009]

Hi,

I have tried to get the sql query result to create a new table, however when I run it, it outputs empty result? here is the code:

`Import-Module Pode.Web Import-Module dbatools $sqlserver = 'xxx' $database = 'xxx' $siteserver = 'xxx' $sitecode = 'xxx' $tallyinterval = 'xxx'

$Query = " code here " Start-PodeServer { Add-PodeEndpoint -Address localhost -Port 8080 -Protocol Http

Use-PodeWebTemplates -Title 'SCCM' -Theme Dark Add-PodeWebPage -Name 'Component Status' -Icon 'Settings' -ScriptBlock { New-PodeWebCard -Content @( New-PodeWebTable -Name 'Component Status' -ScriptBlock { Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | ForEach-Object { @{ MachineName = $.MachineName ComponentName = $.ComponentName Status = $.Status Infos = $.Infos Warnings = $.Warnings Errors = $.Errors } } } )

}} `

I have also tried just using " Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query" inside the script block. same result.

thanks!

[Badgerati]

Hey @qy2009,

Yes this makes sense, the variables created outside of Start-PodeServer won't be available to the page/table's scriptblock since they're in a different runspace while the server is running (scoping). I'd recommend looking at Pode's shared state functions, as these will let you set/get values that are accessible across the multiple runspaces.

It's on my list to get $using: syntax working with pages/elements, and I thought Add-PodeWebPage had an -ArgumentList but it's missing (this will be added from a different issue).

For the shared state, something like the below should work for you:

Import-Module Pode.Web
Import-Module dbatools

$sqlserver = 'xxx'
$database = 'xxx'
$siteserver = 'xxx'
$sitecode = 'xxx'
$tallyinterval = 'xxx'

$Query = "
code here
"

Start-PodeServer {
    Add-PodeEndpoint -Address localhost -Port 8080 -Protocol Http

    Use-PodeWebTemplates -Title 'SCCM' -Theme Dark

    Set-PodeState -Name 'SqlServer' -Value $sqlserver
    Set-PodeState -Name 'Database' -Value $database
    Set-PodeState -Name 'SiteServer' -Value $siteserver
    Set-PodeState -Name 'SiteCode' -Value $sitecode
    Set-PodeState -Name 'TallyInterval' -Value $tallyinterval
    Set-PodeState -Name 'Query' -Value $Query

    Add-PodeWebPage -Name 'Component Status' -Icon 'Settings' -ScriptBlock {
        New-PodeWebCard -Content @(
            New-PodeWebTable -Name 'Component Status' -ScriptBlock {
                $sqlserver = Get-PodeState -Name 'SqlServer'
                $database = Get-PodeState -Name 'Database'
                $query = Get-PodeState -Name 'Query'

                Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | ForEach-Object {
                    @{
                        MachineName = $_.MachineName
                        ComponentName = $_.ComponentName
                        Status = $_.Status
                        Infos = $_.Infos
                        Warnings = $_.Warnings
                        Errors = $_.Errors
                    }
                }
            }
        )
    }
}

[qy2009]

@Badgerati still got empty result :( Is there a way to check the log and see what was wrong?

I have also tried moving all my variables inside the script block inside New-PodeWebTable. still nothing returned ;(

[Badgerati]

Hey @qy2009,

In case there are any errors, add the following under Add-PodeEndpoint:

New-PodeLoggingMethod -Terminal | Enable-PodeErrorLogging

If that doesn't show anything, with the server running, in the browser press F12 (or whatever opens devtools in your browser), open the console and force refresh the page - are there any table loading errors showing up? In the network tab does the ajax call to load the table result in a 200, or a different status code?

Other than that, try outputting the result of the database query to the powershell terminal instead - see if it's actually selecting any data:

Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | ForEach-Object {
    @{
        MachineName = $_.MachineName
        ComponentName = $_.ComponentName
        Status = $_.Status
        Infos = $_.Infos
        Warnings = $_.Warnings
        Errors = $_.Errors
    }
} | Out-Default

Or even just:

Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | Out-Default

Ooor, if it throws an error it should appear on the terminal with the able logging enabled; but there are some strange errors that seem to "fail successfully" and get missed, so try:

New-PodeWebTable -Name 'Component Status' -ScriptBlock {
    try {
        $sqlserver = Get-PodeState -Name 'SqlServer'
        $database = Get-PodeState -Name 'Database'
        $query = Get-PodeState -Name 'Query'

        Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query | ForEach-Object {
           @{
                MachineName = $_.MachineName
                ComponentName = $_.ComponentName
                Status = $_.Status
                Infos = $_.Infos
                Warnings = $_.Warnings
                Errors = $_.Errors
            }
        }
    }
    catch {
        $_.Exception | Out-Default
    }
}

[qy2009]

@Badgerati

From the console, I got this message: jquery.min.js:2 POST http://localhost:8080/elements/table/table_componentstatus_componentstatus_table_componentstatus_componentstatus 400 (Bad Request)

On the Network tab, I got the same 400 from jquery.min.js

Request URL: http://localhost:8080/elements/table/table_componentstatus_componentstatus_table_componentstatus_componentstatus Request Method: POST Status Code: 400 Bad Request Remote Address: 127.0.0.1:8080 Referrer Policy: strict-origin-when-cross-origin Content-Length: 1197 Content-Type: text/html; charset=utf-8 Date: Wed, 28 Jul 2021 15:44:50 GMT Server: Pode X-Pode-ContextId: 54585610-30d0-c059-587d-91dd5fa03533 Accept: / Accept-Encoding: gzip, deflate, br Accept-Language: en-US,en;q=0.9 Connection: keep-alive Content-Length: 0 Content-Type: application/x-www-form-urlencoded; charset=UTF-8 x Host: localhost:8080 Origin: http://localhost:8080 Referer: http://localhost:8080/pages/Component%20Status sec-ch-ua: "Chromium";v="92", " Not A;Brand";v="99", "Google Chrome";v="92" sec-ch-ua-mobile: ?0 Sec-Fetch-Dest: empty Sec-Fetch-Mode: cors Sec-Fetch-Site: same-origin User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36 X-Requested-With: XMLHttpRequest

From the Terminal, I can get all the results fine like the followings: Name Value

---

ComponentName SMS_CLIENT_CONFIG_MANAGER
Infos 5
MachineName xxx
Errors 0
Warnings 160
Status Critical
ComponentName AI_UPDATE_SERVICE_POINT
Infos 0

Thanks so much for your help :)

[Badgerati]

Hey @qy2009,

Hmm, the 400 definitely suggest an error is being thrown 🤔 Which version of PowerShell, Pode, and Pode.Web are you using?

For the results on the terminal, are they from outputting the results of the query from within the table's scriptblock, or manually? Was there anything on the terminal from enable error logging?

Something like the following should hopefully show something in the terminal when you load the table 🤞 If there's an error in the table's scriptblock this should show it, if it shows the results of the query but is a 400 then something spooky is going on; or, if it shows nothing then the the function could be silently failing to get data 🤔:

Import-Module Pode.Web
Import-Module dbatools

$sqlserver = 'xxx'
$database = 'xxx'
$siteserver = 'xxx'
$sitecode = 'xxx'
$tallyinterval = 'xxx'

$Query = "
code here
"

Start-PodeServer {
    Add-PodeEndpoint -Address localhost -Port 8080 -Protocol Http
    New-PodeLoggingMethod -Terminal | Enable-PodeErrorLogging

    Use-PodeWebTemplates -Title 'SCCM' -Theme Dark

    Set-PodeState -Name 'SqlServer' -Value $sqlserver
    Set-PodeState -Name 'Database' -Value $database
    Set-PodeState -Name 'SiteServer' -Value $siteserver
    Set-PodeState -Name 'SiteCode' -Value $sitecode
    Set-PodeState -Name 'TallyInterval' -Value $tallyinterval
    Set-PodeState -Name 'Query' -Value $Query

    Add-PodeWebPage -Name 'Component Status' -Icon 'Settings' -ScriptBlock {
        New-PodeWebCard -Content @(
            New-PodeWebTable -Name 'Component Status' -ScriptBlock {
                try {
                    $sqlserver = Get-PodeState -Name 'SqlServer'
                    $database = Get-PodeState -Name 'Database'
                    $query = Get-PodeState -Name 'Query'

                    $queryResult = Invoke-DbaQuery -SqlInstance $sqlserver -Database $database -Query $query
                    if (!$?) {
                        throw "Query failed"
                    }

                    $results = $queryResult | ForEach-Object {
                        @{
                            MachineName = $_.MachineName
                            ComponentName = $_.ComponentName
                            Status = $_.Status
                            Infos = $_.Infos
                            Warnings = $_.Warnings
                            Errors = $_.Errors
                        }
                    }

                    $results | Out-Default
                    return $results
                }
                catch {
                    $_ | Out-Default
                }
            }
        )
    }
}

Also, from the example code the URL should be http://localhost:8080/elements/table/table_componentstatus_componentstatus (which I get locally), but you have http://localhost:8080/elements/table/table_componentstatus_componentstatus_table_componentstatus_componentstatus which suggests you might have double embedded table? 🤔 Is the example code the full code, or just a copy/paste issue?

The only other thing I can think of is: how many rows of data are being returned? The result could be too large, though I have tested a table with a lot of data and no pagination so I'd be surprised.

[qy2009]

@Badgerati thanks so much for your update ;) I ran your code, and it showed up the result successfully. Then I removed the troubleshooting code, and it also ran successfully.

I guess the only difference from the my last code is this part: return $results

btw, I am planning to let users to select an option, and then add the value inside the sql query to show the results. and to make the sql query result to refresh automatically every 10min, but it return empty result after the selection?

Here is my code:

Import-Module Pode.Web -DisableNameChecking Import-Module dbatools -DisableNameChecking

Start-PodeServer { Add-PodeEndpoint -Address localhost -Port 8080 -Protocol Http Use-PodeWebTemplates -Title 'SCCM' -Theme Dark Add-PodeWebPage -Name 'Component Status' -Icon 'Settings' -ScriptBlock {

    New-PodeWebSelect -Name 'Single' -Options 'patchserver', 'interalpatch', 'externalpatch', 'serverbaseline', 'workstationbaseline' -SelectedValue 'patchserver'
    $value = $WebEvent.Data['Single']

    New-PodeWebCard -Content @(
        New-PodeWebTable -Name 'Component Status' -ScriptBlock {
        $sqlserver = 'xxx'
        $database = 'xxx'
        $siteserver = 'xxx'
        $sitecode = 'xxx'
        $tallyinterval = 'xxx'
        $query = "xxx where $value"
        $results = Invoke-DbaQuery -SqlInstance "xxx" -Database "xxx" -Query $query | ForEach-Object {
@{ 
    DeviceName = $_.DeviceName 
    LastComplianceMessageDesc = $_.LastComplianceMessageDesc
    LastComplianceMessageTime = $_.LastComplianceMessageTime
    LastEnforcementMessageTime = $_.LastEnforcementMessageTime
    LastMessage = $_.LastMessage
}  
        }
        return $results
        }
    )

}

}

thanks so much!

[Badgerati]

Then I removed the troubleshooting code, and it also ran successfully.

I guess the only difference from the my last code is this part: return $results

Whaaat! Crazy!!

Aaah, yeah that's because a Select element is a form-input element, so needs to be within a New-PodeWebForm, and then the selected value will be available in the Form's scriptblock on submit.

(That being said, you've given me an interesting idea to look at maybe having an -OnChangeScriptBlock so you can do actions on a select outside of a Form, hmm 🤔)

In this instance though, you'd need an initial empty Table, then a Form with Select, and on Submit it runs the SQL and updates the table with the results:

Import-Module Pode.Web -DisableNameChecking
Import-Module dbatools -DisableNameChecking

Start-PodeServer {
    Add-PodeEndpoint -Address localhost -Port 8080 -Protocol Http
    Use-PodeWebTemplates -Title 'SCCM' -Theme Dark

    Add-PodeWebPage -Name 'Component Status' -Icon 'Settings' -ScriptBlock {
        # create an initial empty table with column headers
        # this will refresh every 10mins, based on the forms current selected value
        New-PodeWebCard -Content @(
            New-PodeWebTable -Name 'Component Status' -AutoRefresh -RefreshInterval 600 -ScriptBlock {
                return @{
                    DeviceName = ""
                    LastComplianceMessageDesc = ""
                    LastComplianceMessageTime = ""
                    LastEnforcementMessageTime = ""
                    LastMessage = ""
                }
            }
        )

        # this is the form with select, on submit the value will be retrieved.
        # The SQL then run and the results used to update the table
        New-PodeWebCard -Content @(
            New-PodeWebForm -Name FormData -Content @(
                New-PodeWebSelect -Name 'Single' -Options 'patchserver', 'interalpatch', 'externalpatch', 'serverbaseline', 'workstationbaseline' -SelectedValue 'patchserver'
            ) `
            -ScriptBlock {
                $value = $WebEvent.Data['Single']

                $sqlserver = 'xxx'
                $database = 'xxx'
                $siteserver = 'xxx'
                $sitecode = 'xxx'
                $tallyinterval = 'xxx'
                $query = "xxx where $value"

                $results = (Invoke-DbaQuery -SqlInstance "xxx" -Database "xxx" -Query $query | ForEach-Object {
                    @{
                        DeviceName = $_.DeviceName 
                        LastComplianceMessageDesc = $_.LastComplianceMessageDesc
                        LastComplianceMessageTime = $_.LastComplianceMessageTime
                        LastEnforcementMessageTime = $_.LastEnforcementMessageTime
                        LastMessage = $_.LastMessage
                    }
                })

                $results | Update-PodeWebTable -Name 'Component Status'
            }
        )
    }
}

^ something like that should in theory work for what you're after :)

[qy2009]

I know! Sometimes just a small thing....lol

Thanks so much for your code :) I have tested it that this one is not correct:

$results | Update-PodeWebTable -Name 'Component Status'

After I changed to the following I can see the result

Update-PodeWebTable -Name 'Patching Status' -Data $results

However, the table is not showing the correct order of columns , do you know why? I put the DeviceName first, but the result is not. also, the date is strange. it should be like "2021-07-14 15:43:36.697" , but it is showing like "/Date(1627614141947)/"

Please see the screenshots :

On the chrome console, I see the errors here: DevTools failed to load source map: Could not load content for http://localhost:8080/pode.web/libs/popperjs/popper.min.js.map: Connection error: net::ERR_CONNECTION_REFUSED DevTools failed to load source map: Could not load content for http://localhost:8080/pode.web/libs/bootstrap/bootstrap.bundle.min.js.map: Connection error: net::ERR_CONNECTION_REFUSED DevTools failed to load source map: Could not load content for http://localhost:8080/pode.web/libs/bs-stepper/bs-stepper.min.js.map: Connection error: net::ERR_CONNECTION_REFUSED DevTools failed to load source map: Could not load content for http://localhost:8080/pode.web/libs/moment/moment.min.js.map: Connection error: net::ERR_CONNECTION_REFUSED DevTools failed to load source map: Could not load content for http://localhost:8080/pode.web/min-maps/vs/loader.js.map: Connection error: net::ERR_CONNECTION_REFUSED

[Badgerati]

Hey @qy2009, The column order is a quick one, add [ordered] to the front of the hashtable 😄 ie:

$results = (Invoke-DbaQuery -SqlInstance "xxx" -Database "xxx" -Query $query | ForEach-Object {
    [ordered]@{
        DeviceName = $_.DeviceName 
        LastComplianceMessageDesc = $_.LastComplianceMessageDesc
        LastComplianceMessageTime = $_.LastComplianceMessageTime
        LastEnforcementMessageTime = $_.LastEnforcementMessageTime
        LastMessage = $_.LastMessage
    }
})

The date is likely json/javascript trying to be clever 🤔 - try doing $_.LastComplianceMessageTime.ToString() and see what that does.

The map files are definitely there, it's not too bad that it oddly can't find them, but a strange one. Are you using Pode from the PSGallery or a locally built version from cloning the repo?

[qy2009]

omg, you are genius!!! thanks so much for all the help. now I got both of them working :)

I am using Pode & Pode.Web from the PSGallery.

2.4.0 Pode 0.5.1 Pode.Web

cannot say thank you enough!!!