Grok expressions do not match field value

[ajesh]

Hello,

I am trying to parse posgresql audit logs which is in csv format (sample line below)

2020-10-05 00:23:14.603 GMT,ixq_dc_monitor,postgres,11450,10.10.60.83:59536,5f7a6772.2cba,1,authentication,2020-10-05 00:23:14 GMT,131/795155,0,AUDIT,0,connection authorized: user=ixq_dc_monitor database=postgres,,,,,,,,,,edbaudit

below is the pipeline.yaml

description: Pipeline for parsing PostgreSQL audit logs.
processors:
- grok:
    field: message
    ignore_missing: true
    patterns:
    - '^%{TIMESTAMP_ISO8601:log_timestamp} %{DATA:timezone},%{DATA:user_name},%{DATA:database_name},%{NUMBER:process_id},%{HOSTPORT:connection_from},%{DATA:session_id},%{NUMBER:session_line_num},%{DATA:process_status},%{DATA:session_start_time},%{DATA:virtual_transaction_id},%{NUMBER:transaction_id},%{DATA:error_severity},%{NUMBER:sql_state_code},%{DATA:message},%{DATA:detail},%{DATA:hint},%{DATA:internal_query},%{DATA:internal_query_pos},%{DATA:context},%{DATA:query},%{DATA:query_pos},%{DATA:location},%{DATA:application_name},%{DATA:audit_tag}'


- date:
    field: log_timestamp
    target_field: '@timestamp'
    formats:
    - yyyy-MM-dd HH:mm:ss

- remove:
    field: ["message"]

    ignore_missing: true



on_failure:
- set:
    field: error.message
    value: '{{ _ingest.on_failure_message }}'

I am getting the error

Provided Grok expressions do not match field value: [2020-07-27 00:14:08.269 GMT,\"ixq_dc_monitor\",\"postgres\",20436,\"10.10.60.83:59061\",5f1e1c50.4fd4,3,\"SELECT\",2020-07-27 00:14:08 GMT,307/1000841,0,ERROR,42501,\"must be superuser to get directory listings\",,,,,,\"SELECT COUNT(*) FROM pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ '^[0-9A-F]{24}'\",,,\"\",\"edbaudit\"]

From Grok debugger its working fine. but not working via Filebeat. Please help

[mtojek (Marcin Tojek)]

Did you check in Elasticsearch if the correct pipeline is installed? You can use Simulate API to check if it parses correctly.

[ajesh]

Hi Marcin,

Instead of HOSTPORT, we used DATA and its working now , but getting an error like this below

all other fields are now getting parsed

We also tried in Simulate API and there its parsing without any error

[mtojek (Marcin Tojek)]

@ajesh

We also tried in Simulate API and there its parsing without any error

If the Simulate API works without any issues, please double check if the template in ES has the correct content.