How to customize the Oracle Audit Trail app to apply custom sourcetype to data from forwarder instead of syslog input?

[doreno]

Hi there,

I am using Splunk 4.3.3 build 128297 configured with 1 search head and 2 indexers. I am hoping to use the Splunk “Oracle Audit Trail” app, http://splunk-base.splunk.com/apps/36943/oracle-audit-trail, to get the .aud files from a Oracle 11.2 database server into Splunk.

The app was written to receive a TCP connection sent via syslog. The sourcetype “oracle_syslog” is generated at index time based on the format of events sent via standard syslog input. However, I would much prefer to install the forwarder on the server and monitor the audit files directory while using the predefined field extractions, field value lookups, form searches, charts and reports defined in the Oracle app.

Unfortunately this app is not supported by Splunk and the Authors documentation doesn't explore this particular scenario. I tried adding sourcetype=oracle_syslog to the monitor stanza but that didn't work.

Id like to know how to get splunk to apply the oracle_syslog sourcetype to the monitor I've created in the inputs.conf instead of through the default TCP:9996 input.

Here is the inputs.conf on the Oracle server:

[monitor:///u00/oracle/admin/webstage/adump]
index = oracleaudit
disabled = false
sourcetype = oracle_syslog

To add to the confusion, upon simply installing the app on the search head I immediately got the following error in the Splunk web UI:

“The lookup table 'oracle_actions' does not exist. It is referenced by configuration 'oracle_syslog'”

I have checked the oracle_actions file and it does exist. Searching Google I found that others have also run into this error, but I did not find any solutions. I installed the app on the indexers as well as the forwarder to see if that might help but the error persists.

Here are the default props and transforms for the Oracle Audit app.

[root@splunk00.ussl.uhs default]# cat props.conf

[syslog]
TRANSFORMS-sourcetype = sourcetype_to_oracle_syslog

[oracle_syslog]
EXTRACT-oracle_key_value_pair = (?i)(?<_KEY_1>\S+):\[\d+\]\s+"(?<_VAL_1>[^"]+)"
LOOKUP-action = oracle_actions ACTION OUTPUT oracle_actionname, oracle_eventtype, oracle_eventclass

[oracle_key_value-pair]


[root@splunk00.ussl.uhs default]# cat transforms.conf 

[sourcetype_to_oracle_syslog]
SOURCE_KEY = _raw
DEST_KEY = MetaData:Sourcetype
REGEX = Audit\[\d+\]: LENGTH: "\d+" SESSIONID:\[\d+\] "\d+"
FORMAT = sourcetype::oracle_syslog

[oracle_actions]
filename = oracle_actions.csv
max_matches = 1
min_matches = 1

Any help would be very much appreciated.

Thanks!
Dan

[eegilbert]

Has anyone made any progress on this? Specifically the error message. I only seem to get it when I'm not in the oracleaudit application which leads me to think that the file is being globally referenced some how?

[MarioM · Accepted solution]

For the sourcetype rename you need to change the regex in transforms.conf to be this:

[sourcetype_to_oracle_syslog]
SOURCE_KEY = _raw
DEST_KEY = MetaData:Sourcetype
REGEX = Audit\[\d+\]: LENGTH\s+:\s+\'\d+\'\s+ACTION\s+:\[\d+\]\s+
FORMAT = sourcetype::oracle_syslog

For the field extraction and lookup it's still not matching because the lookup file common field mapping is expecting the ACTION field to be numerical as per csv file.

And in your data the number next to action doesnot not match lookup description.

Then you either need to get the right action number in the log or modify the lookup file to match another field which for you represent the action id.

in your log ACTION :[44] 'BEGIN access_tracking_income_max; END; but for 44 in lookup file i have

`44,COMMIT,,`

in your log ACTION :[6] 'COMMIT' ...' but for 6 in lookup file i have

6,UPDATE,User action,Data

[doreno]

Mario,

Yes, you were right. Thank you!

After speaking with our DBA's, I realized that they accidentally sent me a link to the wrong data in email and thats why the expected fields were missing. The data is actually being written to /var/log/messages! Which makes things much easier! Or at least I thought so...

But when testing this myself I found that it doesnt matter whether I use sourcetype = syslog or sourcetype = oracle_syslog, the results are still indexed without the field extractions defined in the Oracle Audit Trail app.

If I add sourcetype = syslog to the inputs.conf monitor stanza, shouldnt it recognize the data via the Oracle Audit Trails props.conf and feed it into the transforms.conf and automatically update the sourcetype and do the field extraction for me?

I am very curious whether you received the same error I did when you installed the Oracle Audit Trail app in Splunk?

“The lookup table 'oracle_actions' does not exist. It is referenced by configuration 'oracle_syslog'”

I get that error after installing the app on both my test and production instances of Splunk, even before importing the data. I suspect that once I get that error message resolved, I should be able to figure out how to get the fields extracted and then the rest of the app should work fine.

Here is a sample of the correct data... Any ideas?

