# Problem generating Grok from AWS Postgres logs

**URL:** https://discuss.elastic.co/t/problem-generating-grok-from-aws-postgres-logs/326100
**Category:** Beats
**Tags:** filebeat
**Created:** [February 21, 2023, 7:15pm UTC](https://discuss.elastic.co/t/problem-generating-grok-from-aws-postgres-logs/326100 "2023-02-21T19:15:45Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![ingri.mahecha](https://avatars.discourse-cdn.com/v4/letter/i/51bf81/32.png) [@ingri.mahecha](https://discuss.elastic.co/u/ingri.mahecha)
#### Post date: [February 21, 2023, 7:15pm UTC](https://discuss.elastic.co/t/problem-generating-grok-from-aws-postgres-logs/326100/1 "2023-02-21T19:15:45Z")

</div>

Hi, I'm having trouble generating the GROK of AWS logs from the same elasticsearch configuration, as well as from the **filebeat.yml** file.

```auto
# ============================== Filebeat inputs ===============================
filebeat.inputs:
- type: aws-cloudwatch
  log_group_arn: arn:aws:logs:URL/postgresql:*
  scan_frequency: 1m
  #credential_profile_name: elastic-beats
  access_key_id: 'key'
  secret_access_key: 'access_key'
  start_position: end  
 

```

I have the console output to be able to visualize

```auto
# ================================== Outputs ===================================
 
output.console:
  pretty: true

```

In the section of the processors

```auto
# ================================= Processors =================================
processors:
grok:
  match:
    message: '%{TIMESTAMP_ISO8601:awscloudwatch.postgresql.log_timestamp}.*:%{IP:awscloudwatch.postgresql.log_client_addr}.*\(%{DATA:awscloudwatch.postgresql.log_port}\):%{DATA:awscloudwatch.postgresql.log_user}@%{GREEDYDATA:awscloudwatch.postgresql.log_database}.*:\[%{DATA:wscloudwatch.postgresql.log_core_id}}\]:%{DATA:awscloudwatch.postgresql.log_level}\:*SESSION,%{NUMBER:awscloudwatch.postgresql.log_session_id}.*WRITE,%{DATA:awscloudwatch.postgresql.log_command_tag},,,\\%{GREEDYDATA:awscloudwatch.postgresql.log_query_name}.*'
        
grok:
  match:
    message: '%{TIMESTAMP_ISO8601:awscloudwatch.postgresql.log_timestamp}.*:%{IP:awscloudwatch.postgresql.log_client_addr}.*\(%{DATA:awscloudwatch.postgresql.log_port}\):%{DATA:awscloudwatch.postgresql.log_user}@%{GREEDYDATA:awscloudwatch.postgresql.log_database}.*:\[%{DATA:awscloudwatch.postgresql.log_core_id}\]:%{DATA:awscloudwatch.postgresql.log_level}\:.*\nSESSION,%{NUMBER:awscloudwatch.postgresql.log_session_id}.*WRITE,%{DATA:awscloudwatch.postgresql.log_command_tag},,,\\%{GREEDYDATA:awscloudwatch.postgresql.log_query_name}.*'

grok:
  match:
    message: '%{TIMESTAMP_ISO8601:awscloudwatch.postgresql.log_timestamp}.*:%{IP:awscloudwatch.postgresql.log_client_addr}.*\(%{DATA:awscloudwatch.postgresql.log_port}\):%{DATA:awscloudwatch.postgresql.log_user}@%{GREEDYDATA:awscloudwatch.postgresql.log_database}.*:\[%{DATA:awscloudwatch.postgresql.log_core_id}\]:%{DATA:awscloudwatch.postgresql.log_level}\:.*SESSION,%{NUMBER:awscloudwatch.postgresql.log_session_id}.*WRITE,%{WORD:awscloudwatch.postgresql.log_command_tag}.*,,,(?<awscloudwatch.postgresql.log_query_name>(.|\r|\n)*)'

```

**The example log**

```auto
2023-02-21 19:01:11 UTC:127.0.0.0(00000):user@Data_base_db:[12345]:LOG: AUDIT: SESSION,1,1,WRITE,UPDATE,,,\"UPDATE \"\"data_base_name\"\" SET \"\"created\"\" = '2023-02-21T20:01:11.584950+00:00'::timestamptz WHERE \"\"data_base_name\"\".\"\"key\"\" = 'fffffffffffffffffffffffffffffffffffffffff'\",<not logged>

```

---

<div class="post-metadata">

### Author: ![ingri.mahecha](https://avatars.discourse-cdn.com/v4/letter/i/51bf81/32.png) [@ingri.mahecha](https://discuss.elastic.co/u/ingri.mahecha)
#### Post date: [March 1, 2023, 5:13pm UTC](https://discuss.elastic.co/t/problem-generating-grok-from-aws-postgres-logs/326100/2 "2023-03-01T17:13:11Z")

</div>

Hahahaha I solved my incognita.

The solution is in the [Processors](https://www.elastic.co/guide/en/beats/filebeat/current/filtering-and-enhancing-data.html#using-processors) of filebeat, which work similar to grok, unlike grok this one does not allow some special characters to omit certain parameters.

It looks like this:

```auto
# ============================== Filebeat inputs ===============================

filebeat.inputs:         
- type: aws-cloudwatch
  enabled: true
  access_key_id: 'key'
  secret_access_key: 'secrect_key'
  log_group_arn: "arn:aws:###:/aws/rds/BD/postgresql:*"
  start_position: end 

```

in the templates

```auto
# ======================= Elasticsearch template setting =======================

setup.template.settings:
  index.number_of_shards: 1
  #index.codec: best_compression
  #_source.enabled: false
setup.template.name: "logs-awspostgresql-default"
setup.template.pattern: "logs-awspostgresql-default"
setup.ilm.enabled: false
output.elasticsearch.index: "logs-awspostgresql-default"

```

in the processors part of the **filebeat.yml** file

```auto
# ================================= Processors =================================
processors:
  - add_host_metadata:
      when.not.contains.tags: forwarded
  - add_cloud_metadata: ~
  - add_docker_metadata: ~
  - add_kubernetes_metadata: ~    
  - dissect:
      tokenizer: "%{awspostgresql.log.timestamp} UTC:%{awspostgresql.log.client_addr}(%{awspostgresql.log.core_id}):%{awspostgresql.log.user}@%{awspostgresql.log.database}:[%{awspostgresql.log.session_id}]:%{awspostgresql.log.level}:%{},WRITE,%{awspostgresql.log.command_tag},,,%{awspostgresql.log.query_name}"
      field: "message"
      #target_prefix: "dissect"
      target_prefix: ""

  - dissect:
      tokenizer: "%{awspostgresql.log.timestamp} UTC:%{awspostgresql.log.client_addr}(%{awspostgresql.log.core_id}):%{awspostgresql.log.user}@%{awspostgresql.log.database}:[%{awspostgresql.log.session_id}]:%{awspostgresql.log.level}:%{},DDL,%{awspostgresql.log.command_tag},,,%{awspostgresql.log.query_name}"
      field: "message"
      #target_prefix: "dissect"
      target_prefix: ""

  - dissect:
      tokenizer: "%{awspostgresql.log.timestamp} UTC:%{awspostgresql.log.client_addr}(%{awspostgresql.log.core_id}):%{awspostgresql.log.user}@%{awspostgresql.log.database}:[%{awspostgresql.log.session_id}]:%{awspostgresql.log.level}: %{awspostgresql.log.detail}"
      field: "message"
      #target_prefix: "dissect"
      target_prefix: ""

  - drop_fields:      
      fields: ["host","tags"]
      ignore_missing: false

  - timestamp:
      field: event.ingested
      target_field: "@timestamp"
      layouts:
        - "yyyy-MM-ddTHH:mm:ssZ"
        - "yyyy-MM-ddTHH:mm:ss.SSSZ"
        - "yyyy-MM-ddTHH:mm:ss.SSS-07:00"
      test:
        - "yyyy-MM-ddTHH:mm:ssZ"
        - "yyyy-MM-ddTHH:mm:ss.SSSZ"
        #- "yyyy-MM-dd HH:mm:ss.SSS"
  - drop_fields:
      fields: [event.ingested]

  - timestamp:
      field: awspostgresql.log.timestamp 
      timezone: "UTC"
      layouts:
      - "yyyy-MM-dd HH:mm:ss.SSSSSS"

```

References

- [Filter and enhance data with processors](https://www.elastic.co/guide/en/beats/filebeat/current/filtering-and-enhancing-data.html#filtering-and-enhancing-data)

- [Dissect strings](https://www.elastic.co/guide/en/beats/filebeat/current/dissect.html#dissect)

---

<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: [March 29, 2023, 7:13pm UTC](https://discuss.elastic.co/t/problem-generating-grok-from-aws-postgres-logs/326100/3 "2023-03-29T19:13:39Z")

</div>

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