Oracle syslog relayed through syslog-ng - how to get a hostname/IP included?

[RSAAdmin]

Has anyone gotten Oracle Audit logging to work behind a syslog-ng relay?

We've got a syslog-ng relay in our second data center that we're using to relay messages to enVision.  It is properly configured per the RSA docs and is sending logs for more than a dozen different systems.

Enter Oracle.

An Oracle system behind the syslog-ng relay is incorrectly logging Oracle message to the relay's IP address.  Oracle doesn't put the hostname or IP in it's log messages, so the only source enVision can identify is the relay itself.  We can see this in a capture of the raw syslog traffic at the enVision server. 10.1.X.Y is the syslog relay, 10.1.A.B is enVision:

12134    50.423856    10.1.X.Y    10.1.A.B    Syslog    LOCAL0.INFO: Jun 10 07:38:39 Oracle Audit[5582888]: LENGTH: "155" SESSIONID: "14005466" ENTRYID: "1" ACTION: "101" RETURNCODE: "0" LOGOFF$PREAD: "0" LOGOFF$LREAD: "69" LOGOFF$LWRITE: "0" LOGOFF$DEAD: "0" SESSIONCPU: "0"\n

For all practical purposes, the hostname appears to be "Oracle", based on it's position after the timestamp.

Oracle's docs aren't very helpful, there is little configuration you can do with the AUDIT commands.

We don't want to configure the Oracle boxes to log directly to enVision, bypassing the relay.  Any other solutions?

[RSAAdmin]

Just curious, why even have the syslog relay?  Seems like it is an additional place for a point of failure and probably not all the efficient....anyway I digress  

Your logs should look something like this:

May 21 18:39:46 test01 Oracle Audit[10760]: LENGTH : '718' ACTION :[586

Check out this blog and see if it helps:

http://sprocketdba.wordpress.com/2010/05/07/how-to-send-the-oracle-audit-log-to-syslog/

[RSAAdmin]

The syslog relay is in another location, across our WAN, and before enVision it was a local collection point. We changed it into a relay to keep the number of data streams going across the WAN to a minimum.

Our Oracle syslog messages are missing a hostname for some reason. Thanks for the blog reference, I'll have the DBAs check the v$instance table and see what's returned for the HOST_NAME. I think that is all that's messing us up.

[RSAAdmin]

In Oracle, the settings all make sense, the proper hostname is seen in the v$instance table.

We found that Oracle messages are also being written to a file (by syslogd) on the local box, and those messages do have a hostname:

  Jun 13 08:00:07 MYHOST local0:info Oracle Audit[1953966]: LENGTH: "155" SESSIONID:...

What is odd is that the AIX messages relayed from the same host are handled just fine by enVision. They have the unqualified hostname in them, which we resolve via the hostnames.ini setup per the 'Supporting syslog Relay' documentation.

Our syslog-ng.conf options are matching what is required in the enVision docs:

      use_dns(no);
      use_fqdn(no);
      keep_hostname(yes);
      chain_hostnames(no);

Why the Oracle messages aren't making it through the relay with a hostname is the mystery at this point.

[RSAAdmin]

OK..so if you are goint to relay those messages and they contain hostname instead of IP address, you have to enter the hostnames into the following file:

nic\csd\config\collectors\hostname.ini on the A-SRV or NAS.

there is a good page in the enVision Online help that explains it all under Support for Syslog Relays.

FWIW, I did a study a couple years back and actually saw no siginificant difference between using a syslog relay over a WAN and having the individual servers send syslog over the WAN as far as performance and bandwidth utilization were concerned.

Paul

[RSAAdmin]

Thanks Paul, point taken on the aggregation via a relay versus individual streams...it took some talking to get the network folks let us push the data over the WAN in the first place (versus building infrastructure in both locations), so we have to prove ourselves before we ask for changes

As noted, the oddest thing is that Unix (AIX) messages off the same Oracle host make it through the syslog-ng relay to enVision without any issues -- they are recorded correctly to the original host.  It is just the Oracle Audit traffic that enVision can't grok due to the missing hostname in the syslog message.  When we write the Oracle audit logs to a file on the Oracle host, the hostname is there.

We're checking other syslogd options and looking at network traces to see if the hostname is getting 'lost' somewhere, or if it's just never populated.  If the latter, then we'll have to point Oracle syslog directly to enVision.

[RSAAdmin]

Remember that if the hostname and not the IP gets relayed by syslog-ng, you need to add the hostname to the hostname.ini file to get Envision to work properly...Envision won't do a reverse lookup to get the IP to store the data, because that would probably kill the collector with all of the DNS requests.

[RSAAdmin]

It gets weirder all the time.  One of the RSA SE's said our syslog-ng configuration must have a 'template' directive to make sure the messages come into enVision in the right format.  He said to use:

     template ("<$PRI> $DATE $HOST $MESSAGE\n")

When we tried this it made no difference -- messages getting to enVision still didn't have the correct original source Hostname in them.

We changed the template to

      template ("<$PRI> $DATE $HOST_FROM $MSG\n")

and now we DO get the correct originating source Host IP. enVision can assign the messages to the proper source.  BUT the message itself is being modified by syslog-ng so that enVision doesn't understand it's an Oracle message.

With $HOST:

     Jun 17 06:10:57 Oracle Audit[4743328]:

With $HOST_FROM:

     Jun 17 06:17:46 10.1.xx.yy Audit[3616850]

See how the word "Oracle" was removed?  The host IP has taken it's place...but we need that word for enVision to understand the messages....

One step forward, one step back. We're on an older version of syslog-ng that might make this harder to fix.  I found a reference to maybe using syslog-ng's "bad_hostname()" as a workaround, but we'll have to investigate that some more.

[RSAAdmin]

We found our fix -- the addition of the syslog-ng option

      bad_hostname("^Oracle$");

The Oracle messages now make it to enVision intact, and enVision auto-senses the device-type as "Oracle".  The changes do not appear to negatively impact any other device sending logs to enVision through the relay.

So the final solution to the whole "How to relay Oracle syslog messages through syslog-ng" was:

1) getting a properly formatted message through the use of a template()

2) using $HOST_FROM in the template to get the proper source IP

3) using the bad_hostname() option to prevent stripping part of the Oracle message content.

Hopefully this help someone in the future!