# Specific GROK filter for multi-line Postgresql log **URL:** \ **Category:** Logstash\ **Created:** [July 25, 2016, 10:59am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286 "2016-07-25T10:59:42Z")\ **Posts on this page:** 14\ **Page:** 1
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [July 25, 2016, 10:59am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/1 "2016-07-25T10:59:42Z")
Hi All, I am having hard times trying to merge multi-line Postgresql logs with GROK into one Logstash event. My log file look like this: ``` Jul 22 17:03:27 my.host example.com[24977]: [137-1] 2016-07-22 17:03:27.339 MSK User: username Database: my_db Host: 192.168.0.52(38494) Proc ID: 24977 etc1 Jul 22 17:03:27 my.host example.com[24977]: [137-2] 2016-07-22 17:03:27.339 MSK User: username Database: my_db Host: 192.168.0.52(38494) Proc ID: 24977 etc2 Jul 22 17:03:27 my.host example.com[24597]: [2953-1] 2016-07-22 17:03:27.339 MSK User: username Database: my_db Host: 192.168.0.52(38053) Proc ID: 24597 etc Jul 22 17:03:27 my.host example.com[3637]: [3779-1] 2016-07-22 17:03:27.340 MSK User: username Database: my_db Host: 192.168.0.52(17809) Proc ID: 3637 etc Jul 22 17:03:27 my.host example.com[24977]: [138-1] 2016-07-22 17:03:27.339 MSK User: username Database: my_db Host: 192.168.0.52(38494) Proc ID: 24977 etc1 Jul 22 17:03:27 my.host example.com[3637]: [3780-1] 2016-07-22 17:03:27.340 MSK User: username Database: my_db Host: 192.168.0.52(17809) Proc ID: 3637 etc Jul 22 17:03:27 my.host example.com[24977]: [138-2] 2016-07-22 17:03:27.339 MSK User: username Database: my_db Host: 192.168.0.52(38494) Proc ID: 24977 etc2 Jul 22 17:03:27 my.host example.com[24977]: [139-1] 2016-07-22 17:03:27.340 MSK User: username Database: my_db Host: 192.168.0.52(38494) Proc ID: 24977 etc Jul 22 17:03:27 my.host example.com[24597]: [2954-1] 2016-07-22 17:03:27.340 MSK User: username Database: my_db Host: 192.168.0.52(38053) Proc ID: 24597 etc ``` I try to use: > multiline { > pattern =\> "... [\d+-1]" # adjust pattern to be more exact > negate =\> true > what =\> "previous" > } but I have: ``` line 1: ...[137-1] and [137-2]... line 2: ...[2953-1]... line 3: ...[3779-1]... line 4: ...[138-1]... line 5: ...[3780-1] and [138-2]... line 6: ...[139-1]... line 7: ...[2954-1]... ``` That's not what I need. I want: ``` line 1: ...[137-1] and [137-2]... line 2: ...[2953-1]... line 3: ...[3779-1]... line 4: ...[138-1] and [138-2]... line 5: ...[3780-1]... line 6: ...[139-1]... line 7: ...[2954-1]... ``` or ``` line 1: ...[137-1] and [137-2]... line 2: ...[2953-1]... line 3: ...[3779-1]... line 4: ...[3780-1]... line 5: ...[138-1] and [138-2]... line 6: ...[139-1]... line 7: ...[2954-1]... ``` I would appreciate if someone can share any hints or his experience on this. Thanks ---
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [July 26, 2016, 7:22pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/2 "2016-07-26T19:22:28Z")
Please give me an idea how to solve my problem. ---
**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:** [July 30, 2016, 12:15pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/3 "2016-07-30T12:15:07Z")
You can't reliably use the multiline codec for this since log lines could be interleaved. Perhaps the aggregate filter can help? ---
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [August 10, 2016, 8:51am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/4 "2016-08-10T08:51:22Z")
Thanks. I read about this plugin and I think it's right. But I don't know ruby for write "code" section for aggregate plugin. Can you help me? ---
**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:** [August 10, 2016, 9:05am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/5 "2016-08-10T09:05:20Z")
Sorry, I'm not that well-versed with aggregate myself and I don't have time to dig around. ---
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [August 10, 2016, 11:26am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/6 "2016-08-10T11:26:05Z")
People, who can help me write codec option for aggregate plugin? ---
**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\ **Post date:** [August 10, 2016, 3:31pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/7 "2016-08-10T15:31:26Z")
Hi, aggregate plugin is the right plugin for your need. Because your logs are interleaved. That said, for one id, can you give an example of what you expect as result document ? ---
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [August 11, 2016, 5:56am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/8 "2016-08-11T05:56:11Z")
I expect the next: > event 1: ...[137-1] and [137-2]... > event 2: ...[2953-1]... > event 3: ...[3779-1]... > event 4: ...[138-1] and [138-2]... > event 5: ...[3780-1]... > event 6: ...[139-1]... > event 7: ...[2954-1] and [2954-2]... Task\_id in this case may be [24977] or 137. My friend help me with ruby code and I made such a configuration: > filter { > grok { > match =\> ["message", "%{SYSLOGTIMESTAMP:timestamp} %{SYSLOGHOST:logsource} %{SYSLOGPROG}: [%{INT:line}-%{INT:part\_of\_line}] %{GREEDYDATA:ostatok}" ] > } > ``` > aggregate { > task_id => "%{line}" > code => "map['full_message'] ||= '' ; map['full_message'] += event['ostatok'] ; event['full_message'] ||= '' ; event['full_message'] = map['full_message']" > } > > ``` > > } > But I have next event on elasticsearch: > event 1: ...[137-1]... > event 2: ...[137-1] and [137-2]... > event 3: ...[2953-1]... > event 4: ...[3779-1]... > event 5: ...[138-1]... > event 6: ...[138-1] and [138-2]... > event 7: ...[3780-1]... > event 8: ...[139-1]... > event 9: ...[2954-1]... > event 10: ...[2954-1] and [2954-2]... > How I can clean my events from event 1: ...[137-1]..., event 5: ...[138-1]..., event 7: ...[2954-1]... ? ---
**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\ **Post date:** [August 11, 2016, 8:00am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/9 "2016-08-11T08:00:16Z")
@111126 Ok, given your logstash configuration, I understand now your need. Your main problem is that you have no explicit "end line marker" to flush aggregate map. But you're lucky ! Last week, aggregate plugin has been released with new options to deal with that case ! So, here's the right configuration for your need : ``` grok { match => ["message", "%{SYSLOGTIMESTAMP:timestamp} %{SYSLOGHOST:logsource} %{SYSLOGPROG}: \[%{INT:line}-%{INT:part_of_line}\] %{GREEDYDATA:ostatok}" ] } aggregate { task_id => "%{line}" code => " map.merge!(event) if map.empty? map['full_message'] ||= '' map['full_message'] += event['ostatok'] " timeout => 10 push_map_as_event_on_timeout => true timeout_code => "event.tag('aggregated')" } if "aggregated" not in [tags] { drop {} } ``` Note that in your grok expression, `[` and `]` chars must be escaped. And for aggregate, the main idea is that, as you have no explicit "end log line", we use 10s timeout to push aggregated map as a new logstash event in the pipeline. ---
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [August 11, 2016, 12:55pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/10 "2016-08-11T12:55:29Z")
Thank you a lot. It's work for me. But after the implementation of your settings, I found a feature that was previously not obvious. But my log is very big and forwarding from many servers, I have coincidence in line numbering. Example: > Aug 11 11:34:53 my.host1 example.com1[21872]: [198-1] 2016-08-11 11:34:53.029 MSK etc > Aug 11 11:34:53 my.host1 example.com1[21878]: [198-1] 2016-08-11 11:34:53.150 MSK etc > Aug 11 11:34:53 my.host1 example.com1[21879]: [198-1] 2016-08-11 11:34:53.515 MSK etc > Aug 11 11:34:53 my.host3 example.com3[16548]: [17198-1] 2016-08-11 11:34:53.529 MSK etc > Aug 11 11:34:53 my.host2 example.com2[19241]: [198-1] 2016-08-11 11:34:53.722 MSK etc > Aug 11 11:34:53 my.host2 example.com2[19017]: [198-1] 2016-08-11 11:34:53.873 MSK etc > Aug 11 11:34:54 my.host1 example.com1[21901]: [198-1] 2016-08-11 11:34:54.091 MSK etc Date, time, number of rows, PID process indication is the real, the rest of the data change for abstract. If task\_id = line, lines described above are merged into a single event and it's not right. If I use task\_id = pid, I have more bad result, because in a single PID handled many lines with different numbers. Can I use double task\_id (line and pid) ? How? ---
**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\ **Post date:** [August 11, 2016, 1:00pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/11 "2016-08-11T13:00:15Z")
Yes, you can ! It's easy : task\_id =\> "%{pid}%{line}" ---
**Author:** ![111126](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/111126/32/36818_2.png) [@111126](https://discuss.elastic.co/u/111126)\ **Post date:** [August 11, 2016, 1:47pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/12 "2016-08-11T13:47:15Z")
Oeeeah! Many Thanks. You did me a really big help. Great! ---
**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\ **Post date:** [August 11, 2016, 5:44pm UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/13 "2016-08-11T17:44:57Z")
Nice to see I helped you ! You're welcome ! ---
**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, 4:43am UTC](https://discuss.elastic.co/t/specific-grok-filter-for-multi-line-postgresql-log/56286/14 "2017-07-06T04:43:48Z")