Sep 19 10:14:39 stage01 ora Audit[8161]: LENGTH : '443' ACTION :[289] 'SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,'integer',4,'file',5,'number',        6,'big integer', 'unknown') TYPE,DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER(:NMBIND_SHOW_OBJ) ORDER BY NAME_COL_PLUS_SHOW_PARAM,ROWNUM' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:16:06 stage01 ora Audit[8313]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:16:06 stage01 ora Audit[8313]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:16:06 stage01 ora Audit[8313]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/2' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:33:15 stage01 ora Audit[9287]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:33:15 stage01 ora Audit[9287]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:33:15 stage01 ora Audit[9287]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:33:45 stage01 ora Audit[9287]: LENGTH : '189' ACTION :[36] 'select instance_name from v$instance' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:33:57 stage01 ora Audit[9287]: LENGTH : '197' ACTION :[44] 'BEGIN access_tracking_income_max; END; 
Sep 19 10:37:59 stage01 ora Audit[9513]: LENGTH : '159' ACTION :[7] 'CONNECT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:37:59 stage01 ora Audit[9513]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:37:59 stage01 ora Audit[9513]: LENGTH : '158' ACTION :[6] 'COMMIT' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:38:09 stage01 ora Audit[9513]: LENGTH : '189' ACTION :[36] 'select instance_name from v$instance' DATABASE USER:[1] '/' PRIVILEGE :[6] 'SYSDBA' CLIENT USER:[6] 'ora' CLIENT TERMINAL:[5] 'pts/3' STATUS:[1] '0' DBID:[10] '9234521554' 
Sep 19 10:38:38 stage01 ora Audit[9513]: LENGTH : '181' ACTION :[28] 'BEGIN .update_; END; 

[MarioM]

i tested and it seems this app will only work with oracle syslog message has it expecting the RETURNCODE field and SESSIONID which doesnot exist in your data.

Thus the fields extractions not working and the lookup error...

Then you will have to built your own searches & dashboards...

you can use this conf to get fields extraction:

props.conf

[<your_sourcetype>]
SHOULD_LINEMERGE = False
LINE_BREAKER = ([\r\n]+)\w{3}\s+\w{3}\s+\d+\s+\d+\:\d+\:\d+\s+\d{4}
KV_MODE = None
REPORT-ACTION_for_oracle = ACTION_for_oracle
REPORT-CLIENT_TERMINAL_for_oracle = CLIENT_TERMINAL_for_oracle
REPORT-CLIENT_USER_for_oracle = CLIENT_USER_for_oracle
REPORT-DATABASE_USER_for_oracle = DATABASE_USER_for_oracle
REPORT-LENGTH_for_oracle = LENGTH_for_oracle
REPORT-PRIVILEGE_for_oracle = PRIVILEGE_for_oracle
REPORT-STATUS_for_oracle = STATUS_for_oracle

transforms.conf

[ACTION_for_oracle]
REGEX = ACTION[\s+]?\:\[\d+\]\s+\'(.*?)\'([\r\n]+)(DATABASE\s+USER)
FORMAT = ACTION::"$1"

[CLIENT_USER_for_oracle]
REGEX = CLIENT\s+USER[\s+]?\:\[\d+\]\s+\'([^']+)
FORMAT = CLIENT_USER::"$1"

[CLIENT_TERMINAL_for_oracle]
REGEX = CLIENT\s+TERMINAL[\s+]?\:\[\d+\]\s+\'([^']+)
FORMAT = CLIENT_TERMINAL::"$1"

[DATABASE_USER_for_oracle]
REGEX = DATABASE\s+USER[\s+]?\:\[\d+\]\s+\'([^']+)
FORMAT = DATABASE_USER::"$1"

[LENGTH_for_oracle]
REGEX = LENGTH[\s+]?\:\s+\'(\d+)\'
FORMAT = LENGTH::$1

[PRIVILEGE_for_oracle]
REGEX = PRIVILEGE[\s+]?\:\[\d+\]\s+\'([^']+)
FORMAT = PRIVILEGE::"$1"

[STATUS_for_oracle]
REGEX = STATUS[\s+]?\:\[\d+\]\s+\'(\d+)
FORMAT = STATUS::$1

[doreno]

Sure! Thank you for your quick help.

[root@webdbstage01 adump]# cat webstage_ora_9793_1.aud

Audit file /u00/oracle/admin/webstage/adump/stage_ora_9793_1.aud
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u00/oracle/product/11.2/db11g
System name:    Linux
Node name:  stage01
Release:    2.6.18-274.el5
Version:    #1 SMP Mon Jul 25 13:17:49 EDT 2011
Machine:    x86_64
VM name:    VMWare Version: 6
Instance name: stage
Redo thread mounted by this instance: 1
Oracle process number: 89
Unix process pid: 9793, image: oracle@stage01

Mon Jul 30 17:29:57 2012 -06:00
LENGTH : '189'
ACTION :[30] 'select sysdate, null from dual'
DATABASE USER:[3] 'OS'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[8] 'D08'
CLIENT TERMINAL:[8] 'D08'
STATUS:[1] '0'
DBID:[10] '2912503054'

Mon Jul 30 17:29:57 2012 -06:00
LENGTH : '189'
ACTION :[30] 'select sysdate, null from dual'
DATABASE USER:[3] 'OS'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[8] 'D08'
CLIENT TERMINAL:[8] 'D08'
STATUS:[1] '0'
DBID:[10] '2912503054'

Mon Jul 30 17:29:57 2012 -06:00
LENGTH : '315'
ACTION :[155] 'DECLARE job BINARY_INTEGER := 1; broken BOOLEAN := TRUE; next_date DATE := SYSDATE+1; BEGIN Grant_Online_Dev_Privs('TABLE','OS','VIDEOS'); END; '
DATABASE USER:[3] 'OS'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[8] 'D08'
CLIENT TERMINAL:[8] 'D08'
STATUS:[1] '0'
DBID:[10] '2912503054'

[MarioM]

if you set the sourcetype then [oracle_syslog] in props.conf will apply if the data is the expected one...

EXTRACT-oracle_key_value_pair = (?i)(?<_KEY_1>\S+):\[\d+\]\s+"(?<_VAL_1>[^"]+)" should extract fields and lookups work.

But from you explanation it seems it is not same data and that's lookup doesnot work too (it's looking for field name ACTION).

Can you post sample adump data that you are indexing into splunk ?