# Using Grok to add SID field to oracle database logmessages

**URL:** <https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216>\
**Category:** Logstash\
**Created:** [April 4, 2016, 9:17am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216 "2016-04-04T09:17:46Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![debarun](https://avatars.discourse-cdn.com/v4/letter/d/a88e4f/32.png) [@debarun](https://discuss.elastic.co/u/debarun)\
**Post date:** [April 4, 2016, 9:17am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/1 "2016-04-04T09:17:47Z")

</div>

Hi,

I am trying to add a field to every message of my alert log .  
The test has to be extracted from the file name and appended as a field to every log entry.

To do that I am doing:-

```
if [path] =~ /alert/ {
        grok {
    match => ["path","%{WORD:logtype}_%{WORD:sid}.%{WORD.filetype}"]
  }
       mutate {
      add_field => { "SID" => "%{sid}" }
    }

  }

```

But the SID is not appended. Instead,

```
  "@timestamp" => "2015-09-28T16:30:32.000Z",
        "path" => "/path/to/file",
        "host" => "hostname",
        "type" => "oracle_alert_log",
        "tags" => [
    [0] "multiline",
    [1] "_grokparsefailure"
],
         "SID" => "%{sid}",
"oradb_status" => "running"

```

I also tried

**match =\> { "path" =\> "%{WORD:logtype}\_%{WORD:sid}.%{WORD.filetype}" }**

But does not work.

Regards,  
Debarun.

---

<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:** [April 4, 2016, 9:44am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/2 "2016-04-04T09:44:36Z")

</div>

Your grok filter doesn't match the input, hence the `_grokparsefailure` tag. It's hard to help you with the grok filter without seeing an actual `path` value. The expression you have obviously doesn't match the string "/path/to/file".

---

<div class="post-metadata">

**Author:** ![debarun](https://avatars.discourse-cdn.com/v4/letter/d/a88e4f/32.png) [@debarun](https://discuss.elastic.co/u/debarun)\
**Post date:** [April 4, 2016, 9:49am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/3 "2016-04-04T09:49:29Z")

</div>

Hi Magnus,

The input part is:-

```
input {
  file {
		path => "/apps/codi/elk/data/oracle/alert_*.log"
		start_position => beginning 
		sincedb_path => '/opt/logstash/sincedb_oracle.db' 
		sincedb_write_interval => 0 
		type => 'oracle_alert_log'
  }
}

```

Regards,  
Debarun.

---

<div class="post-metadata">

**Author:** ![debarun](https://avatars.discourse-cdn.com/v4/letter/d/a88e4f/32.png) [@debarun](https://discuss.elastic.co/u/debarun)\
**Post date:** [April 4, 2016, 10:02am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/4 "2016-04-04T10:02:32Z")

</div>

Hi,

I think the problem is the entire path is /apps/codi/elk/data/oracle/alert\_XOEBS.log, while I am only trying to filter out the SID from the filename.

At the same time I am not sure how to write the match fileter to break down the entire path.

Any suggestions would be helpful 🙂

Regards,  
Debarun.

---

<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:** [April 4, 2016, 10:35am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/5 "2016-04-04T10:35:16Z")

</div>

`%{WORD.filetype}` needs to be `%{WORD:filetype}`. Apart from that it should be okay, but I suggest the following pattern:

```
/%{WORD:logtype}_%{WORD:sid}\.%{WORD:filetype}$
```

---

<div class="post-metadata">

**Author:** ![debarun](https://avatars.discourse-cdn.com/v4/letter/d/a88e4f/32.png) [@debarun](https://discuss.elastic.co/u/debarun)\
**Post date:** [April 4, 2016, 11:20am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/6 "2016-04-04T11:20:30Z")

</div>

But i still get :  
\> \> "message" =\> "\nCompleted checkpoint up to RBA [0xae.2.10], SCN: 6611283675836",

> ```
> > "@version" => "1",
> > "@timestamp" => "2015-09-18T10:02:32.000Z",
> > "path" => "/apps/codi/elk/data/oracle/alert_XOEBS.log",
> > "host" => "JBOSSPOC02",
> > "type" => "oracle_alert_log",
> > "tags" => [
> > [0] "multiline",
> > [1] "_grokparsefailure"
> > ],
> > "SID" => "%{sid}",
> > "oradb_status" => "running"
> 
> ```

The filter is now:-  
if [path] =~ /alert/ {

```
 grok {
    match => { "path" => "/%{WORD:logtype}_%{WORD:sid}\.%{WORD:filetype}$" }

```

}  
mutate {  
add\_field =\> { "SID" =\> "%{sid}" }  
}

}

Regards,  
Debarun.

---

<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:** [April 4, 2016, 11:30am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/7 "2016-04-04T11:30:24Z")

</div>

Hmm. Apparently the definition of WORD ("\b\w+\b") won't work here. You can e.g. use "\w+" instead:

```auto
grok {
  match => {
    "message" => ".*/(?<logtype>\w+)_(?<sid>\w+)\.(?<filetype>\w+)$"
  }
}

```

---

<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:04am UTC](https://discuss.elastic.co/t/using-grok-to-add-sid-field-to-oracle-database-logmessages/46216/8 "2017-07-06T05:04:05Z")

</div>

Closed on July 6, 2017, 5:04am UTC