Using Grok to add SID field to oracle database logmessages

Elastic Stack Logstash
#1

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.

#2

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".

#3

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.

#4

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 :slight_smile:

Regards,
Debarun.

#5

%{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}$
#6

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.

#7

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

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