3 Replies Latest reply on Jun 2, 2016 1:17 PM by kurtzb

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

    kurtzb

      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%'

        • Re: SWQL for PDU Total Watts through SAM from undp poll stats.
          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.CustomPollers
          INNER JOIN Orion.NPM.CustomPollerAssignment ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
          INNER JOIN Orion.NPM.CustomPollerStatus ON CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID
          INNER JOIN Orion.Nodes ON Nodes.NodeID = CustomPollerAssignment.NodeID
          WHERE 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 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%'
          
            • Re: SWQL for PDU Total Watts through SAM from undp poll stats.
              kurtzb

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

              Thanks!

                • Re: SWQL for PDU Total Watts through SAM from undp poll stats.
                  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;}