5 Replies Latest reply on Jun 9, 2017 10:37 AM by sum_giais

    SQL Query Question

    ldabrowski

      I'm looking for a way to extract some Netflow data from the SQL server. Netflow only allows you to look at the data from a specific time range but I'm looking for a way to get the data for business hours only e.g. Top Applications, Specific IP Address Group from 04/01/17 to 05/01/17 9:00AM to 5:00PM only.

        • Re: SQL Query Question
          rschroeder

          There are a LOT of Orion SDK SWQL NetFlow queries you could look into. If one of them isn't the exact tool you need, I bet you'd be able to edit it and make the query you want.  Here's what I found in my SDK for NetFlow pre-built queries:

           

           

          I also found a pretty interesting pre-built Report that looks like it would be a great guide for building exactly what you want--copy it and try editing it to provide your specific address ranges and time frames:

           

           

          Finally, Thwack has a great tutorial on building a custom Business Hour Service Level Agreement Report here: Node SLA Availability Report - Last Month - Business Hours/Days

           

          I think it could be a useful guide to help you get started down the right path using custom fields and limiting hours reported to those you define.

           

          If this doesn't provide the information required to build the right custom NetFlow report, just search Thwack for "Custom SLA Report" and you'll find some good "how to" information. Let us know what you build; share some screen shots to show how you did it. You'll be the next Thwack Rock Star!

            • Re: SQL Query Question
              bmacmt

              As a starting place, this thread:

               

              Using SWQL to find large consumers of bandwidth

               

              Includes code to loo at certain times. It's code I use to look at bandwidth consumers at a smaller interval but might be a good starting place for you.

              From my understanding, you can't get to netflow data via sql, only swql which is solarwinds dB. you need to down the the SWQL Studio.

               

              bob

               

              oh, also, in the sql example referenced you can use the ToLocal() function instead of AddHours() to adjust the time.

                • Re: SQL Query Question
                  ldabrowski

                  Hi. So how do I build a query that allows me to look at application usage on a router in a given week but from 9:00AM to 5:00PM? I'd like to skip all the 'outside business hours' data that's in between.

                   

                  I tried running something like this in SWQL Studio but got a error instead.

                  SELECT  f.SourceIP, f.DestinationIP, f.Bytes, f.totalbytes, f.timestamp, AddHour(-6,f.timestamp)

                  f.ingressbytes,

                  FROM Orion.Netflow.Flowsbyconversation

                  inner join orion.npm.interfaces i on f.interfaceidrx = i.interfaceid

                  inner join orion.nodes n on n.nodeid = i.nodeid

                  WHERE

                  f.TimeStamp > '2017-05-22 09:00:00' AND f.TimeStamp <= '2017-05-26 17:00:00'

                  and n.nodename='myrouter'

                    • Re: SQL Query Question
                      sum_giais

                      Check out this thread: Port/Application Alert in NPM/NTA

                      • Re: SQL Query Question
                        sum_giais

                        Also I think there are some typos in your query. Here's a revised one that is working. There was a missing 'f' after 'FROM Orion.Netflow.Flowsbyconversation', and you're using a WHERE clause trying to limit it by f.TimeStamp when the query hadn't defined it as such. Also removed the 'AddHour(-6,f.timestamp)' part, and there was an extra comma at the end of the column selects before the FROM table 'f.ingressbytes,'

                         

                        Give this a shot.

                         

                        ###########################################

                         

                         

                        SELECT  f.SourceIP, f.DestinationIP, f.Bytes, f.totalbytes, f.timestamp,

                        f.ingressbytes

                        FROM Orion.Netflow.Flowsbyconversation f

                        inner join orion.npm.interfaces i on f.interfaceidrx = i.interfaceid

                        inner join orion.nodes n on n.nodeid = i.nodeid

                        WHERE

                        f.TimeStamp > '2017-06-08 09:00:00' AND f.TimeStamp <= '2017-06-08 17:00:00'

                        and n.nodename='myrouter'