vdat='2003-07-01'
bdat='2003-08-01'

# Auswertung tabelleninhalt
echo "#"
echo "# Anzahl Sätze in der DB, von Datum, bis Datum, Anzahl Tage"
echo "#"
psql -U pgsql nitpicker << EOF1
  EXPLAIN ANALYZE select count(*),
  min(date) as Von, max(date) as Bis, max(date)-min(date) as Tage
  from flows;
EOF1

# Auswertung Tage, Protokoll, Bytes, Pakete aufsummiert
echo "#"
echo "# Tagesstatistik je Protokoll"
echo "#"
psql -U pgsql nitpicker << EOF2
EXPLAIN ANALYZE  select f.date as datum, p.pname as Protokoll,
    sum(f.sdbytes) + sum(f.dsbytes) as Bytes,
    sum(f.sdpkgs) + sum(f.dspkgs) as Pakete
  from protos p, flows f
  where
  f.date >= '$vdat' and f.date <= '$bdat' and
  f.type = p.pnum
  group by datum, protokoll
  order by datum, Bytes desc
  ;
EOF2

echo "#"
echo "# Tagesstatistik gesamt"
echo "#"
psql -U pgsql nitpicker << EOF3
EXPLAIN ANALYZE  select f.date as datum,
    sum(f.sdbytes) + sum(f.dsbytes) as Bytes,
    sum(f.sdpkgs) + sum(f.dspkgs) as Pakete
  from flows f
  where
  f.date >= '$vdat' and f.date <= '$bdat'
  group by datum
  order by datum, Bytes desc
  ;
EOF3

echo "#"
echo "# Top 50: max( Bytes )"
echo "#"
psql -U pgsql nitpicker << EOF4
EXPLAIN ANALYZE  select count(*),
  network(set_masklen(sip,16)), network(set_masklen(dip,16)),
  sum(sdbytes) + sum(dsbytes) as bytes
  from flows f
  where
  f.date >= '$vdat' and f.date <= '$bdat'
  group by network(set_masklen(sip,16)), network(set_masklen(dip,16))
  order by bytes desc
  limit 50;
EOF4

#psql -U pgsql nitpicker << EOF
#  select
#  -- n1.nname as src, n2.nname as dst
#  count(*)
#  from flows f, networks n1, networks n2, protos p
#  where
#  f.sip << n1.nnet and
#  f.dip << n2.nnet
#EOF

#psql -U pgsql nitpicker -c "\
#select \
#  f.sip, f.dip, p.pname, \
#  f.sdpkgs, f.dspkgs \
#  from flows f, protos p where \
#  not f.sip << '194.39.177.0/24' and \
#  not f.sip << '194.99.75.0/24' and \
#  not f.sip = '195.212.179.2' and \
#  not f.dip << '194.39.177.0/24' and \
#  not f.dip << '194.99.75.0/24' and \
#  not f.dip = '195.212.179.2' and \
#  f.type = p.pnum \
#  order by sip, dip;"

#psql -U pgsql nitpicker << EOFX
#  drop table volumen;
#  create table volumen (
#    year int4,
#    week int4,
#    snet inet,
#    dnet inet
#  );
#  drop table n_temp;
#  create table n_temp (
#    year int4,
#    week int4,
#    snet inet,
#    dnet inet,
#    sdbytes int8,
#    dsbytes int8,
#    sdpkgs int8,
#    dspkgs int8
#  );
#EOFX
#date
#psql -U pgsql nitpicker << EOF
#  insert into n_temp
#  select
#    date_part( 'year', f.date ) ,
#    date_part( 'week', f.date ),
#    n.nnet, network( set_masklen( f.dip, 16 ) ),
#    sum( f.sdbytes ), sum( f.dsbytes ),
#    sum( f.sdpkgs ), sum( f.dspkgs )
#  from flows f, networks n
#  where
#    f.sip <<= n.nnet and
#    not f.dip <<= n.nnet
#  group by f.date, n.nnet, network( set_masklen( f.dip, 16 ) )
#  union
#  select
#    date_part( 'year', f.date ) ,
#    date_part( 'week', f.date ),
#    n.nnet, network( set_masklen( f.sip, 16 ) ),
#    sum( f.dsbytes ), sum( f.sdbytes ),
#    sum( f.dspkgs ), sum( f.sdpkgs )
#  from flows f, networks n
#  where
#    f.dip <<= n.nnet and
#    not f.sip <<= n.nnet
#  group by f.date, n.nnet, network( set_masklen( f.sip, 16 ) )
#  union
#  select
#    date_part( 'year', f.date ) ,
#    date_part( 'week', f.date ),
#    network( set_masklen( f.sip, 16 ) ), network( set_masklen( f.dip, 16 ) ),
#    sum( f.sdbytes ), sum( f.dsbytes ),
#    sum( f.sdpkgs ), sum( f.dspkgs )
#  from flows f, networks n
#  where
#    not f.sip <<= n.nnet and
#    not f.dip <<= n.nnet
#  group by f.date, network( set_masklen( f.sip, 16 ) ), network( set_masklen( f.dip, 16 ) )
#EOF
#date
#  f.date >= '2003-03-28' and f.date <= '2003-04-01'