7 Replies Latest reply on Jul 14, 2017 11:40 AM by tdanner

    SWQL Complex query

    loftyyy

      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

        • Re: SWQL Complex query
          tdanner

          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?

          1 of 1 people found this helpful
            • Re: SWQL Complex query
              loftyyy

              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}

                • Re: SWQL Complex query
                  tdanner

                  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)
                  
                  1 of 1 people found this helpful
                    • Re: SWQL Complex query
                      loftyyy

                      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

                        • Re: SWQL Complex query
                          tdanner

                          What version of NPM are you using?

                            • Re: SWQL Complex query
                              loftyyy

                              NPM 10.6.1

                                • Re: SWQL Complex query
                                  tdanner

                                  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))