Re: Network Flow Schema + Bulk Import/Updates

On 9/21/05, Michael L. Artz <dragon@october29.net> wrote:
> Tony Wasson wrote:
>
> >You can use the merge trigger below to do this. You'll need to add
> >some code to update the count. You may also benefit from using the new
> >constraint exclusion (table partitioning) in PostgreSQL 8.1. I am not
> >sure if CE works against the inet datatype -- if not, try converting
> >the IP to an integer.
> >
> >
>
> CE looked like it was just for parent/child relationships ... did I read
> that right?  I'm not sure how it applies.  And the table partitioning
> looks like its still on the todo list ... is that really the case?

CE is available in the PostgreSQL 8.1 beta. I was thinking you might
use it to slice up your data based on subnet. You can make it all fit
in a single table, but splitting it up could help keep the indexes
smaller. Are your SELECTs going to primarily base on source and
destination IPs? You have the possibility of a massive amount of rows!
Is there anything to be learned from large installations of snort
using a Pg backend?

> And as for getting data into the DB ... from earlier posts it sounded
> like standard practice was to bulk load the new data into a temporary
> table and then do an INSERT ... SELECT to load the data into the new
> table.  Is this still the case with the trigger, or can/should I just
> COPY the data straight into the final database?  And I assume that I
> should *not* delete my indexes while I'm loading the table, since the
> queries in the trigger can take advantage of them ... right?

The initial load can be a COPY or a bunch of INSERTs and I'd suggest
doing it without any triggers or indexes. Then build your indexes and
add your triggers.

The way I normally use this trigger is with INSERTs. You can use a
COPY against it too.

> Also, as a slight aside, has anyone created a data type for single IPs
> that is essentially an integer (i.e. 4 bytes) that works with the
> standard functions for INET?

I've never seen a datatype that does this. Here are some functions to
convert INET to INT8 and back.

CREATE OR REPLACE FUNCTION inet_ntoa(int8) RETURNS inet
AS '
--from http://www.snort.org/docs/snortdb/snortdb_faq.html#faq_b4
    SELECT (
        (($1>>24) & 255::int8) || ''.'' ||
        (($1>>16) & 255::int8) || ''.'' ||
        (($1>>8)  & 255::int8) || ''.'' ||
        ($1     & 255::int8)
        )::INET;
' LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION inet_aton(inet) RETURNS int8
AS '
  -- this ignores any subnetting information
  --http://www.mcabee.org/lists/snort-users/Oct-01/msg00426.html
  SELECT
  (
    (split_part($1::TEXT,''.'',1)::INT8*16777216) +
    (split_part($1::TEXT,''.'',2)::INT8*65536) +
    (split_part($1::TEXT,''.'',3)::INT8*256) +
    (split_part(split_part($1::TEXT,''.'',4),''/'',1))::INT8
  )::INT8;
' LANGUAGE 'sql';