Powershell script component output in a table

Bump! :)

Hi there, 

Please have a read through the following as I believe it's the best guide for PowerShell script monitors in SolarWinds: https://blog.kmsigma.com/2017/09/08/sam-powershell-scripting-template/

Kind regards,

Marlie Fancourt | SolarWinds Pre-Sales Manager

Prosperon Networks | SolarWinds Partner since 2006

If this helps answer your question please mark my answer as confirmed to help other users, thank you!

mat12 are you looking at creating a report, a dashboard widget on a summary page, a widget on a node details page? Either is certainly doable but the query for each one will likely be different.

Rather basically you would could do something like below, it will give an output of the defined statistic you want to show so in the original request the label would equal 'Definition_Date'

SELECT MS.Component.Application.Node.Caption as [Node Name]
, MS.Label as [Statistic Name]
, MS.StringData as [Message]

FROM Orion.APM.MultipleStatisticData MS
--Change the below to match the stastic label of the statistic you would like to display
where MS.Label = 'Definition_Date'

Then to do something like this for a node details page you could do the following code

SELECT MS.Component.Application.Node.Caption as [Node Name]
, MS.Label as [Statistic Name]
, MS.StringData as [Message]

FROM Orion.APM.MultipleStatisticData MS
--Change the below to match the stastic label of the statistic you would like to display
where MS.Label = 'Definition_Date' and MS.Component.Application.Node.NodeID = ${NodeID}

Thanks christopher.t.jones123,

I was actually looking to show this powershell output information in SAM:

This shows database size and I know this can also be achieved in AppInsight for Exchange, however AppInsight for exchange eats up 50+ licenses and we already have our custom SQL monitoriing only critical services and status and we only need to get the above sample information from the DB.

mat12, ah I see. 

Well it is doable using a PS script monitor, but the overhead would be a nightmare in large environments. 

I'm thinking that you could do each DB as a PS component and then the respective information as one of the statistic outputs, then you would rinse and repeat for every DB on that SQL server that you want to get that info for. 

The high level of how it would using your example above would be to list out the data you want as a stat and message, understanding that you con do up to 10 outputs per component.

If you paste that PowerShell syntax I should be able to build you an example.

Hi christopher.t.jones123,

As always, thanks for your time again on helping out on this one.
Here is the sample powershell syntax that shows the information:

$Server=”ServerName\InstanceName”           
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
$SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server
$SMOserver.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | Format-Table -Autosize

Thanks in advance!

mat12, here's something really really rough. I haven't tested it but it should work\

I used the output above as an example. The below can be created as a PS component and should output the data for the "AdventureWorks" DB. It could also be worked into that the DB name and Server\Instance could be passed as script arguments. 

$Server = ”ServerName\InstanceName”
$dbname = "AdventureWorks"
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
$SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server
$database = $SMOserver.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | Where-Object {$_.'Name' -eq $dbname}

Write-Host "Statistic.Size: $database.Size"
Write-Host "Message.Size: Database size is $$database.size"
Write-Host "Statistic.DataSpaUsage: $database.DataSpaUsage"
Write-Host "Message.DataSpaUsage: Database size is $$database.DataSpaUsage"
Write-Host "Statistic.IndexSpaceUsage: $database.IndexSpaceUsage"
Write-Host "Message.IndexSpaceUsage: Database size is $$database.IndexSpaceUsage"
Write-Host "Statistic.SpaceAvailable: $database.SpaceAvailable"
Write-Host "Message.SpaceAvailable: Database size is $$database.SpaceAvailable"

Hi christopher.t.jones123

Thanks for this awesome idea!
Here is the error I'm getting initially:

Then when I updated the code and the statistics to "0" I was able to see the information on the webconsole using the updated script below. The reason I put "0" temporarily is it seems the data is detected as string instead of integer that is why Statistics value is equal to NaN previously. But still, I was able to get the information we want via Message for now. I'm also trying to convert this value to integer but with no luck haha!

$Server=”SQLServerName\Instance”      
$dbname = "SolarWindsOrion"
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
$SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server
$database = $SMOserver.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | Where-Object {$_.'Name' -eq $dbname}

Write-Host "Statistic.Size: 0"
Write-Host "Message.Size: Database size is $database.Size"
Write-Host "Statistic.DataSpaceUsage: 0"
Write-Host "Message.DataSpaceUsage: Database size is $database.DataSpaceUsage"
Write-Host "Statistic.IndexSpaceUsage: 0"
Write-Host "Message.IndexSpaceUsage: Database size is $database.IndexSpaceUsage"
Write-Host "Statistic.SpaceAvailable: 0"
Write-Host "Message.SpaceAvailable: Database size is $database.SpaceAvailable"

mat12, thanks for the feedback. I had to get around this for some M365 templates, you can take the output and convert it to an integer and then use it as the stat, something like below is a good example of how to do it.

$Server = ”ServerName\InstanceName”
$dbname = "AdventureWorks"
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null
$SMOserver = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) -argumentlist $Server
$database = $SMOserver.Databases | select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | Where-Object {$_.'Name' -eq $dbname}

$Size = [int]$database.Size
$DataSpaceUsage = [int]$database.DataSpaceUsage
$IndexSpaceUsage = [int]$database.IndexSpaceUsage
$SpaceAvailable = [int]$database.SpaceAvailable

Write-Host "Statistic.Size: $Size"
Write-Host "Message.Size: Database size is $Size"
Write-Host "Statistic.DataSpaceUsage: $DataSpaceUsage"
Write-Host "Message.DataSpaceUsage: Database size is $DataSpaceUsage"
Write-Host "Statistic.IndexSpaceUsage: $IndexSpaceUsage"
Write-Host "Message.IndexSpaceUsage: Database size is $IndexSpaceUsage"
Write-Host "Statistic.SpaceAvailable: $SpaceAvailable"
Write-Host "Message.SpaceAvailable: Database size is $SpaceAvailable"

Hi christopher.t.jones123,

I was able to follow your procedure and convert it to the int using your provided script.  .

I tried also doing it for the SQL HA availability but it seems it is using a different approach.
The reason i'm trying to get the information is to start with db size until I can get the HA status to show in Solarwinds without the AppInsight as this is also not available in AppInsight for SQL (image for sample only):

Thanks again!