Logging to syslog

[barsema]

It would be great if we could log directly to (a remote) syslog.

[martindsouza]

By remote, do you mean on the filesystem or to a 3rd party site (i.e. remove via https etc?)

What sort of output would you want to see? JSON?

[barsema]

We are consolidating all our logging and we are using syslog. So we have a central immutable store for logging.

http://en.wikipedia.org/wiki/Syslog

logging to the local syslog would work, because its easy to have that forwarded tot the remote syslog. But best would be to be able to use remote syslog directly.

It could work like the syslogappender of log4j

[martindsouza]

Fair enough. Do you have some sample code to work off to do this?

On a side note, if we aren't able to integrate it we can always expand the plugins to all the different logging methods so you can integrate syslog logging on your specific system.

[barsema]

I found this python implementation, looks pretty straight forward... only thing im not sure about is sending UDP from oracle but i doubt that is a problem ;-)

https://liftoff.github.io/GateOne/_modules/remote_syslog.html#syslog

[martindsouza]

Can you please put together a proof of concept on it?

[barsema]

Ok apparently PL/SQL does not do UDP natively, but using Java it can be done.

I've put this together something that works in my setup,
If you want to test it yourself , remember to setup the syslog server to allow remote logging

For reference these are the facilities and levels supported by syslog:

FACILITY = {
    'kern': 0, 'user': 1, 'mail': 2, 'daemon': 3,
    'auth': 4, 'syslog': 5, 'lpr': 6, 'news': 7,
    'uucp': 8, 'cron': 9, 'authpriv': 10, 'ftp': 11,
    'local0': 16, 'local1': 17, 'local2': 18, 'local3': 19,
    'local4': 20, 'local5': 21, 'local6': 22, 'local7': 23,
}

LEVEL = {
    'emerg': 0, 'alert':1, 'crit': 2, 'err': 3,
    'warning': 4, 'notice': 5, 'info': 6, 'debug': 7
}

This is my POC

create or replace and compile java source named "Syslog" as

import java.net.DatagramSocket;
import java.net.DatagramPacket;
import java.net.InetAddress;

public class Syslog {

    public static void send (String host,int port, int facility, int level,String message)
        throws java.net.SocketException, java.io.IOException {

        int pri = level + facility*8;
        String data = String.format("<%d> %s",pri, message);
        DatagramSocket s = new DatagramSocket();
        DatagramPacket p = new DatagramPacket(new byte[data.length()], data.length(), InetAddress.getByName(host), port);

        p.setData(data.getBytes());
        s.send(p);

    }

};

/

create or replace
    procedure syslog( p_host in varchar2,
                      p_port in number,
                      p_facility number, 
                      p_level number, 
                      p_message varchar2)
    as language java
    name 'Syslog.send( java.lang.String,int,int,int,java.lang.String )';
    /

-- to test    
begin

   syslog('localhost'
          ,514
          ,3 --deamon
          ,1 -- alert
          ,'Test 123');
end;
/

[barsema]

I created a plugin that uses the above mechanism to log to a remote syslog server, i made a pull request so you can take a look at it.

[martindsouza]

@barsema This is great! I haven't accepted your pull request yet. I want to think of some standards for 3rd party plugins. This will mainly involve preference names and function/procedure names.

As part of 3.1 we launched the set_cust_pref to store preferences with the prefix CUST_. I don't think 3rd party plugins should overload that option as CUST_ prefs should have no conflict with anything Logger sets in the logger_prefs table.

Odds are we may choose something like PLUGIN_... but don't wan to conflict with native Logger plugin settings.

I've schedule this for the 3.2 release however I will probably rename the set_cust_pref and del_cust_pref to just set_pref and del_pref with a p_pref_type parameter which will handle CUST or <3rd party plugin prefix> in the 3.1 release (#127)

[barsema]

That's sound terrific.