Reading Netflow Packet Content using MySQL

Question

I'm developing a software that capture every udp packet that pass by my firewall (a 5.4 OpenBSD virtual machine) from a virtual machine that I have and save the packet in a MySQL database.

My code is basically:

try
{
DatagramSocket serverSocket = new DatagramSocket(9876);        
byte[] receiveData = new byte [1024];
DatagramPacket receivePacket = new DatagramPacket(receiveData, receiveData.length);
serverSocket.receive(receivePacket);
String sentence = new String(receivePacket.getData());
InetAddress IPAddress = receivePacket.getAddress();
Connection conn = DriverManager.getConnection(url, user, password);
String sql = "INSERT INTO tabela_netflow (fluxo) values (?)";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setBytes(1,receivePacket.getData());
int row = statement.executeUpdate();
if (row > 0) 
{
   System.out.println("Packet saved:" +receivePacket.getData());
}

} catch (SQLException ex) 
{
   ex.printStackTrace();
}

The code is working just fine, my problem is that when I try to view the content saved in the database, there are alot of weird characters, since it's binary (I think), I can't read it to get the information I need (the flow record format, wich can be viwed here)

My table is very simple: there are 2 columns, code (int, auto increment) and another column, fluxo (varbinary(10000)). This is what I see when I click "open value in editor" at MySQL Workbench: Packet Content

John Murphy · Answer 1 · 2014-06-11T13:55:45.350

0

NetFlow 5 datagrams are very compact: each field is just a number. You're better off parsing it out in your code and either writing an annotated string to your database or adding database fields for each of the fields in the NetFlow datagram.

The parsing is easy: you just count off the number of bytes and interpret them as integers or characters, depending on field.

Edit: The format is here: Cisco Flow Record formats They list the byte offset, so if you want dOctets (you do, from what you describe), take bytes 20-24 of the payload and convert them to an integer.

edited Jun 11 '14 at 13:55

answered Jun 04 '14 at 14:15

John Murphy

397
3
14

I understand what you meant, since the netflow header format is known, you can count the bytes based on the bytes of each field, but how can I do that? I only have receivePacket.getData() to get the packet content, and I don't know how to split using this method.. – Ganso Jun 08 '14 at 02:54
See here for the format: http://www.cisco.com/c/en/us/td/docs/net_mgmt/netflow_collection_engine/3-6/user/guide/format.html – John Murphy Jun 11 '14 at 13:53
I mean, I know the format, I just don't know how to split from receivePacket.getData() to get every information I need..the method just return a byte vector containing the buffer data, but how I am supposed to get exactly any part I need? For example: if I need the version (first 0-1 bytes) byte[] buffer = new byte[1024]; buffer = receivePacket.getData(); int version = buffer[0]; I don't know if I can do this, and I can't use parseInt since I need to send a string to conversion.. – Ganso Jun 12 '14 at 14:53

kristophbarbour · Answer 2 · 2014-07-28T01:24:32.507

You can convert the unsigned bytes to and int with:

public static int unsignedByteToInt(byte b) {
    return (int) b & 0xFF;
}

This will return the Integer value of the byte. However you are looking for a value from byte[0-1] correct? You will have sum these values, here is a very quick (and probably horrible way of doing so):

public static int unsignedByteToInt(byte[] b, int offset, int len) {
    int r = 0;
    for(int i = 0; i < len; i++){
        r += unsignedByteToInt(b[offset+i]);// the method shown earlier
    }
    return r;
}

Given a DatagramPacket you can call:

int version = unsignedByteToInt(receivedDatagramPacketPacket.getData(), 0,2);

Here are the two methods for Strings (by the way I have not tested these :-\ ):

public static String byteToHex(byte b){
    int i = b & 0xFF;
    return Integer.toHexString(i);
}

public static String byteToHex(byte[] b, int offset, int len){
    String r = "";
    for(int i = 0; i < len; i++){
        r += byteToHex(b[offset+i]);
    }
    return r;
}

Good luck :)

I tested your code, it might have worked for version (I got version = 5 when I tested, sounds ok) but then I tried on the next 2 bytes (bytes 2-3) for count and I got count = 130 (and it should be between 1-30), so I don't know what to do.. — Ganso, Aug 22 '14 at 04:38

Reading Netflow Packet Content using MySQL

2 Answers2