# Fields are merge grok output

**URL:** <https://discuss.elastic.co/t/fields-are-merge-grok-output/233988>\
**Category:** Logstash\
**Created:** [May 23, 2020, 10:04am UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988 "2020-05-23T10:04:10Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![himalc](https://avatars.discourse-cdn.com/v4/letter/h/258eb7/32.png) [@himalc](https://discuss.elastic.co/u/himalc)\
**Post date:** [May 23, 2020, 10:04am UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/1 "2020-05-23T10:04:11Z")

</div>

I have a log like this:

2020-05-22T11:45:21.297418 H 20 mytest.cpp:175 stdlog sql\_execute 38533 73 testdb admin 100-xxy {"query\_str","client","execution\_time\_ms","total\_time\_ms"}  
{"select \* from mydatabase;","tcp:localhost:4000","27","73"}

My Grok pattern:  
filter {  
grok {  
match =\> { "message" =\> "%{GREEDYDATA:timex} %{WORD:Ix} %{NUMBER:nox} %{GREEDYDATA:code} %{WORD:stdlog} %{WORD:type} %{NUMBER:numbery} %{NUMBER:noh} %{WORD:dbtype} %{WORD:loguserx} %{GREEDYDATA:sessionID} %{GREEDYDATA:query\_field} %{GREEDYDATA:query\_stats}" }  
}  
}

problem 01:  
output:  
My result:  
sessionID field contains with part of query\_field result, and query\_field contains with part of the query\_stats results.

sessionID result=\> 731-ufsN {"myquery","source","time","total\_time"} {"select \*

query\_field result=\> from   
query\_stats result=\> db\_states;","tcp:myhost:12336","10","15"}

expected result:  
sessionID result=\> 731-ufsN  
query\_field result=\> {"myquery","client","time","total\_time"}  
query\_stats result=\>{"select \* db\_states;","tcp:myhost:12336","10","15"}

Problem 02:  
how can I get each value inside the {"select \* db\_states;","tcp:myhost:12336","10","15" }.

Any help with this??

---

<div class="post-metadata">

**Author:** ![pup\_seba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pup_seba/32/42988_2.png) [@pup\_seba](https://discuss.elastic.co/u/pup_seba)\
**Post date:** [May 23, 2020, 10:31am UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/2 "2020-05-23T10:31:59Z")

</div>

Hi,

Try this pattern.

```auto
%{TIME:timex} %{WORD:Ix} %{NUMBER:nox} (?<code>[^\s]*) %{WORD:stdlog} %{WORD:type} %{NUMBER:numbery} %{NUMBER:noh} %{WORD:dbtype} %{WORD:loguserx} (?<sessionID>[^\s]*) {(?<query_field>[^}]*)}{(?<query_stats>[^}]*)}$

```

---

<div class="post-metadata">

**Author:** ![himalc](https://avatars.discourse-cdn.com/v4/letter/h/258eb7/32.png) [@himalc](https://discuss.elastic.co/u/himalc)\
**Post date:** [May 23, 2020, 11:29am UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/3 "2020-05-23T11:29:15Z")

</div>

Thanks Pup\_seba, Actually I got my required output just changing the data pattern from GREEDYDATA to DATA in sessionID, query\_field fileds. Not working on splitting the values to different fields like {"myquery","source","time","total\_time"}.

Anyway I will try your solution in my code as well. Thanks a lot.

---

<div class="post-metadata">

**Author:** ![himalc](https://avatars.discourse-cdn.com/v4/letter/h/258eb7/32.png) [@himalc](https://discuss.elastic.co/u/himalc)\
**Post date:** [May 23, 2020, 4:20pm UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/4 "2020-05-23T16:20:46Z")

</div>

Hi Pup\_seba,  
Do let me know, how can I get different field for this result. select query changing every time, like will be more complex time to time (including inner join, join). Need to create graphs using time, total\_time fields.

{"select \* db\_states;","tcp:myhost:12336","10","15"}

My pattern like this. But did not work.

filter {  
grok {  
match =\> { "message" =\> "%{GREEDYDATA:currenttimex} %{WORD:Ix} %{NUMBER:no16} %{GREEDYDATA:code} %{WORD:stdlog} %{WORD:type} %{NUMBER:number38} %{NUMBER:no73} %{WORD:dbtype} %{WORD:loguser} %{DATA:sessionID} %{DATA:query\_topic} %{GREEDYDATA:query\_stats}" }  
}  
}

filter {  
grok {  
match =\> { "query\_stats" =\> "%{GREEDYDATA:sql} %{GREEDYDATA:client} %{NUMBER:time} %{NUMBER:total\_time}" }  
}  
}

---

<div class="post-metadata">

**Author:** ![pup\_seba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pup_seba/32/42988_2.png) [@pup\_seba](https://discuss.elastic.co/u/pup_seba)\
**Post date:** [May 23, 2020, 4:39pm UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/5 "2020-05-23T16:39:00Z")

</div>

Hi himalc 🙂

I think I did not understand your initial expected output then. So, I guess that instead of this:

```auto
sessionID result=> 731-ufsN
query_field result=> {"myquery","client","time","total_time"}
query_stats result=>{"select * db_states;","tcp:myhost:12336","10","15"}

```

You actually expect this output:

```auto
sessionID result=> 731-ufsN
query_str => myquery
client => client
execution_time_ms => time
total_time_ms => total_time
query_stats => {"select * db_states;","tcp:myhost:12336","10","15"}
query_field => "myquery" + "client" + "time" + "total_time"

```

If this is the case, then I guess you need a grok filter AND a mutate filter. The grok would look like this:

```auto
%{TIME:timex} %{WORD:Ix} %{NUMBER:nox} (?<code>[^\s]*) %{WORD:stdlog} %{WORD:type} %{NUMBER:numbery} %{NUMBER:noh} %{WORD:dbtype} %{WORD:loguserx} (?<sessionID>[^\s]*) {(?<query_str>[^,]*),(?<client>[^,]*),(?<execution_time_ms>[^,]*),(?<total_time_ms>[^}]*)}{(?<query_stats>[^}]*)}$

```

Then, you would need to "construct" the query\_stats field, and this is where you use the "mutate" filter. I can't test it right now, but this post could help you [Adding a field from existing ones](https://discuss.elastic.co/t/adding-a-field-from-existing-ones/77697).

The formula I'm using in the grok is quite easy and is always the same. Basically I use some literals to "pinpoint" somethings (look at the commas for instance, those are just literal matches).  
Then I use this extended group (?subexp) over and over again 🙂 You can see more info about that here: [https://github.com/kkos/oniguruma/blob/master/doc/RE](https://github.com/kkos/oniguruma/blob/master/doc/RE)

Then is only a regular expression where I just match "any character until (^) the character which in most cases is a comma. Then I just use a quantifier (_) so I match "all the characters" until the negation. For this you'll see "(?\<field\_name\>[^,]_).

Hope this helped you.

---

<div class="post-metadata">

**Author:** ![himalc](https://avatars.discourse-cdn.com/v4/letter/h/258eb7/32.png) [@himalc](https://discuss.elastic.co/u/himalc)\
**Post date:** [May 23, 2020, 5:02pm UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/6 "2020-05-23T17:02:19Z")

</div>

Hi Pup\_seba,  
Thanks your prompt reply. Unfortunately, above grok pattern does not work for me.  
To be clarify, My final expectation is to get "sql query string","Time", "total time" and "client" values as a separate fields for generating graphs. Below is my log pattern (These values : **{"select \* from mydatabase;","tcp:localhost:4000","27","73"}** ).

In this case,  
query string is = "select \* from mydatabase  
client = tcp:localhost:4000  
time = 27  
total time = 73

log file:  
2020-05-22T11:45:21.297418 H 20 mytest.cpp:175 stdlog sql\_execute 38533 73 testdb admin 100-xxy {"query\_str","client","execution\_time\_ms","total\_time\_ms"}  
{"select \* from mydatabase;","tcp:localhost:4000","27","73"}

---

<div class="post-metadata">

**Author:** ![pup\_seba](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pup_seba/32/42988_2.png) [@pup\_seba](https://discuss.elastic.co/u/pup_seba)\
**Post date:** [May 23, 2020, 5:26pm UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/7 "2020-05-23T17:26:19Z")

</div>

For this log:

```auto
2020-05-22T11:45:21.297418 H 20 mytest.cpp:175 stdlog sql_execute 38533 73 testdb admin 100-xxy {"select * from mydatabase;","tcp:localhost:4000","27","73"}

```

Using this grok filter:

```auto
%{TIME:timex} %{WORD:Ix} %{NUMBER:nox} (?<code>[^\s]*) %{WORD:stdlog} %{WORD:type} %{NUMBER:numbery} %{NUMBER:noh} %{WORD:dbtype} %{WORD:loguserx} (?<sessionID>[^\s]*) {"(?<query_str>[^"]*)","(?<client>[^"]*)","(?<execution_time_ms>[\d]*)","(?<total_time_ms>[\d]*)"}$

```

This is the output:

```auto
{
  "code": "mytest.cpp:175",
  "total_time_ms": "73",
  "noh": "73",
  "query_str": "select * from mydatabase;",
  "sessionID": "100-xxy",
  "type": "sql_execute",
  "stdlog": "stdlog",
  "Ix": "H",
  "numbery": "38533",
  "loguserx": "admin",
  "nox": "20",
  "execution_time_ms": "27",
  "dbtype": "testdb",
  "client": "tcp:localhost:4000",
  "timex": "11:45:21.297418"
}

```

---

<div class="post-metadata">

**Author:** ![himalc](https://avatars.discourse-cdn.com/v4/letter/h/258eb7/32.png) [@himalc](https://discuss.elastic.co/u/himalc)\
**Post date:** [May 23, 2020, 6:19pm UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/8 "2020-05-23T18:19:39Z")

</div>

Hi Sebastian,  
Thanks for the code. But apply this my logstash was shutdown.

Seems 1 field set missing above code.  
2020-05-22T11:45:21.297418 H 20 mytest.cpp:175 stdlog sql\_execute 38533 73 testdb admin 100-xxy **{"query\_str","client","execution\_time\_ms","total\_time\_ms"}**  
{"select \* from mydatabase;","tcp:localhost:4000","27","73"}

I have updated it but still encountered an error. Check my Grok below. Let me know if you found any error here.

match =\> { "message" =\> "%{GREEDYDATA:currenttimex} %{WORD:Ix} %{NUMBER:no16} %{GREEDYDATA:code} %{WORD:stdlog} %{WORD:type} %{NUMBER:number38} %{NUMBER:no73} %{WORD:dbtype} %{WORD:loguser} %{DATA:sessionID} %{DATA:query\_topic} %{"(?\<query\_str\>[^"]_)","(?[^"]_)","(?\<execution\_time\_ms\>[\d]_)","(?\<total\_time\_ms\>[\d]_)"}$" }

log file:  
2020-05-22T11:45:21.297418 H 20 mytest.cpp:175 stdlog sql\_execute 38533 73 testdb admin 100-xxy {"query\_str","client","execution\_time\_ms","total\_time\_ms"}  
{"select \* from mydatabase;","tcp:localhost:4000","27","73"}

This my Grok pattern

filter {  
grok {  
match =\> { "message" =\> "%{TIME:timex} %{WORD:Ix} %{NUMBER:nox} (?`[^\s]) %{WORD:stdlog} %{WORD:type} %{NUMBER:numbery} %{NUMBER:noh} %{WORD:dbtype} %{WORD:loguserx} (?[^\s]) %{DATA:query_topic} {"(?<query_str>[^"])","(?[^"])","(?<execution_time_ms>[\d])","(?<total_time_ms>[\d])"}$" }
}
}`

---

<div class="post-metadata">

**Author:** ![himalc](https://avatars.discourse-cdn.com/v4/letter/h/258eb7/32.png) [@himalc](https://discuss.elastic.co/u/himalc)\
**Post date:** [May 24, 2020, 8:18am UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/9 "2020-05-24T08:18:20Z")

</div>

Hi Sebastian,

Do you have any idea of this issue.

Thanks.

---

<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:** [June 21, 2020, 8:18am UTC](https://discuss.elastic.co/t/fields-are-merge-grok-output/233988/10 "2020-06-21T08:18:32Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.