Archive

How to extract fields from Oracle Diagnostic logs (ODL) format

New Member

I am trying to extract fields from Oracle Diagnostic logs for Hyperion Essbase as each event will have values in different location of the event.

For example: When trying to extract the message in the log

Format 1:
[timestamp] [component] [module] [module id] [context id] [thread id] Command Received to extract

Format 2:
[timestamp] [component] [module] [module id] [context id] [thread id] [DB Name] Connected from [IP Address]

I need to extract the Message which is in different location of the event in the same field.

Any help would be great.

Thank you

Tags (1)
0 Karma

Splunk Employee
Splunk Employee

I think I have it. This does th e "OR" check for DBNAME: \w+ and does not capture that.
HTH

REGEX = (?:\[tid:\s+\d+\]\s+\[DBNAME:\s+\w+\]|\[tid:\s+\d+\])(?P<message>.+)

New Member

This matches any character within [...] and not the string. No luck.

0 Karma

Splunk Employee
Splunk Employee

How about something like this?
in props.conf:

REGEX = .+\[tid:\s+\d+\](?P<message>.+)

This captures DBNAME: SAMPLE as well. Not sure if you want that or not. I'll work on it a bit more to see if I cna have a single regex to do it, but I imagine, knowing the name of DB might be useful?

0 Karma

New Member

No I don't need the DBNAME as that is not part of the Message string.

I used this regex
REGEX = [.*]\s+(?P[A-Z].+)

This appears to be working.

0 Karma

New Member

Here is the sample. There are many formats in the ODL.

Format 1

[2016-12-30T11:08:46.216-05:00] [ESSBASE0] [NOTIFICATION:16] [TCP-59] [TCP] [ecid: 1482887126970,0] [tid: 140198389143872] Connected from [::ffff:999.999.99.999]

[2016-12-30T11:08:27.60-05:00] [ESSBASE0] [NOTIFICATION:16] [AGENT-1001] [AGENT] [ecid: 1482887126970,0] [tid: 140198073563456] Received client request: Clear Application/Database (from user [sampleuser@Native Directory])

Format 2

[2016-12-30T11:08:24.302-05:00] [PLN3] [NOTIFICATION:16] [REQ-91] [REQ] [ecid: 148308120489,0] [tid: 140641102035264] [DBNAME: SAMPLE] Received Command [SetAlias] from user [sampleuser@Native Directory]

[2016-12-30T11:08:26.932-05:00] [PLN3] [NOTIFICATION:16] [SSE-82] [SSE] [ecid: 148308120489,0] [tid: 140641102035264] [DBNAME: SAMPLE] Spreadsheet Extractor Big Block Allocs -- Dyn.Calc.Cache : [202] non-Dyn.Calc.Cache : [0]

The filed value that I am working to extract is below:

Format 1

Message_Text="Connected from [::ffff:999.999.99.999]"
Message_Text="Received client request: Clear Application/Database (from user [sampleuser@Native Directory])"

Format 2

Message_Text="Received Command [SetAlias] from user [sampleuser@Native Directory]"
Message_Text="Spreadsheet Extractor Big Block Allocs -- Dyn.Calc.Cache : [202] non-Dyn.Calc.Cache : [0]"

As you can see the field name is the same but the value comes from different location of the event.

I have defined the following in props.conf

[sample_source_type]
REPORT-Message_Text = extract_Message_Text
REPORT-Message_Text = extract_Message_Text_FMT2
REPORT-Message_Text = extract_Message_Text_FMT3

I have defined the following in transforms.conf

[extract_Message_Text]
REGEX = ^[^ \n]\s+[[^]]+][^]\n]]\s+[\w+:[^]]+[^[\n][[^]]+[^:\n]:\s+\d+,\d+[^:\n]*:\s+\d+]\s+(?P.+)
FORMAT = Message_Text::$1

[extract_Message_Text_FMT2]
REGEX = ^[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]*\s+(?P.+)
FORMAT = Message_Text::$1

[extract_Message_Text_FMT3]
REGEX = ^[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+[^\s\n]\s+\d]\s(?P.+)
FORMAT = Message_Text::$1

This is due to making the same fields available across many source types which will have same ODL format events. But it is not working as expected.

Thank you

0 Karma

Legend

Can you add sample events for Format 1 and Format 2 from your logs?
Have you tried Interactive Field Extraction in Splunk using Extract New Fields which generates dynamic regular expressions based on the need.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!