bash+snort-pgsqlで健康状態、統計情報の確認。

■bash+snort-pgsqlで健康状態、統計情報の確認。

　参考：postgreSQLの健康状態をチェックする
　http://www.plusmb.jp/2009/08/07/4299.html

　上位セグメントからも下位セグメントからも切り離せたので、
　同じセグメントのみ捕捉するsnortのDB状態を見る事に意味が出てきた。

■同じセグメントからは以下の通り、内部テストのみを検知している。
　別のセグメントではもっとテスト項目が多いのだが、そこはスルーで。。。

　※検知の種類
　　ICMP Test(ping)
　　ポートスキャン(nmap)
    MS SQL(OpenVASによるアタックテスト)
    SIP(ssh使用時)

$ echo "select sig_name from signature;" | sudo -u snort psql -U snort -d snort -A -F\, -t
ICMP Test
COMMUNITY SIP TCP/IP message flooding directed to SIP proxy
COMMUNITY EXPLOIT LANDesk Management Suite Alerting Service buffer overflow
MS-SQL probe response overflow attempt
(portscan) TCP Portsweep
(portscan) Open Port
(portscan) TCP Portscan

$ THISTIME=`env LANG=C date '+%b *%-d %H:%M:'`; sudo pkill -USR1 snort
$ sudo grep "^${THISTIME}.*snort" /var/log/syslog |    sed s/".*snort\[[0-9]*\]\: "//g | grep -v "0.000%\|\: *0"
*** Caught Usr-Signal
===============================================================================
Packet Wire Totals:
   Received:     23990273
   Analyzed:     23990138 (99.999%)
    Dropped:          135 (0.001%)
===============================================================================
Breakdown by protocol (includes rebuilt packets):
     IPV6: 10229      (0.043%)
      IP4: 23975306   (99.938%)
  IP4disc: 8493       (0.035%)
      TCP: 23917432   (99.697%)
      UDP: 49268      (0.205%)
      ARP: 4605       (0.019%)
  DISCARD: 8493       (0.035%)
InvChkSum: 213354     (0.889%)
    Total: 23990140
===============================================================================
Action Stats:
ALERTS: 4486
LOGGED: 5658
===============================================================================
Frag3 statistics:
===============================================================================
Stream5 statistics:
            Total sessions: 54064
              TCP sessions: 54064
TCP StreamTrackers Created: 61299
TCP StreamTrackers Deleted: 56792
              TCP Timeouts: 18347
       TCP Segments Queued: 2
     TCP Segments Released: 2
       TCP Rebuilt Packets: 2
         TCP Segments Used: 2
              TCP Discards: 153951
           TCP Port Filter
                   Tracked: 23724375
           UDP Port Filter
===============================================================================
HTTP Inspect - encodings (Note: stream-reassembled packets included):
    POST methods:                   2
    GET methods:                    2
    Headers extracted:              4
    Header Cookies extracted:       2
    Total packets processed:        14343280
===============================================================================
dcerpc2 Preprocessor Statistics
===============================================================================
SSL Preprocessor:
   SSL packets decoded: 86463
          Server Hello: 9142
           Certificate: 1834
           Server Done: 11151
   Client Key Exchange: 2
         Change Cipher: 9364
    Server Application: 9107
                 Alert: 3912
  Unrecognized records: 64305
      Sessions ignored: 9104
    Detection disabled: 1421
===============================================================================

■snortデータベースの容量確認。

$ DBTOTAL=`echo "select pg_database_size('snort');" | \
  sudo -u snort psql -U snort -d snort -A -F, -t`

$ echo $DBTOTAL | awk '{print $1" = "$1/1024"KB = "$1/1024/1024"MB"}'
45430544 = 44365.8KB = 43.3259MB

■上記のデータベースサイズからsnortデータベースのテーブル容量を百分率計算してみる。
　ついでに容量が1%以上のテーブルに絞る。
　※DB容量の58%〜60%程度は使用中のテーブル以外に使われている様子。

$ echo "select tablename from pg_tables where tableowner = 'snort';" | \
  sudo -u snort psql -U snort -d snort -A -F, -t | \
  for list in `xargs`;do     echo -n "${list},"; \
    echo "select pg_relation_size('${list}');" | \
    sudo -u snort psql -U snort -d snort -A -F, -t; \
  done | sort -nr -t\, -k2 | \
  awk -F\, '{print $1","$2","($2/'${DBTOTAL}')*100}' | \
  awk -F\, '{sum += $3};($3>=1.0) {print $0} END {print sum}' | \
  sed s/"\$"/"%"/g
opt,7577600,16.6795%
iphdr,4071424,8.96186%
tcphdr,3637248,8.00617%
event,2637824,5.80628%
data,917504,2.01958%
41.6717%

■接続中のユーザ数(同じユーザ名でもカウントされる)

$ echo "select count(procpid) from pg_stat_activity;" | \
  sudo -u snort psql -U snort -d snort -A -F, -t
3

■ユーザ名をユニークにすると。

$ echo "select usename from pg_stat_activity;" | \
  sudo -u snort psql -U snort -A -F, -t | sort | uniq -c
      3 snort

■テーブルのアクセス頻度
　※アクセスされていないテーブルは除く。

$ echo "select
    relname,
    coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+
    coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,
    coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select,
    coalesce(n_tup_ins,0) as insert,
    coalesce(n_tup_upd,0) as update,
    coalesce(n_tup_del,0) as delete
    from pg_stat_user_tables
    order by total desc;" | \
  sudo -u snort psql -U snort -d snort -A -F, | \
  grep -v ",0,0,0,0,0"
relname,total,select,insert,update,delete
event,316170,304607,11563,0,0
opt,145204,115090,30114,0,0
signature,80962,80962,0,0,0
iphdr,11563,0,11563,0,0
tcphdr,10158,0,10158,0,0
data,7522,5859,1663,0,0
sensor,10,8,0,2,0
schema,4,4,0,0,0
(16 行)

■I/O回数とヒット率の悪い順。
　余程のことが無い限り、「signature」が一番下に来ないとおかしい。
　ヒット率が高ければ(99%)が、既知のルールにヒットしていると考えられる。
　以下は90%を超えたヒット率のテーブルのみ抽出。

$ echo "select * from pg_statio_all_tables;" | \
  sudo -u snort psql -U snort -d snort -A -F, | \
  head -1 | sed s/","/"\n"/g
relid
schemaname
relname
heap_blks_read
heap_blks_hit
idx_blks_read
idx_blks_hit
toast_blks_read
toast_blks_hit
tidx_blks_read
tidx_blks_hit

$ echo "select
    relname,heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,
    (heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ritu
    from pg_statio_all_tables
    where heap_blks_hit >= 1
    and schemaname = 'public' order by ritu;" | \
    sudo -u snort psql -U snort -d snort -A -F, | \
    awk -F\, '{if ($10 > 90) print $0; else if (NR==1) print $0}'
relname,heap_blks_read,heap_blks_hit,idx_blks_read,idx_blks_hit,ritu
event,1109,14359,846,81775,92
signature,5,11580,3,0,99