Oracle audit logs

[chud]

Hello. We have a requirement that we need to store Oracle audit logs for a period of one year.
I am wondering if Nagios Log Server is the best tool for this job.

For example, how do we retrieve audit logs from say, four months ago?

Does Nagios store the individual files, or does it just store the entries in a database, and you would go to NLS and query by some value such as date to retrieve the entries?

Any recommendations for this type of requirement?

[mbellerue]

Nagios Log Server may be a good fit for this. It's based on Elasticsearch, so the log files themselves aren't kept. Just the entries. The way it works, at least for Linux/Unix clients, is a configuration for rsyslog where log entries are sent to Log Server as they're generated. Log Server then parses the entries, and stores them in Elasticsearch.

Keeping logs for a 1 year period may require a chunk of storage, depending on how many servers you are going to add to Log Server. You might want to consider external storage mounted via iSCSI or NFS, as upgrading and expanding local storage could be cumbersome.

Feel free to download and give it a test drive.
https://www.nagios.com/products/nagios-log-server/

[chud]

Thanks for your reply.
I am testing NLS as a solution, and may need some query/filter help on this.

For my test, I sent a single audit log, like so:

bash setup-linux.sh -s logservername -p 5544 -f /u01/app/oracle/admin/PROD/adump/reallylongfilename.aud -t oracleauditlog

Note that I sent a specific file, instead of doing *.aud, and I gave the file a tag ( -t ) of "oracleauditlog" in the command above.

Then in NLS, I look up that host, and type "oracleauditlog" in the query box, and search.

Below that, on the "Events over time" graph, I see a yellow bar on the graph and if I mouse over that bar, it indicates the time and other info about when I sent the audit log file over. So that was successful.

However, below that under "All Events" section, I do not see any of the entries from the audit log, or any reference to it at all. All I see are syslog entries that look like they come from /var/log/messages - my audit log's entries are nowhere to be seen.

On the left hand side, in the "Type to filter..." box I thought maybe I needed to enter "tag" and filter by that, however there is no option for filtering by tag (just things like timestamp, host, message, type, etc).

So how would I filter/query to see the entries from this audit log that I sent over?

[mbellerue]

If you go to Reports you should get a list of unique hosts that are sending data to LS. Select the host that should be sending the audit logs. That should bring you to a search page with host:"oraclhostname" in the query bar. Add to that type:"oracleauditlog" and see if that gets you your data.

If it doesn't, would you be able to send me the contents of the /etc/rsyslog.d/ directory from your Oracle machine? Also if you can send an example log message from the audit log, that would be awesome. You can send it in a PM if you like.

[chud]

...see if that gets you your data.

Thanks, but unfortunately it didn't.

If it doesn't, would you be able to send me the contents of the /etc/rsyslog.d/ directory from your Oracle machine? Also if you can send an example log message from the audit log, that would be awesome. You can send it in a PM if you like.

PM sent.

[mbellerue]

Excellent, thank you! Based on the log entry you send, I think we're running into an issue of multiline log entries. Can you take a look at this KB article, and see if you can apply this to your Log Server instance.
https://support.nagios.com/kb/article/n ... g-727.html

It may be that a single audit log entry is actually generating a bunch of small log entries. You might be able to confirm this by searching for Oracle Database 12c

[chud]

Thank you, that knowledge base article is helpful.

One question, the pattern that I will be searching for in an Oracle audit log seems to be that each entry in the log starts with a line like this:

Mon Feb 17 23:42:04 2020 -06:00

Each entry seems to be 13 lines, including that first line which begins with the day/date.

So the pattern I need to search for is a line that starts with one of the following:

Sun
Mon
Tue
Wed
Thu
Fri
Sat

So I'm thinking the custom input would be something like this:

Code: Select all

tcp {
    codec => multiline {
        pattern => '^Sun|Mon|Tue|Wed|Thu|Fri|Sat'
        negate => true
        what => previous
    }
    port => 6677
    type => 'syslog_multiline'
}

Does that look about right?

[mbellerue]

That looks about right. But you should definitely go through your existing logs and make sure nothing else is submitting the first 3 letters of the day. Unless you want those rolled into multiline as well.

[chud]

That looks about right. But you should definitely go through your existing logs and make sure nothing else is submitting the first 3 letters of the day. Unless you want those rolled into multiline as well.

I thought it would only apply this multiline to the Oracle audit log, or whatever log I tagged multiline in the server config.
Are you saying it will apply this to other logs as well?

[mbellerue]

Oh, no my apologies. I just wanted to make sure that you don't have anything else that logs with the same starting string. But you are correct, ultimately it won't matter unless you have that application log to the same input. My mistake.