How to change this SWQL (95% percentile) Report to only show 2 Interfaces, and not everything.

Hi Guys, I still have a lot to learn using SWQL and have a request for a Report, they're asking for a 95% percentile Report which is fine as there are a number of OOTB templates available.

However, the requester only wants these metrics (percentile) on 2 critical interfaces, not all interfaces. How do I introduce the WHERE Interface LIKE..... line into the below SWQL please for just 2 or a small handful of interfaces.

(95th Percentile Traffic - Last Month - OOTB Report)


SELECT
I.InterfaceID,
N.NodeID,
N.Caption AS NodeName,
N.DetailsUrl AS NDetailsUrl,
N.VendorIcon AS Vendor,
I.Caption AS Interface_Caption,
I.InterfaceIcon AS Interface_Icon,
I.DetailsUrl AS IDetailsUrl,
AverageInboundBps95th,
AverageOutboundBps95th,
AverageTotalBps95th
FROM
(
SELECT
[DateTime],
InterfaceID,
AverageInboundBps95th,
AverageOutboundBps95th,
AverageInboundBps99th,
AverageTotalBps95th
FROM Orion.NPM.InterfacePercentiles
WHERE [DATETIME] BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
) AS IP
JOIN Orion.NPM.Interfaces I ON I.InterfaceID = IP.InterfaceID
JOIN Orion.Nodes N ON I.NodeID = N.NodeID

Thank you.

  • hello,

    you can input in where AND I.CAPTION LIKE '%XXXX%', YOU CAN USE SWIS SOURCE TO TESTE YOUR QUERY WRITE IN URL hyourserver/.../Swis.aspx

  • Thank you   , added the code below. Last line. 

    SELECT
    I.InterfaceID,
    N.NodeID,
    N.Caption AS NodeName,
    N.DetailsUrl AS NDetailsUrl,
    N.VendorIcon AS Vendor,
    I.Caption AS Interface_Caption,
    I.InterfaceIcon AS Interface_Icon,
    I.DetailsUrl AS IDetailsUrl,
    AverageInboundBps95th,
    AverageOutboundBps95th,
    AverageTotalBps95th
    FROM
    (
    SELECT
    [DateTime],
    InterfaceID,
    AverageInboundBps95th,
    AverageOutboundBps95th,
    AverageInboundBps99th,
    AverageTotalBps95th
    FROM Orion.NPM.InterfacePercentiles
    WHERE [DATETIME] BETWEEN AddMonth(-1,DateTrunc('month', GETUTCDATE())) AND DateTrunc('month', GETUTCDATE())
    ) AS IP 
    JOIN Orion.NPM.Interfaces I ON I.InterfaceID = IP.InterfaceID
    JOIN Orion.Nodes N ON I.NodeID = N.NodeID
    Where N.Caption LIKE 'YOURNODECAPTION' AND I.Caption  LIKE 'YOURINTERFACECAPTION'

  • Going to try this now, very much appreciated.