Regular expression to upload files from oracle audit file

[pstamati]

Hi there,
Firts of all I don´t know anything about regular expressions. Bad for me, I know, but I need to deal with txt exported logs from Oracle and I don´t figure out how to make a regular expression to upload data to splunk.
Log files are like this:

19/05/11 09:28:51|43|ALTER USER||USERNAMEZZ|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:28:51|114|GRANT ROLE|USERNAMEZZ|DWENGAGE_SELECT_EMERG|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:28:51|114|GRANT ROLE|USERNAMEZZ|ENGAGE49_SELECT_EMERG|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:53:20|43|ALTER USER||USERNAME|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:53:20|114|GRANT ROLE|USERNAM|DWENGAGE_SELECT_EMERG|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:53:20|114|GRANT ROLE|USERNAM|ENGAGE49_SELECT_EMERG|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:57:57|114|GRANT ROLE|USERNAMEE|START1_SELECT_EMERG|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 09:57:58|43|ALTER USER||USERNAMEE|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 10:38:46|43|ALTER USER||USERNAMER|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 10:38:46|114|GRANT ROLE|USERNAMER|ENGAGE49_SELECT_EMERG|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 10:41:11|43|ALTER USER||USERNAM|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 11:23:47|43|ALTER USER||USERNAMEZZZ|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 11:35:39|43|ALTER USER||USERNAMEZZZ|NAPELLIDO|apellido-nombre|MACHIHE-301
19/05/11 12:55:46|43|ALTER USER||USERNAMEZZZ|MKIYOZAW|apellido-nombre|MACHIHE-201
19/05/11 12:56:07|43|ALTER USER||USERNAMEZZZ|MKIYOZAW|apellido-nombre|MACHIHE-201
19/05/11 13:06:54|49|ALTER SYSTEM|||USERNAM|apellido-nombre|MACHIHE-201

Seems to be easy, since it is splitted by pipe char, but i cannot solve this with filed extract assistant.

Could you help me with this?
Many thanks in advance.

[bvamos]

I have uploaded a new App (Splunk for Oracle Audit Trail) what can parse and analyze Oracle Audit Trails sent via syslog. This App is not yet visible but hopefully will be soon. You can use that App to analyze your Oracle Audit Trail.
A new feature would be the ability of parsing your export files. You just have to ask for it 🙂

[bvamos]

Splunk for Oracle Audit Trails is available. Download from: http://splunk-base.splunk.com/apps/36943/oracle-audit-trail

[pstamati]

Is there anything else to do appart from this, because it doesn´t work.

I exported logs from Oracle, running scheduled scripts that obtain Oracle Audit events exporting to files. I upload this files using Files & Directories data inputs.
regards

[joshd]

I'm not sure which method you used for field extraction and I'm not sure what exact of the fields represents but as long as there is consistency in the field layout and delimitation then what you can basically do is when splunk indexes the file(s) configure a custom sourcetype for the files, lets say oracle_logs ... then in transforms.conf write a transform like so:

[oracle_exp_logs]
DELIMS = "|"
FIELDS = "date","code","statement","username","field1","field2","field3","field4"

And then in your props.conf apply the transform to the sourcetype associated with the indexed files..

[oracle_logs]
REPORT-oracle = oracle_exp_logs

...What this basically will do is use "|" as the delimiter in the file and break the fields apart based on that. It will then associate the broken down fields with the field names specified by "FIELDS=" in your transform.