Problem generating Grok from AWS Postgres logs

Elastic Stack Beats
1

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

# ============================== 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

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

In the section of the processors

# ================================= 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

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>
2

Hahahaha I solved my incognita.

The solution is in the 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:

# ============================== 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

# ======================= 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

# ================================= 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

3

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