Re: [syslog-ng]Syslog to oracle

23 Jun 2004

      The simplest way we found was a stored proc and a named pipe.

I modified a script found somewhere on the net so I cant claim credit for
this method but here it is

In syslog-ng.conf

destination d_oracle {pipe("/dev/ora.pipe"template("exec syslog.log (
'$HOST', '$FACILITY', '$PRIORITY', '$SEVERITY', '$TAG', to_date('$MONTH $DAY
$HOUR:$MIN:$SEC', 'mm dd hh24:mi:ss'), '$PROGRAM', substr('$MSG',1,511));\n
COMMIT;\n")template-escape(yes)); };

log { source(s_sys); source(s_udp); destination(d_oracle); };

Import script

#!/bin/sh
nohup /opt/oracle/product/v8172/bin/sqlplus syslog/syslog@DEV9 <
/dev/ora.pipe > /dev/null &

We also use a checker in cron to assure the pipe reading script stays up

#!/bin/bash
export ORACLE_HOME=/opt/oracle/product/v8172
# export ORACLE_BASE=/usr/oracle
export PATH=$ORACLE_HOME/bin:$PATH
SQL=`ps ax|grep "/opt/oracle/product/v8172/bin/sqlplus" | grep -v grep | wc
-l`
if [ $SQL -lt 1 ];then
/usr/local/sbin/import-to-oracle.sh
Fi

Syslog.sql

create or replace package syslog
as
function get_host_id (
 p_host        in varchar2
) return number
;
procedure alert (
 p_syslog_no     in number
,p_msg           in varchar2
);
procedure log (
 p_host        in varchar2
,p_facility    in varchar2
,p_priority    in varchar2
,p_severity    in varchar2
,p_tag         in varchar2
,p_datestamp   in date
,p_program     in varchar2
,p_msg         in varchar2
);
function diskusage (
 p_host        in varchar2
,p_datestamp   in date
) return varchar2
;

end syslog;
/
show error
-------
create or replace package body syslog
as
function get_host_id (
 p_host        in varchar2
) return number
as
cursor l_cur is
  select host_id
  from   hosts
  where  host_ip = p_host
  or     host_name = lower(p_host)
  ;
l_row   l_cur%rowtype;
begin
  open l_cur;
  fetch l_cur into l_row;
  if l_cur%notfound then
     insert into hosts (
       host_ip
      ,host_name
     )
     values (
       decode(translate(p_host,'.0123456789','.'), '...', p_host, null)
      ,decode(translate(p_host,'.0123456789','.'), '...', null,
lower(p_host))
     );
     select hosts_seq.currval
     into   l_row.host_id
     from   dual;
  end if;
  close l_cur;
  return l_row.host_id;
end get_host_id;

procedure alert (
 p_syslog_no     in number
,p_msg           in varchar2
)
as
begin
  insert into alerts (
    syslog_no
   ,sig_no
  )
  select p_syslog_no
        ,sig_no
  from   signatures s
  where  active = '1'
  and    lower(p_msg) like '%'||lower(pattern)||'%'
  and    not exists (
         select null
         from   exceptions
         where  sig_no = s.sig_no
         and    lower(p_msg) like '%'||lower(pattern)||'%'
        )
  ;
end alert;

procedure log (
 p_host        in varchar2
,p_facility    in varchar2
,p_priority    in varchar2
,p_severity    in varchar2
,p_tag         in varchar2
,p_datestamp   in date
,p_program     in varchar2
,p_msg         in varchar2
)
as 
l_syslog_no   number;
begin
  insert into syslogs (
    host_id
   ,facility
   ,priority
   ,severity
   ,tag
   ,datestamp
   ,program
   ,msg
  ) select 
    get_host_id(p_host)
   ,p_facility
   ,p_priority
   ,p_severity
   ,p_tag
   ,p_datestamp
   ,p_program
   ,p_msg
   from dual
   where not exists (
         select null
         from   nosyslogs
         where  active = '1'
         and    lower(p_msg) like '%'||lower(pattern)||'%'
  );
  if sql%rowcount > 0 then
  select syslogs_seq.currval
  into   l_syslog_no
  from   dual
  ;
  alert (l_syslog_no, p_msg);
  end if;
end log;

function diskusage (
 p_host        in varchar2
,p_datestamp   in date
) return varchar2
as
l_usage varchar2(200) default '';
begin
/*
   for r in (
       select disk, usage
       from   v_diskusage
       where  host_name = p_host
       and    datestamp > p_datestamp
   )
*/
   for r in (
select 
       s.host_id
      ,h.host_name
      ,substr(s.msg, instr(s.msg,' ',1,2)+1,
              instr(s.msg,':',1,2) - (instr(s.msg,' ',1,2)+1)) disk
      ,substr(s.msg, instr(s.msg,':',1,2)+1) usage
      ,s.datestamp
from   syslogs s, hosts h
where  s.host_id = h.host_id
and    h.host_name = p_host
and    s.msg like '%DiskUsage%'
and    s.datestamp > p_datestamp
   )
   loop
      l_usage := l_usage ||':'||r.usage;
   end loop;
   if length(l_usage) > 0 then
      return substr(l_usage,2);
   end if;
   return l_usage;
end diskusage;

end syslog;
/
show error

Try this and let us know. Im not a dba so I cant help with the sql script
but this worked in a production environment of some 30 odd mixed linux
windows and cisco so it should work well for you

Thanks

Brett Stevens

On 22/6/04 23:36, "Marc Brown" <rrgroups@yahoo.com> wrote:
...
Hi ,
can we have syslog messages logged to oracle database
. If so , please send me some info and references on
that.
- marc
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!
http://promotions.yahoo.com/new_mail
_______________________________________________
syslog-ng maillist  -  syslog-ng@lists.balabit.hu
https://lists.balabit.hu/mailman/listinfo/syslog-ng
Frequently asked questions at http://www.campin.net/syslog-ng/faq.html