This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL Complex query

very new to the swis api and SWQL so would greatly appreciate any help.

i am looking for a query that will give me traffic information interfaces this being the max/min in/out bps  and the avg in/out bps for a period of the last 30 days.

any thing i have tried so far will give me multiple entries for every few hours is there anyway to just take the highest and lowest recorded for the given day to be used for the min and max.

many thanks sorry if this a vague

  • That sounds very doable, but let's tighten up the requirements a bit before we dive into writing a query. What all columns do you want in the output? Do you want one row per interface where each row summarizes the last month? Or 30 rows per interface where each row summarizes a day? Do you want all monitored interfaces included or only a subset?

  • Hi thanks for the reply I was looking for 30 rows per interface each representing a day. As for columns I was looking to get average bps in and out and then the max and Min in and out for each day, a column showing each date, the interface ID and the interfaces name. As for subset size is where it gets confusing as am looking to take this and apply a script allowing for the information to be retrieved somewhat automattically for each interface (not sure if this is strictly doable yet?) But for this example I guess maybe just a subset of the top 10 interfaces  for one and example and then say another example where it does it by interface ID so just the month for say e.g. interface ID =${4066}

  • Here's a query that will get this info for all interfaces:

    SELECT I.NodeID, I.InterfaceID, I.Node.Caption AS Node, I.Caption AS Interface, DATETRUNC('day', I.Traffic.ObservationTimestamp) AS [Day],

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

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

        MAX(I.Traffic.InMaxbps) AS InMaxBps, AVG(I.Traffic.OutMaxbps) AS OutMaxBps

    FROM Orion.NPM.Interfaces I

    WHERE I.Traffic.ObservationTimestamp > ADDDAY(-30, GETUTCDATE())

    GROUP BY I.NodeID, I.InterfaceId, I.Node.Caption, I.Caption, DATETRUNC('day', I.Traffic.ObservationTimestamp)

    If you want to limit it to a specific interface, just add "AND InterfaceID=4066" to there WHERE clause line. To limit it to top 10, you need to define an ordering. It gets more complicated because now you need a subquery to pick out which interfaces are part of the top 10. If you mean top 10 by average combined throughput, then you could do this:

    SELECT I.NodeID, I.InterfaceID, I.Node.Caption AS Node, I.Caption AS Interface, DATETRUNC('day', I.Traffic.ObservationTimestamp) AS [Day],

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

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

        MAX(I.Traffic.InMaxbps) AS InMaxBps, AVG(I.Traffic.OutMaxbps) AS OutMaxBps

    FROM Orion.NPM.Interfaces I

    WHERE I.Traffic.ObservationTimestamp > ADDDAY(-30, GETUTCDATE()) AND I.InterfaceID IN (

        SELECT TOP 10 T.InterfaceID

        FROM Orion.NPM.Interfaces T

        WHERE T.Traffic.ObservationTimestamp > ADDDAY(-30, GETUTCDATE())

        GROUP BY T.InterfaceID

        ORDER BY AVG(T.Traffic.Averagebps) DESC

    )

    GROUP BY I.NodeID, I.InterfaceId, I.Node.Caption, I.Caption, DATETRUNC('day', I.Traffic.ObservationTimestamp)

  • Hi thanks for the help with this and the querys however when using them SWQL studio is throwing up a ADDDAY function not found?

    many thanks

  • What version of NPM are you using?

  • Hmm, that's an old version. If you don't mind me asking, why not upgrade?

    I don't have a system with 10.6 around to test with, but I looked at the source history to see what functions were available then. I think this query might work:

    SELECT I.NodeID, I.InterfaceID, I.Node.Caption AS Node, I.Caption AS Interface, CONCAT(YEAR(I.Traffic.ObservationTimestamp),'-',MONTH(I.Traffic.ObservationTimestamp),'-',DAY(I.Traffic.ObservationTimestamp)) AS [Day],

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

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

        MAX(I.Traffic.InMaxbps) AS InMaxBps, AVG(I.Traffic.OutMaxbps) AS OutMaxBps

    FROM Orion.NPM.Interfaces I

    WHERE I.Traffic.ObservationTimestamp > GETUTCDATE()-30 AND I.InterfaceID IN (

        SELECT TOP 10 T.InterfaceID

        FROM Orion.NPM.Interfaces T

        WHERE T.Traffic.ObservationTimestamp > GETUTCDATE()-30

        GROUP BY T.InterfaceID

        ORDER BY AVG(T.Traffic.Averagebps) DESC

    )

    GROUP BY I.NodeID, I.InterfaceId, I.Node.Caption, I.Caption, CONCAT(YEAR(I.Traffic.ObservationTimestamp),'-',MONTH(I.Traffic.ObservationTimestamp),'-',DAY(I.Traffic.ObservationTimestamp))

  • Hi,

    Can you share where in the query I can filter the interfces in  by a Custom Properties?

    Many regards,

    Carlos

  • Between the ) and group by put in a line like

    And I.customproperties.<yourproperty> = '<yourvalue>'