OSSIM下添加oracle listener.log

原创

Richie0524 2010-08-30 16:35:06 ©著作权

文章标签 oracle 职场休闲 ossim listener.log 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者Richie0524的原创作品，请联系作者获取转载授权，否则将追究法律责任

这种方法不仅可以用在oracle日志的收集上，对于其他软件的时间日志，访问日志，都可以收集，关键是要将日志都分好类，用正则表达式都能匹配到，这样数据才是完整的

oracle的listener.log的官方文档 http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/troublestng.htm#i423432 在这里可以查看，可以看到一般分为三类，所以我们正则表达式也用3个写；

1，首先收集oracle listener.log ，在ossim服务器上 tail -f /var/log/oracle.log，查看日志，看到日志已经过来；

日志怎么发到agent，用epilog的这些工具的方法这里就不再赘述，可以看我老大的空间http://hi.baidu.com/jiekech00

跟着他的空间走，你可以很好的学习ossim系统

2，取日志开始写正则表达式

Aug 30 11:41:59 TOP-SERV-oracle GenericLog 0 30-8月 -2010 11:41:58 * 12502
Aug 30 11:41:59 TOP-SERV-oracle GenericLog 0 TNS-12502: TNS: 监听程序没有从客户机收到 CONNECT_DATA

Aug 30 11:42:02 TOP-SERV-oracle GenericLog 0 30-8月 -2010 11:42:01 * (CONNECT_DATA=(SID=zgdx)(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.81)(PORT=3273)) * establish * zgdx * 0
Aug 30 11:42:03 TOP-SERV-oracle GenericLog 0 30-8月 -2010 11:42:03 * service_update * zgdx * 0

Aug 30 11:43:37 TOP-SERV-oracle GenericLog 0 30-8月 -2010 11:43:37 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=TOP-SERV-oracle)(PORT=1521)))(VERSION=169870336)) * status * 0
Aug 30 12:30:10 TOP-SERV-oracle GenericLog 0 30-8月 -2010 12:30:10 * (CONNECT_DATA=(SERVICE_NAME=zgd)(CID=(PROGRAM=D:\downloads\instantclient\sqlplus.exe)(HOST=PPL)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.230)(PORT=14367)) * establish * zgd * 12514

分析日志发现需要用3个正则表达式才能全部匹配，写出正则表达式如下：

第一个

(?P<sysdate>\w+\s\d{1,2}\s\d\d:\d\d:\d\d)\s+(?P<hostname>\S+)\s+\S+\s+\d+\s+(?P<ora_date>\d{1,2}-\S+\s-\d{4}\s\d\d:\d\d:\d\d)\s\*\s$CONNECT_DATA=(\(SID=\w+$)?($SERVICE_NAME=[^$]+\))?$CI
D=\(PROGRAM=(?P<program>\S+)?$$HOST=(?P<client>[^$]+)\)$USER=(?P<user>[^$]+)?\){2,3}($SERVICE_NAME=[^$]+\){1,2})?\s\*\s$ADDRESS=(\(PROTOCOL=(?P<proto>[^$]+)\))?$HOST=(?P<host1>[^$]+)\)$
PORT=(?P<sport>\d{1,5})${2,3}\s\*\s(?P<stat>\S+)\s\*\s(?P<ora_sid>\S+)\s\*\s(?P<re_code>\d+)

第二个

(?P<sysdate>\w+\s\d{1,2}\s\d\d:\d\d:\d\d)\s+(?P<hostname>\S+)\s+\S+\s+\d+\s+(?P<ora_date>\d{1,2}-\S+\s-\d{4}\s\d\d:\d\d:\d\d)\s\*\s$CONNECT_DATA=\(CID=\(PROGRAM=(?P<program>[^$]+)?\)$HOST
=([^$]+)?\)$USER=(?P<user>[^$]+)\)\)$COMMAND=(?P<stat>[^$]+)\)$ARGUMENTS=(\d+)$$SERVICE=(\(DESCRIPTION=)?\(ADDRESS=\(PROTOCOL=(?P<proto>[^$]+)\)$HOST=([^$]+)\)$PORT=(?P<dport>\d{1,5})$
{3,4}$VERSION=\d+$\){1,2}\s\*\s(\S+)\s\*\s(\d+)

第三个

(?P<sysdate>\w+\s\d{1,2}\s\d\d:\d\d:\d\d)\s+(?P<hostname>\S+)\s+\S+\s+\d+\s+(?P<ora_date>\d{1,2}-\S+\s-\d{4}\s\d\d:\d\d:\d\d)\s\*\s(?P<event>service_\w+)\s\*\s(?P<ins_name>\S+)\s\*\s(?P<ret_
code>\d+)

3，在ossim服务器上 /etc/ossim/agent/plugins/ 下新建一个文件oracle.cfg

添加内容如下：

;; oracle listener.log
;; plugin_id: 9004
;;
;; MODIFICATION BY: Jiekechoo 2010/08/30
;;
;; $Id: oracle.cfg,v 1.7 2010/08/30 Exp $
;;

[DEFAULT]
plugin_id=9004

[config]
type=detector
enable=yes

source=log
location=/var/log/oracle.log
create_file=true

