# Extract certain fields from Oracle listener log

**URL:** <https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992>\
**Category:** Logstash\
**Created:** [October 12, 2015, 9:19am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992 "2015-10-12T09:19:46Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![oravirt](https://avatars.discourse-cdn.com/v4/letter/o/3e96dc/32.png) [@oravirt](https://discuss.elastic.co/u/oravirt)\
**Post date:** [October 12, 2015, 9:19am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/1 "2015-10-12T09:19:46Z")

</div>

Hi,

First off, I'm new to logstash so I appreciate any help I can get with this problem.

I'm trying to get some information from the Oracle listener log into logstash but I'm not entirely sure how do that. Logstash reads the file just fine, and I can view the info in Kibana with no problems. But, I really just want certain parts of the information.  
Below is a sample of the logfile. Lots of information but the only rows I'm really interested in are the ones that have the word 'established' in them.  
Then for each of those lines I want a certain number of fields (from the highlighted example below):  
DATE -\> (12-OCT-2015 10:32:33)  
SERVICE\_NAME -\> (asmdb)  
PROGRAM -\> (sqlplus@db1)  
USER -\> (oracle)  
HOST -\> (db1)

So basically, a grep for 'established' and then regexp the fields I want. I guess grok is the way to go but I cant really figure it out.

Mon Oct 12 10:31:09 2015  
WARNING: Subscription for node down event still pending  
12-OCT-2015 10:32:09 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=db1)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647552)) \* status \* 0  
Mon Oct 12 10:32:32 2015  
**12-OCT-2015 10:32:33** \* (CONNECT\_DATA=(SERVER=DEDICATED)(SERVICE\_NAME= **asmdb** )(CID=(PROGRAM= **sqlplus@db1** )(HOST= **db1** )(USER= **oracle** ))) \* (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=38999)) \* **establish** \* asmdb \* 0  
12-OCT-2015 10:32:33 \* service\_update \* asmdb \* 0  
12-OCT-2015 10:32:36 \* service\_update \* asmdb \* 0

Any ideas?

Thanks in advance!  
/M

---

<div class="post-metadata">

**Author:** ![oravirt](https://avatars.discourse-cdn.com/v4/letter/o/3e96dc/32.png) [@oravirt](https://discuss.elastic.co/u/oravirt)\
**Post date:** [October 12, 2015, 9:23am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/2 "2015-10-12T09:23:40Z")

</div>

Hi again,

Forgot to mention I'm on Logstash 1.5.4

/M

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 12, 2015, 10:26am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/3 "2015-10-12T10:26:13Z")

</div>

What have you tried so far?

---

<div class="post-metadata">

**Author:** ![oravirt](https://avatars.discourse-cdn.com/v4/letter/o/3e96dc/32.png) [@oravirt](https://discuss.elastic.co/u/oravirt)\
**Post date:** [October 13, 2015, 8:38am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/4 "2015-10-13T08:38:46Z")

</div>

Hi,

Sorry for the late answer, got held up with other things yesterday

I actually got his working this morning (or at least sort of)

I have started writing a couple of patterns to extract the fields that I need:

```
SERVICE_NAME (?<=(SERVICE_NAME=)).*?(?=\))
PROGRAM (?<=(PROGRAM=)).*?(?=\))
HOST (?<=(HOST=)).*?(?=\))
USER (?<=(USER=)).*?(?=\))

```

And if I use this:

```
  grok {
      type => "oracle-listener-conn"
      match => {"message" => "%{NOTSPACE}%{SPACE} %{TIME} \*%{SPACE}%{NOTSPACE}%{SERVICE_NAME:service_name}%{NOTSPACE}%{PROGRAM:program}%{NOTSPACE}%{HOST:client}%{NOTSPACE}%{USER:user}" }

      patterns_dir => '/etc/logstash/patterns/'
    }

```

I get this:

```
    "message" => "12-OCT-2015 13:31:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=asmdb)(CID=(PROGRAM=sqlplus@db1)(HOST=db1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=43307)) * establish * asmdb * 0",
    "@version" => "1",
    "@timestamp" => "2015-10-12T11:31:24.757Z",
      "host" => "db1",
      "path" => "/u01/app/oracle/diag/tnslsnr/db1/listener/trace/listener.log",
      "type" => "oracle-listener-conn",
      "service_name" => "asmdb",
      "program" => "sqlplus@db1",
      "client" => "db1",
      "user" => "oracle"

```

Which is what I want, so that's good.

However, the order of the fields I want may shift around and some of them may also be empty, and in that case I'll miss those.  
Is there a way to write the match part more generic, or do (can?) I have to write a separate line for every possible scenario?

Thanks!  
regards  
/M

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 13, 2015, 10:15am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/5 "2015-10-13T10:15:34Z")

</div>

