Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Netflow Report

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.

Find more posts tagged with

Accepted answers

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.

All comments

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.

mcbridea

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.

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

@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

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

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

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.