process=
start=no
stop=no
startup=
shutdown=

[translation]

[oracle-audit-trail-1]
event_type=event
regexp=(?P<sysdate>\w+\s\d{1,2}\s\d\d:\d\d:\d\d)\s+(?P<hostname>\S+)\s+\S+\s+\d+\s+(?P<ora_date>\d{1,2}-\S+\s-\d{4}\s\d\d:\d\d:\d\d)\s\*\s$CONNECT_DATA=(\(SID=\w+$)?($SERVICE_NAME=[^$]+\))?$CI
D=\(PROGRAM=(?P<program>\S+)?$$HOST=(?P<client>[^$]+)\)$USER=(?P<user>[^$]+)?\){2,3}($SERVICE_NAME=[^$]+\){1,2})?\s\*\s$ADDRESS=(\(PROTOCOL=(?P<proto>[^$]+)\))?$HOST=(?P<host1>[^$]+)\)$
PORT=(?P<sport>\d{1,5})${2,3}\s\*\s(?P<stat>\S+)\s\*\s(?P<ora_sid>\S+)\s\*\s(?P<re_code>\d+)

date={normalize_date($sysdate)}
plugin_sid=1
protocol={$proto}
src_ip={$host1}
src_port={$sport}
dst_ip={resolv($hostname)}
dst_port=1521
username={$user}
userdata1={$ora_date}
userdata2={$client}
userdata3={$ora_sid}
userdata4={$re_code}
userdata5={$stat}
userdata6={$program}

[oracle-audit-trail-2]
event_type=event
regexp=(?P<sysdate>\w+\s\d{1,2}\s\d\d:\d\d:\d\d)\s+(?P<hostname>\S+)\s+\S+\s+\d+\s+(?P<ora_date>\d{1,2}-\S+\s-\d{4}\s\d\d:\d\d:\d\d)\s\*\s$CONNECT_DATA=\(CID=\(PROGRAM=(?P<program>[^$]+)?\)$HOST
=([^$]+)?\)$USER=(?P<user>[^$]+)\)\)$COMMAND=(?P<stat>[^$]+)\)$ARGUMENTS=(\d+)$$SERVICE=(\(DESCRIPTION=)?\(ADDRESS=\(PROTOCOL=(?P<proto>[^$]+)\)$HOST=([^$]+)\)$PORT=(?P<dport>\d{1,5})$
{3,4}$VERSION=\d+$\){1,2}\s\*\s(\S+)\s\*\s(\d+)

date={normalize_date($sysdate)}
plugin_sid=2
protocol={$proto}
src_ip={resolv($host1)}
dst_ip={resolv($hostname)}
dst_port={$dport}
username={$user}
userdata1={$ora_date}
userdata4={$14}
userdata5={$stat}
userdata6={$program}

[oracle-registration-event]
event_type=event
regexp=(?P<sysdate>\w+\s\d{1,2}\s\d\d:\d\d:\d\d)\s+(?P<hostname>\S+)\s+\S+\s+\d+\s+(?P<ora_date>\d{1,2}-\S+\s-\d{4}\s\d\d:\d\d:\d\d)\s\*\s(?P<event>service_\w+)\s\*\s(?P<ins_name>\S+)\s\*\s(?P<ret_
code>\d+)

date={normalize_date($sysdate)}
plugin_sid=3
dst_ip={resolv($hostname)}
src_ip={resolv($hostname)}
userdata1={$ora_date}
userdata2={$event}
userdata3={$ins_name}
userdata4={$ret_code}

4，添加引用oracle.cfg文件在/etc/ossim/agent/下打开 config.cfg

[plugins]

oracle=/etc/ossim/agent/plugins/oracle.cfg

5，创建文件在/usr/share/doc/ossim-mysql/contrib/plugins/ 下创建oracle.sql 内容如下：

--oracle
-- plugin_id: 9004
DELETE FROM plugin WHERE id = "9004";
DELETE FROM plugin_sid where plugin_id = "9004";

INSERT INTO plugin (id, type, name, description) VALUES (9004, 1, 'oracle', 'Oracle listener.log');

INSERT INTO plugin_sid (plugin_id, sid, category_id, class_id, name, priority, reliability) VALUES (9004, 1, NULL, NULL, 'Listener Log Audit Trail Information' , 1, 1);

INSERT INTO plugin_sid (plugin_id, sid, category_id, class_id, name, priority, reliability) VALUES (9004, 2, NULL, NULL, 'Listener Log Audit Trail Information' , 1, 1);

INSERT INTO plugin_sid (plugin_id, sid, category_id, class_id, name, priority, reliability) VALUES (9004, 3, NULL, NULL, 'Oracle Service Registration Event Information' , 1, 1);

保存退出；

5,在/usr/share/doc/ossim-mysql/contrib/plugins/ 目录下

执行 ossim-db<oracle.sql

6,重启服务 /etc/init.d/ossim-agent restart

/etc/init.d/ossim-server restart

7,查看agent日志 tail -f /var/log/ossim/agent.log

看是否有oracle的信息

如果没有可以用 tail -f /var/log/ossim/server.log查看哪里有问题，再做相应的修改

8，最后可以在 web页面上可以看到信息