device_id - Time Range of Netflow record

[JoshD]

Two questions:

1)I am trying to run some SQL queries (using MySQL workbench) against the plixer database. I am having a problem getting the value of the ifinfo.device_id field as it is a Varbinary/BLOB. I tried the standard cast by it gives me strange characters.
2)In the Netflow tables (e.g. `scrut_192_168_0_2`.`conv_1m_5_201110191801_201110191900`) How can I determine what the daterange of the each record is? I assume it is derived from flowStartSysUpTime and flowEndSysUpTime, how?

Thanks in advance for your help,
Josh

[pauld]

Hi Josh,

First off, is there something you're trying to accomplish that Scrutinizer can't already do?

1)I am trying to run some SQL queries (using MySQL workbench) against the plixer database. I am having a problem getting the value of the ifinfo.device_id field as it is a Varbinary/BLOB. I tried the standard cast by it gives me strange characters.

You are correct, our device id's are stored in binary, so you have to use a special function to insert and/or select.

There are two functions:

ascii to binary inet_a2b('127.0.0.1')

binary to ascii inet_b2a(device_id)

Here's an example select statement: "SELECT inet_b2a(device_id) FROM plixer.exporters;"

2)In the Netflow tables (e.g. `scrut_192_168_0_2`.`conv_1m_5_201110191801_201110191900`) How can I determine what the daterange of the each record is? I assume it is derived from flowStartSysUpTime and flowEndSysUpTime, how?

Each record is stored in a 1 minute interval, which is why we recommend setting the active timeout in NetFlow to 1 minute. To find the 1 minute interval that the record is for, you can select the intervalTime from the conversation table.

For example: "SELECT intervalTime,inet_b2a(sourceIPAddress) FROM scrut_192_168_0_2.conv_1m_5_201110191801_201110191900;"

Thanks,
Paul

[JoshD]

Paul,

Thank you for your help and reply. See my inline responses below:

Hi Josh,
First off, is there something you're trying to accomplish that Scrutinizer can't already do?

Yes - I am aggragating the netflow traffic based on categories in which Scruntinzer isn't aware of (e.g. location of devices).

1)I am trying to run some SQL queries (using MySQL workbench) against the plixer database. I am having a problem getting the value of the ifinfo.device_id field as it is a Varbinary/BLOB. I tried the standard cast by it gives me strange characters.

You are correct, our device id's are stored in binary, so you have to use a special function to insert and/or select.
There are two functions:
ascii to binary inet_a2b('127.0.0.1')
binary to ascii inet_b2a(device_id)
Here's an example select statement: "SELECT inet_b2a(device_id) FROM plixer.exporters;

Thank you - this worked along with casting the value to a char (e.g. SELECT cast(inet_b2a(device_id) as char) FROM `plixer`.`ifinfo`;). If this info isn't proprietary - where are those functions stored/defined?

2)In the Netflow tables (e.g. `scrut_192_168_0_2`.`conv_1m_5_201110191801_201110191900`) How can I determine what the daterange of the each record is? I assume it is derived from flowStartSysUpTime and flowEndSysUpTime, how?
Each record is stored in a 1 minute interval, which is why we recommend setting the active timeout in NetFlow to 1 minute. To find the 1 minute interval that the record is for, you can select the intervalTime from the conversation table.
For example: "SELECT intervalTime,inet_b2a(sourceIPAddress) FROM scrut_192_168_0_2.conv_1m_5_201110191801_201110191900;"Thanks,
Paul

How can I derive the time from interval time (which is an unsigned int - simply casting it to a date returns a null)? Does this join to another table that has the time?

Thanks in advance,
Josh

[pauld]

Hi Josh,

pauld wrote:Hi Josh,
First off, is there something you're trying to accomplish that Scrutinizer can't already do?

JoshD wrote:Yes - I am aggragating the netflow traffic based on categories in which Scruntinzer isn't aware of (e.g. location of devices).

Can you accomplish this by creating a report and adding multiple devices (exporters or hosts) from the same location into one report? How are you grouping by location?



JoshD wrote:How can I derive the time from interval time (which is an unsigned int - simply casting it to a date returns a null)? Does this join to another table that has the time?

You can convert the epoch into a time stamp like this:

"SELECT FROM_UNIXTIME(intervalTime) FROM scrut_127_0_0_1.conv_1m_5_201111281601_201111281700;"

- Paul

[JoshD]

Paul,

Thank you for the reply.

I probably could create "groups" based on location. However I look at data from multiple netflow collectors and find it easier to learn the db and use SQL then to learn the individual collectors UI.

FROM_UNIXTIME(intervalTime) is what I was looking for. This brings up another question though - apparently then intervalTime is in UTC. Are the names of the tables (e.g. scrut_127_0_0_1.conv_1m_5_201111281601_201111281700)also based on UTC time or are they based on the system time?

TY,
Josh

[pauld]

Hey Josh,

These tables are also based off of UTC time.

[JoshD]

Paul,

Thank you for your help.

TY,
Josh