Getting Data In
Highlighted

How to relate a log header or summary without timestamp to events with timestamp?

Path Finder

Hi,

In Splunk 6.1.2, I have been asked by a client to configure indexing of Oracle audit trails. The client's trails are being saved in .aud files whose filenamea have a timestamp added:

oraprod_ora_10486100_20140630175559131361143795.aud

After the third underscore character, the date and time are visible in the format yyyymmddhhmmss (20140630175559 - 6th of July, 2014, 5:55:59 PM). The file always has a summary or header which is 12 lines by standard. Then it is followed by a blank line, then the events with timestamp (some values were edited to preserve client's privacy) begin:

Audit file /bkp_risc/oracle/audit/oraprod/oraprod_ora_10486100_20140630175559131361143795.aud
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORACLE_HOME = /oracle/produto/11.2
System name:    AIX
Node name:  node_name_edited
Release:    1
Version:    7
Machine:    00F8E9C04C00
Instance name: oraprod
Redo thread mounted by this instance: 1
Oracle process number: 93
Unix process pid: 10486100, image: oracle@node_name_edited (TNS V1-V3)

Mon Jun 30 17:55:59 2014 -03:00
LENGTH : '152'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[8] '33405459'

Mon Jun 30 17:55:59 2014 -03:00
LENGTH : '151'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
...

I've got props.conf and transforms.conf set to extract all fields from the summary and events as well, it is working fine thanks to the help of chanfoli in this question. The remaining problem I have is as follow: I need to preserve and index the summary and relate it to the events of the file it belongs to. I thought a common value is a timestamp, and since the summary doesn't have one, I could extract it from the filename and associate it to each line in the summary. Or I could just get the timestamp from the first event (which is the same) and associate it to each and all summary lines/events. How would I accomplish this?

Another and kinda more logical solution would be associate events and summary by source, being it the filename, but then relating events in time would not be accomplished, since the summary has the _time variable set to index time, while the events have _time set with their own timestamps, and these values more often than not are different.

In an ideal world, I would be able to relate the summary and events of each file by timestamp AND filename. Is it possible? If yes, how?

0 Karma
Highlighted

Re: How to relate a log header or summary without timestamp to events with timestamp?

Splunk Employee
Splunk Employee

I'm not sure I understand, but doesn't the summary have a timestamp in the first line you could grab:
Audit file /bkprisc/oracle/audit/oraprod/oraprodora1048610020140630175559131361143795.aud
(2014-JUNE-30 17:55:59)?

0 Karma
Highlighted

Re: How to relate a log header or summary without timestamp to events with timestamp?

Path Finder

Yes. I was able to stablish a relation during search time with wildcards in the format 20140630, for instance, please see my full comment below coz I was unable to reply to your comment (some issue with the editor)

0 Karma
Highlighted

Re: How to relate a log header or summary without timestamp to events with timestamp?

Path Finder

to ssievert_splunk (can't reply to your comment, something wrong with the editor - again):

Yes. I was able to stablish a relation during search time with wildcards in the format *20140630*, for instance, saving the trouble of creating complex relations elsewhere. But I was not able to stablish this relation during index time with the file name. Instead, I changed, in transforms.conf, the first stanza from this:

[HEADER01_for_oracle]
REGEX = Audit\s+file\s+(.*\s*?)Oracle\s+Database
FORMAT = OAT_Audit_file::"$1"

... to this:

[HEADER01_for_oracle]
REGEX = Audit\s+file\s+(.*\s*?)Oracle\s+Database
FORMAT = OAT_Audit_file::"$1"
REGEX = \w{3}\s+\w{3}\s+\d+\s+\d+\:\d+\:\d+\s+\d{4}
DEST_KEY = _time

... extracting the timestamp from the first event after the header/summary and then setting all header fields with this specific timestamp. Problem is, I noticed this specific field OAT_Audit_file is not being indexed anymore. This is is an undesired effect. I know there is something wrong with the stanza, but I cannot find out what it is or how to fix it. The second regex, which sets the _time variable, was in a stanza of its own before, but it didn't work, setting a correct timestamp only for the fields in the header/summary which didn't have a timestamp but at the same time it caused Splunk to ignore the timestamp from the events that follow, messing them up to a point all had an index-time timestamp, not the one stated in the file for each event.

How should I set a timestamp to the header fields and leave the others alone but correctly set?

0 Karma