SWQL Queries - Interface Stats

Version 2

    I made these queries so I can extract CSV data for use inside tools such as Splunk.  Handy for correlating event data.

     

    # 1 day stats from 8 days ago and back 68 days (30 days @ 1 day intervals)

    SELECT i.FullName,

      t.DateTime, t.InAveragebps, t.OutAveragebps, t.Averagebps, t.OutPercentUtil, t.InPercentUtil,

      t.PercentUtil, t.OutMinbps, t.OutMaxbps, t.InMinbps, t.InMaxbps, t.InAvgUnicastPkts, t.OutAvgUnicastPkts,

      t.InAvgMultiCastPkts, t.OutAvgMultiCastPkts, t.InTotalPkts, t.OutTotalPkts,

      e.InDiscards, e.InErrors, e.OutDiscards, e.OutErrors

    FROM Orion.NPM.Interfaces as i

    INNER JOIN Orion.NPM.InterfaceTraffic as t ON i.InterfaceID=t.InterfaceID

    INNER JOIN Orion.NPM.InterfaceErrors as e ON i.InterfaceID=e.InterfaceID

    AND i.FullName LIKE 'device-name%' AND t.DateTime=e.DateTime AND t.DateTime > GetUtcDate()-61 AND t.DateTime < GetUtcDate()-31

     

     

    # 1 hour stats from 8 days ago and back 68 days (22 days @ 1 hr intervals)

    SELECT i.FullName,

      t.DateTime, t.InAveragebps, t.OutAveragebps, t.Averagebps, t.OutPercentUtil, t.InPercentUtil,

      t.PercentUtil, t.OutMinbps, t.OutMaxbps, t.InMinbps, t.InMaxbps, t.InAvgUnicastPkts, t.OutAvgUnicastPkts,

      t.InAvgMultiCastPkts, t.OutAvgMultiCastPkts, t.InTotalPkts, t.OutTotalPkts,

      e.InDiscards, e.InErrors, e.OutDiscards, e.OutErrors

    FROM Orion.NPM.Interfaces as i

    INNER JOIN Orion.NPM.InterfaceTraffic as t ON i.InterfaceID=t.InterfaceID

    INNER JOIN Orion.NPM.InterfaceErrors as e ON i.InterfaceID=e.InterfaceID

    AND i.FullName LIKE 'device-name%' AND t.DateTime=e.DateTime AND t.DateTime > GetUtcDate()-30 AND t.DateTime < GetUtcDate()-8

     

     

    # last 7 days stats (5 min intervals)

    SELECT i.FullName,

      t.DateTime, t.InAveragebps, t.OutAveragebps, t.Averagebps, t.OutPercentUtil, t.InPercentUtil,

      t.PercentUtil, t.OutMinbps, t.OutMaxbps, t.InMinbps, t.InMaxbps, t.InAvgUnicastPkts, t.OutAvgUnicastPkts,

      t.InAvgMultiCastPkts, t.OutAvgMultiCastPkts, t.InTotalPkts, t.OutTotalPkts,

      e.InDiscards, e.InErrors, e.OutDiscards, e.OutErrors

    FROM Orion.NPM.Interfaces as i

    INNER JOIN Orion.NPM.InterfaceTraffic as t ON i.InterfaceID=t.InterfaceID

    INNER JOIN Orion.NPM.InterfaceErrors as e ON i.InterfaceID=e.InterfaceID

    AND i.FullName LIKE 'device-name%' AND t.DateTime=e.DateTime AND t.DateTime > GetUtcDate()-7