Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SWQL for PDU Total Watts through SAM from undp poll stats.

Hi all,

I am trying very hard to learn SWQL for the SDK API. I have a SQL query that works and can not figure out how to get the same to work with SWQL. I join and get Nodes already exists. So I am not getting something very basic about SWQL I think. I am now looking to just run strait SQL and give up. So if there is anyone that can take a look at this and tell me the proper way to run it in SWQL?

SELECT SUM(RawStatus) as TotalWatts FROM CustomPollers

INNER JOIN CustomPollerAssignment ON CustomPollerAssignment.PollerID = CustomPollers.PollerID

INNER JOIN CustomPollerStatus ON CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID

INNER JOIN NodesData ON NodesData.NodeID = CustomPollerAssignment.NodeID

WHERE UniqueName LIKE '%watts%' AND GroupName LIKE '%APC%' AND SysName LIKE '%XXX%' AND SysName NOT LIKE '%XXX%'

Find more posts tagged with

Accepted answers

tdanner

Here is a straightforward conversion of your SQL query to SWQL, which really just means fixing up some entity and property names that are different than the corresponding database tables:

SELECT SUM(RawStatus) as TotalWatts FROM Orion.NPM.CustomPollersINNER JOIN Orion.NPM.CustomPollerAssignment ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerIDINNER JOIN Orion.NPM.CustomPollerStatus ON CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentIDINNER JOIN Orion.Nodes ON Nodes.NodeID = CustomPollerAssignment.NodeIDWHERE UniqueName LIKE '%watts%' AND GroupName LIKE '%APC%' AND SysName LIKE '%XXX%' AND SysName NOT LIKE '%XXX%'

And here is another version of that query that replaces all of the joins with navigation properties, a feature of SWQL that is not available in SQL:

SELECT SUM(N.CustomPollerAssignmentOnNode.CustomPollerStatus.RawStatus) as TotalWattsFROM Orion.Nodes NWHERE N.SysName LIKE '%XXX%' AND N.SysName NOT LIKE '%XXX%'  AND N.CustomPollerAssignmentOnNode.CustomPoller.UniqueName LIKE '%watts%'  AND N.CustomPollerAssignmentOnNode.CustomPoller.GroupName LIKE '%APC%'

All comments

tdanner

Here is a straightforward conversion of your SQL query to SWQL, which really just means fixing up some entity and property names that are different than the corresponding database tables:

SELECT SUM(RawStatus) as TotalWatts FROM Orion.NPM.CustomPollersINNER JOIN Orion.NPM.CustomPollerAssignment ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerIDINNER JOIN Orion.NPM.CustomPollerStatus ON CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentIDINNER JOIN Orion.Nodes ON Nodes.NodeID = CustomPollerAssignment.NodeIDWHERE UniqueName LIKE '%watts%' AND GroupName LIKE '%APC%' AND SysName LIKE '%XXX%' AND SysName NOT LIKE '%XXX%'

And here is another version of that query that replaces all of the joins with navigation properties, a feature of SWQL that is not available in SQL:

SELECT SUM(N.CustomPollerAssignmentOnNode.CustomPollerStatus.RawStatus) as TotalWattsFROM Orion.Nodes NWHERE N.SysName LIKE '%XXX%' AND N.SysName NOT LIKE '%XXX%'  AND N.CustomPollerAssignmentOnNode.CustomPoller.UniqueName LIKE '%watts%'  AND N.CustomPollerAssignmentOnNode.CustomPoller.GroupName LIKE '%APC%'

kurtzb

That is it! I really appreciate it. Its helping me learn SWQL. When I finish the SAM template i'll post.

Thanks!

kurtzb

I used this with a SAM powershell component monitor to get Total Watts. Im putting that in a widget next to a Datacenter Network Atlas map that is monitoring doors (open, closed). Will be building onto that with temps per cage.

Powershell script; replace the xxx info with host names convention you want to match; and with you do not want to match. In our case one of the PDU's is hosed and needs a power-reset and is giving a crazy output for watts.

Add-PSSnapin "SwisSnapin"

$server = "{solarwinds api server}"

$swis = Connect-Swis -trusted -host $server

$query = "SELECT SUM(N.CustomPollerAssignmentOnNode.CustomPollerStatus.RawStatus) as TotalWatts

FROM Orion.Nodes N

WHERE N.Sysname LIKE `'xxx%`' AND N.SysName NOT LIKE `'xxx%`'

AND N.CustomPollerAssignmentOnNode.CustomPoller.UniqueName LIKE `'%watts%`'

AND N.CustomPollerAssignmentOnNode.CustomPoller.GroupName LIKE `'%apc%`'"

$TotalWatts = Get-SwisData $swis $query

if ($TotalWatts) {Write-Host "Message.TotalWatts: TotalWatts"; Write-host "Statistic.TotalWatts: $TotalWatts"; Exit 0;}

Else

{Write-Host "Message.TotalWatts: Command failed to run!"; Write-Host "Statistic.TotalWatts: $TotalWatts"; Exit 3;}