4 Replies Latest reply on Jun 17, 2010 4:52 PM by adeimel

    Netflow Report

    gwsample

      I am trying to get a netflow report or SQL query to find some information about some traffic on my network.  I have 41 routers sending netflow information to my Orion server.  What I would like to see is a report for a specific endpoint showing totals for the amount for traffic transmitted through each router for this node for a given time period.   Does anyone know of an easy way to do this?  Any help would be appreciated. 

        • Re: Netflow Report
          adeimel

          I am working on this very issue with my dba now and should have something based off of ip groups working in a day or so. 

           

          I will post to the exchange once I confirm it works correctly.

          • Re: Netflow Report
            Andy McBride

            Does it have to be a report? If not then the endpoint search resource might do it, depending on how many endpoints you have to track.

            • Re: Netflow Report
              adeimel

              this may or may not fit you needs but should be able to be customized as you require on your end.

              My requirement was to do a top 100 conversations including a protocol breakdown over the last 24 hours PER ip address group enabled.

              So for this to run you will need to define a ip address group and enable it to display on the webpage for this script to pick it up.

              This is just a first draft on the issue for us so its not quite fast enough to run out of the report writer yet so it should be run straight from SQL at of course your own risk.

              For about 35 netflow nodes it takes about 8 minutes to run currently.

              I am only at the very early stages of making sure it does return what I need but so far it appears to be working.

               

              SET NOCOUNT ON

              CREATE TABLE #app(

              Protocol_name varchar(15),

              sourceIPsort bigint,

              DestIPsort bigint,

              port_number varchar(20),

              Application_name varchar(255),

              Sum_of_Bytes_Transferred bigint)

               

              CREATE TABLE #sum(

              groupname varchar(255),

              sourceHostname varchar(255),

              Desthostname varchar(255),

              caption varchar(255),

              sourceIPsort bigint,

              DestIPsort bigint)

               

              Create table #result(

              groupname varchar(255),

              sourceHostname varchar(255),

              Desthostname varchar(255),

              caption varchar(255),

              Protocol_name varchar(15),

              port_number varchar(20),

              Application_name varchar(255),

              Sum_of_Bytes_Transferred varchar(50), 

              SumTansferred bigint)

               

               

              declare @Group Varchar(255), @Group2 Varchar(255), @IPStart bigint, @IPEND bigint, @StartDate datetime, @EndDate datetime

               

               

              SET @StartDate = CAST((ROUND(CAST(GetDate() - 1 AS FLOAT), 0, 1)) as datetime)

              SET @EndDate = GetDate()

               

              DECLARE Svr CURSOR READ_ONLY

              FOR

                    SELECT IPAddressGroupName, IPRangeStart, IPRangeEnd

                    FROM IPAddressGroups

                    WHERE Enabled = 1

                    ORDER BY IPAddressGroupName

               

              OPEN Svr

               

              FETCH NEXT FROM Svr

              INTO @Group, @IPStart, @IPEnd

               

              WHILE @@FETCH_STATUS = 0

              BEGIN

               

                    INSERT INTO #sum

                    SELECT @Group, fcs.FullHostname, fcd.FullHostname, n.Caption, nfcs.SourceIPSort, nfcs.DestIPSort

                    FROM NetflowConversationSummary nfcs

                    LEFT OUTER JOIN FlowCorrelation fcs ON nfcs.SourceIPSort = fcs.IPAddressSort

                    LEFT OUTER JOIN FlowCorrelation fcd on nfcs.DestIPSort = fcd.IPAddressSort

                    INNER JOIN Nodes N on nfcs.NodeID = n.NodeID

                    WHERE nfcs.DateTime BETWEEN @StartDate AND @EndDate

                      AND EXISTS(SELECT 1 FROM NetFlowSources WITH(nolock) WHERE NetFlowSources.InterfaceID=nfcs.InterfaceIDRx AND NetFlowSources.Enabled=1)

                      AND (nfcs.DestIPSort BETWEEN @IPStart and @IPEnd

                       OR nfcs.SourceIPSort BETWEEN @IPStart and @IPEnd)

                    GROUP BY fcs.FullHostname, fcd.FullHostname, n.Caption, nfcs.SourceIPSort, nfcs.DestIPSort

               

                    INSERT INTO #app

                    SELECT  TP.ProtocolName AS Protocol_Name, NAS.SourceIPSort, NAS.DestIPSort, NAS.Port AS Port_Number,  NAS.AppName AS Application_Name,  SUM(NAS.TotalBytes) AS SUM_of_Bytes_Transferred

                    FROM NetflowApplicationSummary2 NAS

                    INNER JOIN TransportProtocols TP ON (NAS.Protocol = TP.ProtocolID)      

                    WHERE DateTime BETWEEN @StartDate AND @EndDate

                      AND (nas.DestIPSort BETWEEN @IPStart and @IPEnd

                       OR nas.SourceIPSort BETWEEN @IPStart and @IPEnd)

               

                    GROUP BY TP.ProtocolName, NAS.Port, NAS.AppName,  NAS.SourceIPSort, NAS.DestIPSort 

               

               

               

                    SET @group2 = @Group

                    FETCH NEXT FROM Svr

                    INTO @Group, @IPStart, @IPEnd

               

                    IF @group != @group2

                    BEGIN

                          insert into #result

                          SELECT top 100 groupname, sourceHostname, Desthostname, caption, Protocol_name, port_number, Application_name,

                          case when Sum_of_Bytes_Transferred/1024/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024/1024 as varchar) + ' GB' 

                                 when Sum_of_Bytes_Transferred/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024 as varchar) + ' MB' 

                                 when Sum_of_Bytes_Transferred/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024 as varchar) + ' KB' 

                                 ELSE CAST(Sum_of_Bytes_Transferred as varchar) + ' B'

                          END, Sum_of_Bytes_Transferred

                          from #sum a

                          join #app b on a.sourceIPsort = b.sourceIPsort and a.DestIPsort = b.DestIPsort

                          Group by groupname, sourceHostname, Desthostname, caption, Protocol_name, port_number, Application_name, case when Sum_of_Bytes_Transferred/1024/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024/1024 as varchar) + ' GB' 

                                 when Sum_of_Bytes_Transferred/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024 as varchar) + ' MB' 

                                 when Sum_of_Bytes_Transferred/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024 as varchar) + ' KB' 

                                 ELSE CAST(Sum_of_Bytes_Transferred as varchar) + ' B'

                          END, Sum_of_Bytes_Transferred

                          ORDER BY 9 desc

               

                          truncate table #sum

                          TRUNCATE table #app

               

                    END

               

              END

               

              CLOSE Svr

              DEALLOCATE Svr

               

                          insert into #result

                          SELECT top 100 groupname, sourceHostname, Desthostname, caption, Protocol_name, port_number, Application_name,

                          case when Sum_of_Bytes_Transferred/1024/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024/1024 as varchar) + ' GB' 

                                 when Sum_of_Bytes_Transferred/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024 as varchar) + ' MB' 

                                 when Sum_of_Bytes_Transferred/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024 as varchar) + ' KB' 

                                 ELSE CAST(Sum_of_Bytes_Transferred as varchar) + ' B'

                          END, Sum_of_Bytes_Transferred

                          from #sum a

                          join #app b on a.sourceIPsort = b.sourceIPsort and a.DestIPsort = b.DestIPsort 

                          Group by groupname, sourceHostname, Desthostname, caption, Protocol_name, port_number, Application_name, case when Sum_of_Bytes_Transferred/1024/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024/1024 as varchar) + ' GB' 

                                 when Sum_of_Bytes_Transferred/1024/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024/1024 as varchar) + ' MB' 

                                 when Sum_of_Bytes_Transferred/1024 > 1 then cast(CAST(Sum_of_Bytes_Transferred as float)/1024 as varchar) + ' KB' 

                                 ELSE CAST(Sum_of_Bytes_Transferred as varchar) + ' B'

                          END, Sum_of_Bytes_Transferred

                          ORDER BY 9 desc

               

              SELECT groupname, sourceHostname, Desthostname, caption, Protocol_name, port_number, Application_name, Sum_of_Bytes_Transferred  

              FROM #result

              ORDER BY 1 asc, SumTansferred DESC

               

              drop table #app

              drop table #result

              drop table #sum

                • Re: Netflow Report
                  adeimel

                  did some more testing... 

                   

                  if you specify a single ip address group by replacing 

                   

                  SELECT IPAddressGroupName, IPRangeStart, IPRangeEnd

                        FROM IPAddressGroups

                        WHERE Enabled = 1

                        ORDER BY IPAddressGroupName

                   

                  with 

                        SELECT IPAddressGroupName, IPRangeStart, IPRangeEnd

                        FROM IPAddressGroups

                        WHERE IPAddressGroupName = 'Your IP Addess Group'

                        ORDER BY IPAddressGroupName

                   

                  It will (at least in my case) run fast enough to be scheduled as a report or to run in the UI.