|
| |
| |
|
|
|
|
|
NetFlow2SQL Collector automatically creates tables within a given database. The table name format is <basename>yyMMddhhmm, where basename - user defined name, yy - year, MM - month, dd - day, hh - hour, mm - minute.
A new table is created within a given interval: every N months, N days, N hours, N minutes. Optionally, the tables older than N months or N days are dropped.
Please note that time information is taken from incoming NetFlow packets. So, make sure your router's clock is correct. You can setup an NTP server in your router configuration.
Microsoft SQL tables contain the following columns: |
|
|
|
|
Num, bigint - record index,
FlowSeq, int - sequence counter of total flows seen,
DateTime, int - current count of seconds since 01/01/1970 (not UTC but local time),
SrcAddr, int - source IP address,
SrcPort, smallint - TCP/UDP source port number or equivalent,
DstAddr, int - destination IP address,
DstPort, smallint - TCP/UDP destination port number or equivalent,
NextAddr, int - IP address of next hop router,
Protocol, tinyint - IP protocol type (for example, TCP = 6; UDP = 17),
Packets, int - packets in the flow,
Bytes, int - total number of Layer 3 bytes in the packets of the flow,
Input, smallint - SNMP index of input interface,
Output, smallint - SNMP index of output interface.
|
|
|
|
All column values are converted and stored in host byte order (little-endian).
MySQL tables contain the same columns. The only difference is that all integer types are unsigned. |
|
|
|
Microsoft SQL Server |
|
|
|
Please use Microsoft SQL Server Management Studio to perform queries.
Since Microsoft SQL does not support unsigned integer types, you need to create two scalar-valued functions: |
|
|
|
|
CREATE FUNCTION UINT (@VAL int)
RETURNS bigint
AS
BEGIN
DECLARE @ReturnValue bigint
Set @ReturnValue = CONVERT(bigint,CONVERT(binary(4),@VAL))
RETURN @ReturnValue
END
GO CREATE FUNCTION USHORT (@VAL smallint)
RETURNS int
AS
BEGIN
DECLARE @ReturnValue int
Set @ReturnValue = CONVERT(int,CONVERT(binary(2),@VAL))
RETURN @ReturnValue
END
GO |
|
|
|
Additionally, you need to create one more scalar-valued function, which converts integer value to IP address string: |
|
|
|
|
CREATE FUNCTION INET_NTOA (@VAL bigint)
RETURNS varchar(15)
AS
BEGIN
DECLARE @ReturnValue varchar(15)
DECLARE @B1 tinyint
DECLARE @B2 tinyint
DECLARE @B3 tinyint
DECLARE @B4 tinyint
Set @B1=(@VAL & 0xFF000000)/0x1000000
Set @B2=(@VAL & 0xFF0000)/0x10000
Set @B3=(@VAL & 0xFF00)/0x100
Set @B4=@VAL & 0xFF
Set @ReturnValue = CAST(@B1 As varchar(3))+'.'+CAST(@B2 As varchar(3))+'.'+CAST(@B3 As varchar(3))+'.'+CAST(@B4 As varchar(3))
RETURN @ReturnValue
END
GO
|
|
|
|
Query sample: |
|
|
|
|
SELECT TOP 1000
Num,
dbo.UINT(FlowSeq) as FlowSeq,
DATEADD(s,dbo.UINT([DateTime]),'19700101') as DateTime,
dbo.INET_NTOA(SrcAddr) as SrcAddr,
dbo.USHORT(SrcPort) as SrcPort,
dbo.INET_NTOA(DstAddr) as DstAddr,
dbo.USHORT(DstPort) as DstPort,
dbo.INET_NTOA(NextAddr) as NextAddr,
Protocol,
dbo.UINT(Packets) as Packets,
dbo.UINT(Bytes) as Bytes
FROM <databasename>.dbo.<tablename> |
|
|
|
You can add to your query the WHERE clause: |
|
|
|
|
WHERE (dbo.USHORT(DstPort)=443) OR (dbo.USHORT(DstPort)=80)
|
|
|
|
Calculate packets and bytes: |
|
|
|
|
SELECT
dbo.INET_NTOA(DstAddr) as DstAddr,
SUM(dbo.UINT(Packets)) as PacketsTotal,
SUM(dbo.UINT(Bytes)) as BytesTotal
FROM <databasename>.dbo.<tablename>
GROUP BY DstAddr
ORDER BY BytesTotal;
|
|
|
|
MySQL Server |
|
|
|
Please use MySQL Query Browser to perform queries.
This is a query sample: |
|
|
|
|
SELECT
Num,
FlowSeq,
TIMESTAMPADD(SECOND,DateTime,'1970-01-01') as DateTime,
INET_NTOA(SrcAddr) as SrcAddr,
SrcPort,
INET_NTOA(DstAddr) as DstAddr,
DstPort,
INET_NTOA(NextAddr) as NextAddr,
Protocol,
Packets,
Bytes
FROM `<databasename>`.`<tablename>` LIMIT 1000;
|
|
|
|
Add to your query the WHERE clause:
|
|
|
|
|
WHERE DstPort=443 OR DstPort=80 |
|
|
|
Calculate packets and bytes: |
|
|
|
|
SELECT
DstPort,
SUM(Packets) as PacketsTotal,
SUM(Bytes) as BytesTotal
FROM `<databasename>`.`<tablename>`
GROUP BY DstPort
ORDER BY BytesTotal; |
|
|
|
|
|
 |
|
|
|
|
|
