SWQL Queries - Interface Stats

johnsond

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

hebron1212

Should this work in the custom query widget? All I get is the column headings.

Also, how could I write the query (or any query) so that it uses only the current interface (I.e. the custom query widget is on the Interface Details for Te0/0/4 page, so the query would only bring up data for that one interface, without having to specify it explicitly in the query).

Thanks.

Steve