3 Replies Latest reply on Jul 27, 2017 2:27 PM by tdanner

    Custom query help

    miked_nme

      Greetings Everyone,

       

       

      Need help constructing a complex SWQL query to build a historical BW utilization table going back 180 days IN/OUT and broken up by month. Goal is to have it be 1 line, multiple columns per node/interface within a custom table as part of a larger situational awareness dashboard for our director. We would also like the values to conditionally format based on what percentage is returned for that given month. An example being Interface X off of Node Y was at 90% utilization per the 95th percentile IN for the month of March. We want that 90% value to be Red. If it dropped to 36% in May we want that value to be Green. The conditional format color just going off the already out of the box Solarwinds default thresholds.

       

       

      I've already tried the syntax suggested in this thread: https://thwack.solarwinds.com/thread/113097 But I just get "Query is not valid" when attampting to use

       

       

      DateTrunc(‘day’, T.ObservationTimestamp) AS [Day],

      AVG(T.Traffic.InAveragebps) AS InAvgBps, AVG(T.Traffic.OutAveragebps) AS OutAvgBps,

      MIN(T.Traffic.InMinbps) AS InMinBps, AVG(T.Traffic.OutMinbps) AS OutMinBps

      MAX(T.Traffic.InMaxbps) AS InMaxBps, AVG(T.Traffic.OutMinbps) AS OutMaxBps

       

       

      Or the subquery further down the thread.

       

       

      We are on the latest version of NPM here.

       

       

      This is what I have so far trying different things to also add in some fields to another custom table we're working on for the same dashboard. We have tabs capturing assets from different regions which is why we are using the CarrierName to filter out the other nodes/interfaces.

       

       

      SELECT N.Caption AS [Node Name],

      C.CarrierName AS PACE,

      I.Name,

      T.ObservationTimestamp,

      T.InTotalPkts AS [Total Pkts IN],

      T.OutTotalPkts AS [Total Pkts OUT],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=N%3a'+ToString(N.NodeID) AS [_LinkFor_Node Name],

      '/Orion/NetPerfMon/NodeDetails.aspx?NetObject=I:'+ToString(I.InterfaceID)+'&view=InterfaceDetails' AS [_LinkFor_Name],

      I.DetailsUrl,

      N.DetailsUrl

        FROM Orion.Nodes AS N

        INNER JOIN Orion.NPM.Interfaces AS I on N.NodeID=I.NodeID

        INNER JOIN Orion.NPM.InterfaceTraffic AS T on N.NodeID=T.NodeID

        INNER JOIN Orion.NPM.InterfacesCustomProperties AS C on I.InterfaceID=C.InterfaceID

        WHERE C.CarrierName Like 'Primary_b%'

        OR C.CarrierName Like 'Alternate_b%'

        ORDER BY N.Caption Asc, C.CarrierName Desc

        • Re: Custom query help
          tdanner

          "T.Traffic.InAveragebps" - in your query, you have used an explicit JOIN to bring in Orion.NPM.InterfaceTraffic with the alias "T". So you don't need to say "T.Traffic.InAveragebps". You can just say "T.InAveragebps". Or "I.Traffic.InAveragebps".

           

          Also - you are joining in InterfaceTraffic only on the NodeID. You should join on the InterfaceID instead.

           

          I recommend you work out this query in SWQL Studio before transferring it to the website. You will get autocomplete for the property names and better error messages than "Query is not valid". You can download it from https://github.com/solarwinds/OrionSDK/releases/latest

            • Re: Custom query help
              miked_nme

              Thank you for the Tip, I took out the .Traffic portion of the query. Also realized shortly after trying the modified join that trying to join Orion.Nodes to NPM.InterfaceTraffic wasn't going to work on InterfaceID. There's no InterfaceID column in Orion.Nodes, at least not listed here: SolarWinds Information Service v3.0 Schema Documentation

               

              So I changed that Join statement to be between NPM.Interfaces and NPM.InterfaceTraffic which worked. Was able to obtain SWQL Studio by way of a ticket as we cannot reach github from here due to certificate issues with the site. Just working through some local hbss issues now to get it working fully and then will test queries in there.

               

              Any suggestions on a specific syntax that will accomplish what we are trying to do as described above?