topic Re: How to extract fields from Oracle Diagnostic logs (ODL) format in Splunk Search

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

vchinnadurai — Thu, 29 Dec 2016 22:33:50 GMT

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

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

niketn — Fri, 30 Dec 2016 13:11:52 GMT

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.

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

vchinnadurai — Tue, 29 Sep 2020 12:16:56 GMT

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

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

sshelly_splunk — Wed, 04 Jan 2017 23:00:52 GMT

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?

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

sshelly_splunk — Wed, 04 Jan 2017 23:08:01 GMT

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>.+)

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

vchinnadurai — Thu, 05 Jan 2017 22:44:27 GMT

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

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

vchinnadurai — Thu, 05 Jan 2017 22:48:20 GMT

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.