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

Answer 1

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.

Answer 2

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 :)