You might be able to use the [kv filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-kv.html), but the enclosing parentheses might make it difficult (the `trim` and `trimkey` should be useful). Otherwise a [ruby filter](https://www.elastic.co/guide/en/logstash/current/plugins-filters-ruby.html) will definitely allow you to write a generic extractor of key/value pairs.

---

<div class="post-metadata">

**Author:** ![oravirt](https://avatars.discourse-cdn.com/v4/letter/o/3e96dc/32.png) [@oravirt](https://discuss.elastic.co/u/oravirt)\
**Post date:** [October 14, 2015, 9:35am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/6 "2015-10-14T09:35:45Z")

</div>

Cool, I'll have a look at that.

Thank you!

/M

---

<div class="post-metadata">

**Author:** ![raviguduru](https://avatars.discourse-cdn.com/v4/letter/r/65b543/32.png) [@raviguduru](https://discuss.elastic.co/u/raviguduru)\
**Post date:** [December 10, 2015, 4:04pm UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/7 "2015-12-10T16:04:32Z")

</div>

I tried your pattern to extract the fields but I am getting compiler error or not match (\_grokfailure). Below is the sample records in oracle listener log. I am looking for all the fields in assignment.

30-APR-2014 08:46:00 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=west4)(PORT=1521)))(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:46:51 2014  
30-APR-2014 08:46:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:47:51 2014  
30-APR-2014 08:47:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:48:51 2014  
30-APR-2014 08:48:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:49:51 2014  
30-APR-2014 08:49:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=east4)(VERSION=186647040)) \* status \* 0

---

<div class="post-metadata">

**Author:** ![raviguduru](https://avatars.discourse-cdn.com/v4/letter/r/65b543/32.png) [@raviguduru](https://discuss.elastic.co/u/raviguduru)\
**Post date:** [December 10, 2015, 5:06pm UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/8 "2015-12-10T17:06:19Z")

</div>

Magnus,

I tried your pattern to extract the fields but I am getting compiler error or not match (\_grokfailure). Below is the sample records in oracle listener log. I am looking for all the fields in assignment. I need the data after that status.

30-APR-2014 08:46:00 \*  
(CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=west4)(PORT=1521)))(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:46:51 2014  
30-APR-2014 08:46:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:47:51 2014  
30-APR-2014 08:47:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:48:51 2014  
30-APR-2014 08:48:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0  
Wed Apr 30 08:49:51 2014  
30-APR-2014 08:49:51 \* (CONNECT\_DATA=(CID=(PROGRAM=)(HOST=sdp04)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER\_WFLOWPRD)(VERSION=186647040)) \* status \* 0

---

<div class="post-metadata">

**Author:** ![Tony\_Aponte1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tony_aponte1/32/46431_2.png) [@Tony\_Aponte1](https://discuss.elastic.co/u/Tony_Aponte1)\
**Post date:** [February 3, 2016, 10:52pm UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/9 "2016-02-03T22:52:31Z")

</div>

Are you referring to the different types of listener log event lines (i.e. connection requests, status, tnspings, db registration events), each with a different number of fields? I ran into the same thing and ended up using conditionals. Although my example is sourcing from the XML version of the logs the conditional statements may still apply to your issue:

filter {  
if "listner\_log" in [tags] {  
multiline {  
pattern =\> "^((?!).)\*$"  
what =\> "next"  
negate =\> false  
}  
xml {  
store\_xml =\> "false"  
source =\> "message"  
target =\> "doc"  
xpath =\> [  
"/msg/@client\_id", "msg\_client\_id",  
"/msg/@comp\_id", "msg\_comp\_id",  
"/msg/@host\_id", "msg\_host\_id",  
"/msg/@host\_addr", "msg\_host\_addr",  
"/msg/@level", "msg\_level",  
"/msg/@type", "msg\_type",  
"/msg/@time", "msg\_time",  
"/msg/txt/text()","msg\_txt"  
]  
}  
date {  
match =\> ["msg\_time", "ISO8601"]  
}  
if [message] =~ /CONNECT\_DATA/ {  
grok {  
match =\> ["msg\_txt", "%{URIHOST} %{TIME} \* %{DATA:uri} \* %{DATA:address} \* %{DATA:action} \* %{DATA:service\_name} \* %{GREEDYDATA:result}"]  
}  
grok { match =\> ["uri", "(SERVICE\_NAME=%{DATA:uri\_service\_name})"] }  
grok { match =\> ["uri", "(COMMAND=%{DATA:uri\_command})"] }  
grok { match =\> ["uri", "(PROGRAM=%{DATA:uri\_program})"] }  
grok { match =\> ["uri", "(HOST=%{DATA:uri\_srchost})"] }  
grok { match =\> ["uri", "(USER=%{DATA:uri\_user})"] }  
grok { match =\> ["uri", "(ARGUMENTS=%{DATA:uri\_arguments})"] }  
grok { match =\> ["uri", "(SERVICE=%{DATA:uri\_service})"] }  
grok { match =\> ["uri", "(VERSION=%{DATA:uri\_version})"] }  
grok { match =\> ["address", "(PROTOCOL=%{DATA:address\_protocol})"] }  
grok { match =\> ["address", "(HOST=%{DATA:address\_srchost})"] }  
grok { match =\> ["address", "(PORT=%{DATA:address\_psrcport})"] }  
} else {  
grok {  
break\_on\_match =\> false  
match =\> ["msg\_txt", "%{URIHOST} %{TIME} \* %{DATA:action} \* %{DATA:instance\_name} \* %{GREEDYDATA:result}"]  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 6, 2017, 5:13am UTC](https://discuss.elastic.co/t/extract-certain-fields-from-oracle-listener-log/31992/10 "2017-07-06T05:13:06Z")

</div>