Grok pattern match for multiple lines

Elastic Stack Logstash
1

I am reading a postgresql log file in and some lines are pretty big and come in on multiple lines. Any one have any idea on how to match this pattern?

grok pattern:

(%{TIMESTAMP_ISO8601:timestamp}%{SPACE}%{TZ:time_zone})?,(%{DATA:user_name})?,(%{DATA:database_name})?,(%{NUMBER:process_id})?,("%{DATA:connection_from}")?,(%{USERNAME:session_id})?,(%{NUMBER:session_line_num})?,("%{DATA:command_tag}")?,(%{TIMESTAMP_ISO8601:timestamp2}%{SPACE}%{TZ:time_zone2})?,(%{DATA:virtual_transaction_id})?,(%{DATA:transaction_id})?,(%{DATA:error_severity})?,(%{DATA:sql_state_code})?,"duration:%{SPACE}%{NUMBER:duration:float}%{SPACE}ms%{SPACE}statement:%{SPACE}%{GREEDYDATA:statement}",(%{DATA:detail})?,(%{DATA:hint})?,(%{DATA:internal_query})?,(%{DATA:internal_query_pos})?,(%{DATA:context})?,(%{DATA:query})?,(%{DATA:query_pos})?,(%{DATA:location})?,(%{GREEDYDATA:application_name})?

sample log output failing:

2020-04-16 21:00:03.090 UTC,"app_program_service","program_service",20926,"10.124.192.165:51086",5e98c6eb.51be,85,"SELECT",2020-04-16 20:58:19 UTC,10/12373928,0,LOG,00000,"duration: 9.683 ms  statement:
select p.id as program_id,
       p.cms_program_guid,
       p.name as program_name,
       p.description as program_description,
       p.episode_number,
       p.series_sequence,
       p.type as program_type,
       p.genre,
       p.ratings,
       p.rating_reasons,
       p.release_year,
       p.grid_title,
       p.character,
       p.subject,
       p.setting,
       p.time_period,
       p.mood,
       p.theme,
       p.advisories,
       p.warnings,
       p.category,
       p.rt_score,
       p.image as program_image,
       (select array_to_json(array_agg(row_to_json(t))) from (
          select key, value from program_lookup where program_id=p.id) t) as program_lookups,
       s.id as season_id,
       s.name as season_name,
       s.number as season_number,
       s.image as season_image,
       s.description as season_description,
       f.id as franchise_id,
       f.name as franchise_name,
       f.franchise_url as franchise_url,
       f.image as franchise_image,
       f.description as franchise_description,
       f.guid AS cms_franchise_guid,
       (SELECT DISTINCT ON (franchise_id) VALUE
          FROM franchise_lookup
         WHERE key IN ('cms_franchise_id', 'rovi_series_id')
           AND franchise_id = f.id
         ORDER BY franchise_id, key asc, cl desc, value) AS cms_franchise_id,
       (select array_to_json(array_agg(row_to_json(e))) from (
          select c.id as celebrity_id, c.display_name, c.image, c.first_name, c.last_name, c.birth_year
            from celebrity c
             inner join program_celebrity pc on c.id=pc.celebrity_id
            where pc.program_id=p.id AND c.guid IS NOT NULL) e) as celebrities,
       (select array_to_json(array_agg(row_to_json(tm))) from (
          select t.name, t.sport, t.league, t.sub_league_1, t.sub_league_2, t.gender
            from team t
             inner join program_team pt on t.id=pt.team_id
            where pt.program_id=p.id and t.guid is not null ) tm) as teams,
       GREATEST(p._modified, COALESCE(s._modified, '-infinity'::timestamp), COALESCE(f._modified,
                '-infinity'::timestamp)) as _modified

          from program p
            inner join program_lookup pl on p.id=pl.program_id
            left join season s on p.season_id = s.id and s.franchise_id = p.franchise_id
            left join franchise f on p.franchise_id = f.id
          where pl.key='cms_program_guid' and pl.value='706eb09f7e7a44a28f99a26d3a956fbd'",,,,,,,,,""
2

If you want a pattern to match multiple lines then add (?m) at the start of the pattern.

3

that worked, thanks

4

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