3 Replies Latest reply on Aug 15, 2017 8:12 AM by mesverrum

    Netflow Chart Query (SWQL/SQL)

    max348

      I want to create a custom dashboard showing netflow data (as a chart) for a particular interface for last 24 hours. I want to show netflow from router interfaces connecting to different sites. Can anyone help me with the query and how i can get it done.

       

      I tried below query but it show me data for 24hrs which is not upto the current time. For example if current time is 9:00 AM the query will show last 24 hrs starting from 5:00 AM.

       

      SELECT

      timestamp as time,

         NodeID as node,

          InterfaceIDRx as Interface,

         EgressBytes AS eBytes,

         IngressBytes AS iBytes,

         TotalBytes AS TotalBytes

      FROM Orion.NetFlow.Flows AS flow

      WHERE

        nodeid = xxx and InterfaceIDRx = xxx

      GROUP BY time,node,eBytes, iBytes,TotalBytes,Interface

       

       

      Would appreciate if anyone could help me on this.

        • Re: Netflow Chart Query (SWQL/SQL)
          mesverrum

          Played with it in my lab and it seems like the timestamp seems to do some kind of localization, when I run it from my laptop I get the most recent times in PST but when I run the same query directly on my lab server they show up in UTC.  I added a tolocal function, and you can skip filtering on the nodeid since interface id's are globally unique, let me know if that works better for you.

           

          SELECT top 1000

          tolocal(timestamp) as time,

             NodeID as node,

              InterfaceIDRx as Interface,

             EgressBytes AS eBytes,

             IngressBytes AS iBytes,

             TotalBytes AS TotalBytes

          FROM Orion.NetFlow.Flows AS flow

          WHERE

            InterfaceIDRx = xxx

          GROUP BY timestamp,node,eBytes, iBytes,TotalBytes,Interface

          1 of 1 people found this helpful
            • Re: Netflow Chart Query (SWQL/SQL)
              max348

              Hi mesverrum.. thanks for the reply.. i will give at a try.. do you have any other query to show netflow information for a particular interface or is my above query good enough ?? i want to create a dashboard that shows netflow traffic from routers interface that connects to different sites... would appreciate your advice as i am not so good with coding

                • Re: Netflow Chart Query (SWQL/SQL)
                  mesverrum

                  This looks like a decent query to track what you say you want.  One thing you may want to keep in mind with a chart like this is that netflow by default filters to the top 95th percentile of traffic, so this chart would never be 100% accurate and won't match exactly to what you see if you just set up a total bps chart for the same interface, should be close but not exact.