Solarwinds SWQL Examples

4-byte (32-bit) BGP Neighbor Information with AsDotNotation

SELECT 
  NeighborIP, 
  ProtocolName, 
  ProtocolStatus,

  -- AutonomousSystem is stored in a signed 32 bit integer, and will therefore 
  -- overflow into a negative number when the number exceeds 2,147,483,648.
  -- High Bound of AS before this event is 32768.65535.
  --   NOTE: THIS FIELD IS FOR REFERENCE ONLY AND CAN BE DROPPED IN PRODUCTION.
  AutonomousSystem AS RawAS,

  -- AsPlainNotation represents the correct enumeration for 4-byte AS (as well 
  -- as 2-byte AS by adding the Autonomous system to 4,294,967,296)
  CASE
    WHEN AutonomousSystem < 0 THEN (
      -- NEGATIVE ASN INTEGER OVERFLOW ENCOUNTERED
      -- Add 64-bit CONST Integer representing positive offset to counteract 
      -- negative overflow... 
      4294967296 + AutonomousSystem
    )
    ELSE ( 
      -- ASN WITHIN NORMAL BOUNDS
      -- Return RAW ASN.
      AutonomousSystem 
    )
    END AS AsPlainNotation,

  -- AsDotNotation represents the X.Y Dot Notation specified in RFC4893 for
  -- the 4-byte (or 32-bit) BGP Peer's AS.
  CASE
    WHEN AutonomousSystem <      0 THEN (
      -- NEGATIVE ASN INTEGER OVERFLOW ENCOUNTERED
      -- Add 64-bit CONST Integer representing positive offset to counteract
      -- negative overflow... split components:
      --   +  To get most significant 16-bits divide by 2^16 (65536) and 
      --      truncate with FLOOR.
      --   +  To get least significant 16-bits perform 'most significant bit
      --      calculation' and Left shift 16-bits by multiplying by 2^16
      --      and subtracting from the full 32-bit value.
      TOSTRING(
           FLOOR( (4294967296 + AutonomousSystem) / 65536)
      )
      + '.' + 
      TOSTRING(
        (4294967296 + AutonomousSystem) -
          (FLOOR( (4294967296 + AutonomousSystem) / 65536) * 65536)
      )
    )
    WHEN AutonomousSystem <      0 THEN (
      -- POSITIVE 4-BYTE ASN ENCOUNTERED
      -- Split components:
      --   +  To get most significant 16-bits divide by 2^16 (65536) and 
      --      truncate with FLOOR.
      --   +  To get least significant 16-bits perform 'most significant bit
      --      calculation' and Left shift 16-bits by multiplying by 2^16
      --      and subtracting from the full 32-bit value.
    WHEN AutonomousSystem >  65535 THEN (
       TOSTRING(
            FLOOR( (AutonomousSystem) / 65536)
       )
       + '.' + 
       TOSTRING(
         (AutonomousSystem) -
           (FLOOR( (AutonomousSystem) / 65536) * 65536)
      )
    )
    ELSE (
      -- POSITIVE 2-BYTE ASN ENCOUNTERED
      -- Append 0. to the autonomous system.
      '0.' + 
      TOSTRING(AutonomousSystem)
    )
  END AS AsDotNotation
FROM Orion.Routing.Neighbors
WHERE NodeID = ${NodeID}

Systemwide Cellular Interface Table Overview of BPS, PPS, Non-Unicast %, Drops (Discards+Errors), Avg Packet Size

SELECT 
   intf.Node.NodeName AS Node,
   '/Orion/images/StatusIcons/Small-' + intf.StatusIcon AS [_IconFor_Node], 
   intf.DetailsUrl AS [_LinkFor_Node],
   '' AS [RX],
   '/Orion/images/ForecastingIcons/trenddown.png' AS [_IconFor_RX],
   (TOSTRING(CEILING(intf.Inbps * 10) / 10) + ' bps') AS [bps↓], 
   (TOSTRING(CEILING(intf.InPps * 10) / 10) + ' pps / ' + TOSTRING(CEILING(intf.InPktSize * 10) / 10) + ' bytes') AS [pps/packet↓],  
   CASE WHEN intf.InPps > 0 
     THEN 
      (TOSTRING(intf.InUcastPps / intf.InPps * 100) + '% / ' + 
       TOSTRING(intf.InMcastPps / intf.InPps * 100) + '%')
     ELSE '0% / 0%'
   END AS [Ucast/Mcast↓],
   CASE WHEN intf.InPps > 0 
     THEN 
      (TOSTRING(((intf.InErrorsThisHour) / intf.InPps * 100)) + '% / ' +
       TOSTRING(((intf.InDiscardsThisHour) / intf.InPps * 100)) + '%')
     ELSE '0% / 0%'
   END AS [Error/Discard↓], 
   '' AS [TX],
   '/Orion/images/ForecastingIcons/trend.png' as [_IconFor_TX],
   (TOSTRING(CEILING(intf.Outbps * 10) / 10) + ' bps') AS [bps↑], 
   (TOSTRING(CEILING(intf.OutPps * 10) / 10) + ' pps / ' + TOSTRING(CEILING(intf.OutPktSize)) + ' bytes') as [pps/packet↑], 
   CASE WHEN intf.OutPps > 0 
     THEN 
      (TOSTRING(intf.OutUcastPps / intf.OutPps * 100) + '% / ' + 
       TOSTRING(intf.OutMcastPps / intf.OutPps * 100) + '%')
     ELSE '0% / 0%'
   END AS [Ucast/Mcast↑],
   CASE WHEN intf.OutPps > 0 
     THEN 
      (TOSTRING(((intf.OutErrorsThisHour) / intf.OutPps * 100)) + '% / ' +
       TOSTRING(((intf.OutDiscardsThisHour) / intf.OutPps * 100)) + '%')
     ELSE '0% / 0%'
   END AS [Error/Discard↑]
FROM Orion.NPM.Interfaces intf
WHERE intf.InterfaceName LIKE 'Ce%'
ORDER BY (intf.Inbps + intf.Outbps) DESC
Advertisements