topic Re: Oracle Audit Trail Field Extraction in Splunk Search

Oracle Audit Trail Field Extraction

JSapienza — Wed, 09 Mar 2011 00:52:46 GMT

I am trying to extract the fields from an Oracle 10g Audit trail. Below is a sample of the raw log :

Tue Feb 15 10:14:44 2011
SESSIONID: "21288516" ENTRYID: "5158831" STATEMENT: "3585703" USERID: "TEST" ACTION: "6" RETURNCODE: "0" OBJ$CREATOR: "TEST" OBJ$NAME: "SR_JOB" OS$USERID: "auditwks"

I have tried this in my transform.cfg :

REGEX = (?:(["']|(")).*?(?<!\\)(?(1)\1|(?(2)"))

FORMAT = $1::$3

But it seem to not be working and im not all that good with regex's. Any assistance is greatly appreciated.

Re: Oracle Audit Trail Field Extraction

gkanapathy — Wed, 09 Mar 2011 01:30:27 GMT

Looks to me like you just copied the REGEX from http://answers.splunk.com/questions/12157/oracle-audit-log-regex but that won't work because the format of the data there is completely different from the one you have here.

You might just be able to use:

DELIMS = " ", ":"

instead of a REGEX with this data format, but if you really wanted you could probably use:

REGEX = (\w+):\s+\"([^\"]*)\"
FORMAT = $1::$2

Re: Oracle Audit Trail Field Extraction

JSapienza — Wed, 09 Mar 2011 01:46:46 GMT

Yea I was just looking at that. I copied the wrong REGEX when I was reading that post. oops.. I edited my original post but I will try your suggestion. Thanks!

Re: Oracle Audit Trail Field Extraction

JSapienza — Wed, 09 Mar 2011 03:05:34 GMT

OK , I tried both the DELIMS and the modified REGEX and the fields still were not extracted. I am getting new events but the fields are not showing .. its the darnedest thing. Any ideas ?

Re: Oracle Audit Trail Field Extraction

JSapienza — Wed, 09 Mar 2011 22:15:15 GMT

Couldn't get the extraction to work at index time for some reason. But, I resolved this issue with a few search time extractions.
Example:
(?im)USERID:\s\"(?P.+?)\"

Re: Oracle Audit Trail Field Extraction

gkanapathy — Fri, 11 Mar 2011 03:55:44 GMT

Search time is preferred most of the time, and definitely better in this case. Index time is both slower and less flexible.

Re: Oracle Audit Trail Field Extraction

bvamos — Mon, 28 Sep 2020 10:14:12 GMT

You can extract the key/value pair with one extraction:
(?i)(?<_KEY_1>\S+):\s+"(?<_VAL_1>[^"]+)"
This will result these fields and values from you log:

SESSIONID=21288516
ENTRYID=5158831
STATEMENT=3585703
USERID=TEST
ACTION=6
RETURNCODE=0
OBJ_CREATOR=TEST
OBJ_NAME=SR_JOB
OS_USERID=auditwks

Note that Splunk is smart enough to replace the $ char in the field name.