Searching session tables from previous days

[spellfish]

@leonward I have a question regarding executing a search (or a fetch), from sessions that have occurred before the current date. In my session db, session tables from the previous days are tagged with date/timestamps, but for some reason a search doesn't seem to return results from previous days. Is there a specific set of options to do this? I've used --last values that stretch into a previous 24 hour period, as well as --eachway. --stime and --etime with appropriate values don't seem to work either. It seems the client only searches through the current days table. Any tips on how to do this correctly?

[leonward]

It's working for me using the latest code in the repo. Here is an example:

[17:07:44]xxxx@drax~/code/OpenFPC$ ./openfpc-client -a search --stime 'Tue Jan 20 00:00:00 GMT 2015' --etime "Tue Jan 20 00:01:00 GMT 2015" -dpt 53 -u XXXX --limit 1
* Reading configuration from /Users/lward/.openfpc-client.rc

   * openfpc-client 0.9 *
     Part of the OpenFPC project - www.openfpc.org

Password for user xxxx :
=====================================================================================================================================================
 Custom Search
=====================================================================================================================================================
 Start: Tue Jan 20 00:00:00 2015 (Europe/London)
 End  : Tue Jan 20 00:01:00 2015 (Europe/London)
 Node : Home_Node
 Rows : 1
 SQL  : SELECT start_time,INET_NTOA(src_ip),src_port,INET_NTOA(dst_ip),dst_port,ip_proto,src_bytes, dst_bytes,(src_bytes+dst_bytes) as total_bytes
    FROM session IGNORE INDEX (p_key) WHERE unix_timestamp(CONVERT_TZ(`start_time`, '+00:00', @@session.time_zone))
    between 1421712000 and 1421712060 AND dst_port='53' ORDER BY start_time DESC LIMIT 1
=====================================================================================================================================================
  Row            Start Time         Source IP   sPort       Destination   dPort   Proto     Src Bytes     Dst Bytes   Total Bytes           Node Name
    0   2015-01-20  0:00:39    xxx.xxx.xx.xxx   46274           x.x.x.x      53     udp         16620         47340         63960           Home_Node
=====================================================================================================================================================
[17:09:06]xxxx@drax~/code/OpenFPC$

[spellfish]

Ok. Thank you for the example - I will test in house and get back to you asap.

[spellfish]

@leonward apologies for the delay here. I'm finally returning to this project. Here is what I get from running a similar command on my proxy host. My openfpc-client is still based off of 0.9.1, so maybe I have discovered something that is fixed.

neha@xxxxxx:/etc/openfpc$ openfpc-client -device ids_dev -a search --stime 'Mon Jan 1 00:00:00 UTC 2015' --etime 'Fri Jan 10 00:00:00 UTC 2015' -dpt 443 -u xxx -p xxx

• openfpc-client 0.9 * Part of the OpenFPC project - www.openfpc.org

======================================== Use of uninitialized value in concatenation (.) or string at /usr/bin/openfpc-client line 285. ======================================== Start: Wed Jan 28 09:07:14 2015 (UTC) End : Wed Jan 28 09:07:14 2015 (UTC) Use of uninitialized value in concatenation (.) or string at /usr/bin/openfpc-client line 290. Rows : Use of uninitialized value in concatenation (.) or string at /usr/bin/openfpc-client line 291. SQL : ======================================== Row Use of uninitialized value in numeric lt (<) at /usr/bin/openfpc-client line 311. ========================================

[spellfish]

Ok, I have confirmed that this is only complaining from my proxy host. From the node itself, this works without error. I'm not sure what might be happening on the proxy node, if you have any suggestions I'm all ears.

Also - do you have any recommendations on how to purge old sessions along with old packets? I shutdown openfpc for quite a few days. Now there are stale sessions, and while my packet storage is mostly full, it seems openfpc cannot find the packet specified:

neha@xxxxxx:~$ openfpc-client -a search --stime 'Tue Jan 20 12:55:00 UTC 2015' --etime 'Wed Jan 21 08:00:00 UTC 2015' -dpt 443 -u xxxx -p xxxx --limit 1

This yields the result, and prints a single packet from this time window. Clearly the session db has retained this information. Though on fetching, the packet is not found:

neha@xxxxxx:~$ ^search^fetch openfpc-client -a fetch --stime 'Tue Jan 20 12:55:00 UTC 2015' --etime 'Wed Jan 21 08:00:00 UTC

OFPC Request Failed: Insufficient constraints for request (Null or invalid BPF)

I'd like to start with a fresh storage window, and attempt a fetch without such a long gap in time. What is the best way to do this?

[leonward]

Ill take a look and see if I can replicate the bug.

Sent from a mobile device. Apologies for any typos but they happen.

On 28 Jan 2015, at 10:26, nehafish [email protected] wrote:

Ok, I have confirmed that this is only complaining from my proxy host. From the node itself, this works without error. I'm not sure what might be happening on the proxy node, if you have any suggestions I'm all ears.

— Reply to this email directly or view it on GitHub.

[leonward]

Found a load of issues with the proxy operation and searches. I've made some changes and added some debugging. Before you check it out, I've also changed the way that the routes work. You no longer need to specify a user/pass for each node. The user/pass entered by the user for the connection is now sent to each proxy. This improves a lot of usability and logging. Let me know how you get on.

[spellfish]

ok, I've forked this repo so I can add remote db host changes, and then I can test with the new proxy changes. I'm still using 0.9.1 in my implementation, so there's an upgrade that's necessary.