Splunk Search

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

vchinnadurai
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

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

vchinnadurai
New Member

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

0 Karma

sshelly_splunk
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

vchinnadurai
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

vchinnadurai
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

niketn
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...