[Logstash] Grok Filter does not work _grokparsefailure

Elastic Stack Logstash
1

Hi,

I am trying to parse this:

# Time: 2022-02-21T21:06:30.359422Z\n# User@Host: backoffice[backoffice] @  [0.0.0.0]  Id:  5373\n# Query_time: 13.690075  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 189879\nSET timestamp=1645477590;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650182975, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''20/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and s.order_amount = 514.06  order by s.trx_date DESC LIMIT 0, 10');"

With this pattern:

# Time: %{TIMESTAMP_ISO8601:aws_mysql.slowquery.date}\\n# User@Host: %{USER:aws_mysql.slowquery.user1}\[%{USER:aws_mysql.slowquery.user2}\] @  \[%{IP:aws_mysql.slowquery.ip:ip}\]  Id:  %{NUMBER:aws_mysql.slowquery.id:integer}\\n# Query_time: %{BASE16FLOAT:aws_mysql.slowquery.querytime}  Lock_time: %{BASE16FLOAT:aws_mysql.slowquery.locktime} Rows_sent: %{NUMBER:aws_mysql.slowquery.rows_sent:integer}  Rows_examined: %{NUMBER:aws_mysql.slowquery.rows_examined:integer}\\n%{GREEDYDATA:aws_mysql.slowquery.set_timestamp};\\n%{GREEDYDATA:aws_mysql.slowquery.query};

with this logstash configuration:

 filter {
  if [event][dataset] == "aws_mysql.slowquery" {
   grok {
     match => { "message" => "# Time: %{TIMESTAMP_ISO8601:aws_mysql.slowquery.date}\\n# User@Host: %{USER:aws_mysql.slowquery.user1}\[%{USER:aws_mysql.slowquery.user2}\] @  \[%{IP:aws_mysql.slowquery.ip:ip}\]  Id:  %{NUMBER:aws_mysql.slowquery.id:integer}\\n# Query_time: %{BASE16FLOAT:aws_mysql.slowquery.query_time:float}  Lock_time: %{BASE16FLOAT:aws_mysql.slowquery.lock_time:float} Rows_sent: %{NUMBER:aws_mysql.slowquery.rows_sent:integer}  Rows_examined: %{NUMBER:aws_mysql.slowquery.rows_examined:integer}\\n%{GREEDYDATA:aws_mysql.slowquery.set_timestamp};\\n%{GREEDYDATA:aws_mysql.slowquery.query}" }
     }
   }
 }

But the document that is storing does not have the fields and I dont know if I miss something. The tags indicate that it was a failure in grok filter.

"tags": [
"logstash-aws-niubiz",
"beats-input,
"beats_input_codec_plain_applied",
"_grokparsefailure"
],

2

Hi @Rossana

I formatted your code... please do that in the future otherwise it is very hard to read ... just select and use the </> button

Also please do not post images of text they are very hard to read and some people can not see them.. Can you post a text version of the screenshot... then perhaps we can help you.

3

Thank U

1 Like
4

Works fine for me.

"aws_mysql.slowquery.set_timestamp" => "SET timestamp=1645477590",
    "aws_mysql.slowquery.lock_time" => 0.0,
        "aws_mysql.slowquery.user1" => "backoffice",
   "aws_mysql.slowquery.query_time" => 13.690075,
           "aws_mysql.slowquery.ip" => "0.0.0.0",
1 Like
5

Works Fine for Me too ! @Badger beat me to it!

test file

# Time: 2022-02-21T21:06:30.359422Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5373\n# Query_time: 13.690075  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 189879\nSET timestamp=1645477590;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650182975, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''20/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and s.order_amount = 514.06  order by s.trx_date DESC LIMIT 0, 10');"
# Time: 2022-02-21T21:06:31.359422Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5373\n# Query_time: 13.690075  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 189879\nSET timestamp=1645477590;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650182975, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''20/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and s.order_amount = 514.06  order by s.trx_date DESC LIMIT 0, 10');"
# Time: 2022-02-21T21:06:32.359422Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5373\n# Query_time: 13.690075  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 189879\nSET timestamp=1645477590;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650182975, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''20/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and s.order_amount = 514.06  order by s.trx_date DESC LIMIT 0, 10');"
# Time: 2022-02-21T21:06:33.359422Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5373\n# Query_time: 13.690075  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 189879\nSET timestamp=1645477590;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650182975, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''20/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and s.order_amount = 514.06  order by s.trx_date DESC LIMIT 0, 10');"

test logstash.conf

input {
  file {
    path => "/Users/sbrown/workspace/sample-data/discuss/slowlog/slowlog.txt"
    start_position => "beginning"
    sincedb_path => "/dev/null"
  }
}

filter {
   grok {
     match => { "message" => "# Time: %{TIMESTAMP_ISO8601:aws_mysql.slowquery.date}\\n# User@Host: %{USER:aws_mysql.slowquery.user1}\[%{USER:aws_mysql.slowquery.user2}\] @  \[%{IP:aws_mysql.slowquery.ip:ip}\]  Id:  %{NUMBER:aws_mysql.slowquery.id:integer}\\n# Query_time: %{BASE16FLOAT:aws_mysql.slowquery.query_time:float}  Lock_time: %{BASE16FLOAT:aws_mysql.slowquery.lock_time:float} Rows_sent: %{NUMBER:aws_mysql.slowquery.rows_sent:integer}  Rows_examined: %{NUMBER:aws_mysql.slowquery.rows_examined:integer}\\n%{GREEDYDATA:aws_mysql.slowquery.set_timestamp};\\n%{GREEDYDATA:aws_mysql.slowquery.query}" }
     }
   
   mutate {
      remove_field => [ "message" ]
   }
}

output {
  stdout {codec => rubydebug}
  
  # elasticsearch {
  #   hosts => ["localhost:9200"]
  #   index => "my-discuss-slowlog"
  # }
}

output

{
             "aws_mysql.slowquery.date" => "2022-02-21T21:06:32.359422Z",
        "aws_mysql.slowquery.rows_sent" => "0",
       "aws_mysql.slowquery.query_time" => 13.690075,
            "aws_mysql.slowquery.query" => "call sp_execute_sql_on_server('select  s.id,\\r\\ns.channel,\\r\\ns.capture_type,\\r\\ns.ruc,\\r\\ns.commerce_id,\\r\\nc.`name` as commerce_name, \\r\\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\\r\\ns.id_resolutor,\\r\\ns.id_procesador,\\r\\ns.trx_date,\\r\\ns.registerFrequent,\\r\\ns.useFrequent,\\r\\ns.po_number,\\r\\ns.po_ext_number,\\r\\ns.shop_po_number,\\r\\ns.global_po_number,\\r\\ns.currency,\\r\\ns.order_amount, \\r\\ns.auth_payment,\\r\\ns.settled_amount,\\r\\ns.bin,\\r\\ns.pan,\\r\\ns.panEnc,\\r\\ns.expEnc,\\r\\ns.cardholder,\\r\\ns.cardholder_email,\\r\\ns.sub_product,\\r\\ns.state,\\r\\ns.action_code,\\r\\ns.authorization_code,\\r\\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\\r\\ns.authorization_date,\\r\\ns.annulation_trace,\\r\\ns.annulation_date,\\r\\ns.annulation_reason,\\r\\ns.cavv,\\r\\ns.eci,\\r\\ns.xid,\\r\\ns.cavv_response,\\r\\ns.cvv2_response,\\r\\ns.verification_code,\\r\\nc.mcc,\\r\\ns.bank,\\r\\ns.entity_id,\\r\\ns.pago_link_id,\\r\\ns.billing_address,\\r\\ns.aditionalFieldType,\\r\\ns.confirmation_date,\\r\\ns.countable,\\r\\ns.acquirer,\\r\\ns.brand,\\r\\ns.expiration_date,\\r\\ndccIndicator,\\r\\ndccEligibilityCode,\\r\\ndccCurrencyCode,\\r\\ndccCurrencyCodeAlpha,\\r\\ndccAmount,\\r\\ndccExponent,\\r\\ndccExchangeRate,\\r\\ndccWholeSaleRate,\\r\\ndccMarkup,\\r\\ndccRateSource,\\r\\ndccRateDateTime,\\r\\nquotaNumber,\\r\\nquotaAmount,\\r\\nquotaDeferred,\\r\\nnoInterestProgram,\\r\\nnoInterestType,\\r\\nnoInterestAmount,\\r\\nnoInterestDiscount,\\r\\nnoInterestMessage,\\r\\nsignature,\\r\\nts.`user` as user,\\r\\nts.`name` as name,\\r\\nts.`lastName` as lastName,\\r\\ns.vaultBlock,\\r\\ns.additionalData,\\r\\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\\r\\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \\r\\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\\r\\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \\r\\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \\r\\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \\r\\nand s.commerce_id = CONVERT(650182975, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''20/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and s.order_amount = 514.06  order by s.trx_date DESC LIMIT 0, 10');\"",
            "aws_mysql.slowquery.user1" => "backoffice",
                                 "host" => "hyperion",
               "aws_mysql.slowquery.ip" => "10.27.10.23",
            "aws_mysql.slowquery.user2" => "backoffice",
               "aws_mysql.slowquery.id" => "5373",
        "aws_mysql.slowquery.lock_time" => 0.0,
    "aws_mysql.slowquery.set_timestamp" => "SET timestamp=1645477590",
                                 "path" => "/Users/sbrown/workspace/sample-data/discuss/slowlog/slowlog.txt",
                           "@timestamp" => 2022-02-22T02:11:11.613Z,
                             "@version" => "1",
    "aws_mysql.slowquery.rows_examined" => "189879"
}
1 Like
6

Hi,

Thanks @stephenb @Badger but…

I know the grok filter works because I tested on the grok debuguer of kibana. Then why it gives me the grokfailure when I configure on logstash? :frowning:

This is an event of aws_cloudwatch input and I took the message only.

This is the flow
Filebeat -> logstash -> ECS
I also tested the output on the console executing logstash manually and its the same result.

7

Can you post in the entire document after the failed parsing...

I thought I saw in the message that it was an array.

Can you paste in that whole failed result that you had the image of before and format it?.

Oh, and you're using AWS cloud watch. It may be a grok parse failure in the ingest pipeline, not your logstash.

What does the output section of your logstash configuration look like?

8

Hi @stephenb ,

This is the document.
I took it from Discover, json format.

{

  "_index": "filebeat-103963-8.0.0-000001",

  "_type": "_doc",

  "_id": "cpYVH38BvkCp1mWsiPPb",

  "_version": 1,

  "_score": 1,

  "_ignored": [

    "event.original"

  ],

  "_source": {

    "input": {

      "type": "aws-cloudwatch"

    },

    "tags": [

      "logstash-aws-niubiz",

      "beats-input",

      "beats_input_codec_plain_applied",

      "_grokparsefailure"

    ],

    "message": "# Time: 2022-02-22T01:39:21.322880Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5478\n# Query_time: 31.699714  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 133668\nSET timestamp=1645493961;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650161518, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and po_number = 776204  order by s.trx_date DESC LIMIT 0, 10');",

    "alp": 103963,

    "awscloudwatch": {

      "ingestion_time": "2022-02-22T01:39:23.000Z",

      "log_stream": "db-ro",

      "log_group": "/aws/rds/cluster/db-cluster/slowquery"

    },

    "log.file.path": "/aws/rds/cluster/db-cluster/slowquery/db-ro",

    "@version": "1",

    "cliente": "NIUBIZ",

    "@timestamp": "2022-02-22T01:39:21Z",

    "event": {

      "module": "aws_mysql",

      "dataset": "aws_mysql.slowquery",

      "original": "# Time: 2022-02-22T01:39:21.322880Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5478\n# Query_time: 31.699714  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 133668\nSET timestamp=1645493961;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650161518, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and po_number = 776204  order by s.trx_date DESC LIMIT 0, 10');",

      "id": "36695741557162726408880013473085916144739111147336368128",

      "ingested": "2022-02-22T01:39:36.496Z"

    },

    "cloud": {

      "region": "us-east-1",

      "provider": "aws"

    },

    "ecs": {

      "version": "8.0.0"

    },

    "host": {

      "mac": [

        "0e:18:b7:35:a0:85"

      ],

      "os": {

        "kernel": "5.10.82-83.359.amzn2.x86_64",

        "version": "2",

        "family": "redhat",

        "type": "linux",

        "name": "Amazon Linux",

        "codename": "Karoo",

        "platform": "amzn"

      },

      "architecture": "x86_64",

      "id": "b1b67b6e8de14a95b237e45b6e9a4296",

      "ip": [

        "10.25.64.149",

        "fe80::c18:b7ff:fe35:a085"

      ],

      "hostname": "srv-agente-elastic-mpos",

      "name": "srv-agente-elastic-mpos",

      "containerized": false

    },

    "agent": {

      "name": "srv-agente-elastic-mpos",

      "ephemeral_id": "33ed93b6-4129-40b3-a8e1-675f2ad341dc",

      "type": "filebeat",

      "version": "8.0.0",

      "id": "7dfac496-876a-43f9-a828-01fd474d98ad"

    }

  },

  "fields": {

    "awscloudwatch.log_stream": [

      "db-ro"

    ],

    "host.os.name.text": [

      "Amazon Linux"

    ],

    "host.hostname": [

      "srv-agente-elastic-mpos"

    ],

    "host.mac": [

      "0e:18:b7:35:a0:85"

    ],

    "host.ip": [

      "10.25.64.149",

      "fe80::c18:b7ff:fe35:a085"

    ],

    "agent.type": [

      "filebeat"

    ],

    "event.module": [

      "aws_mysql"

    ],

    "awscloudwatch.ingestion_time": [

      "2022-02-22T01:39:23.000Z"

    ],

    "host.os.version": [

      "2"

    ],

    "host.os.kernel": [

      "5.10.82-83.359.amzn2.x86_64"

    ],

    "@version": [

      "1"

    ],

    "awscloudwatch.log_group": [

      "/aws/rds/cluster/db-cluster/slowquery"

    ],

    "host.os.name": [

      "Amazon Linux"

    ],

    "agent.name": [

      "srv-agente-elastic-mpos"

    ],

    "host.name": [

      "srv-agente-elastic-mpos"

    ],

    "host.id": [

      "b1b67b6e8de14a95b237e45b6e9a4296"

    ],

    "host.os.type": [

      "linux"

    ],

    "cloud.region": [

      "us-east-1"

    ],

    "alp": [

      103963

    ],

    "host.os.codename": [

      "Karoo"

    ],

    "input.type": [

      "aws-cloudwatch"

    ],

    "agent.hostname": [

      "srv-agente-elastic-mpos"

    ],

    "message": [

      "# Time: 2022-02-22T01:39:21.322880Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5478\n# Query_time: 31.699714  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 133668\nSET timestamp=1645493961;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650161518, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''21/02/2022'',''%d/%m/%Y'')  and po_number = 776204  order by s.trx_date DESC LIMIT 0, 10');"

    ],

    "tags": [

      "logstash-aws-niubiz",

      "beats-input",

      "beats_input_codec_plain_applied",

      "_grokparsefailure"

    ],

    "host.architecture": [

      "x86_64"

    ],

    "cliente": [

      "NIUBIZ"

    ],

    "event.ingested": [

      "2022-02-22T01:39:36.496Z"

    ],

    "cloud.provider": [

      "aws"

    ],

    "@timestamp": [

      "2022-02-22T01:39:21.000Z"

    ],

    "agent.id": [

      "7dfac496-876a-43f9-a828-01fd474d98ad"

    ],

    "ecs.version": [

      "8.0.0"

    ],

    "host.containerized": [

      false

    ],

    "host.os.platform": [

      "amzn"

    ],

    "log.file.path": [

      "/aws/rds/cluster/db-cluster/slowquery/db-ro"

    ],

    "agent.ephemeral_id": [

      "33ed93b6-4129-40b3-a8e1-675f2ad341dc"

    ],

    "agent.version": [

      "8.0.0"

    ],

    "host.os.family": [

      "redhat"

    ],

    "event.id": [

      "36695741557162726408880013473085916144739111147336368128"

    ],

    "event.dataset": [

      "aws_mysql.slowquery"

    ]

  }

}

This is my logstash output:

output {
  elasticsearch {
     hosts => ["XXXX.us-east-1.aws.found.io:9243"]
     index => "filebeat-103963-8.0.0"
     user => "elastic"
     password => "pass"
   }
#   stdout { codec => rubydebug }
}

About this:

Oh, and you're using AWS cloud watch. It may be a grok parse failure in the ingest pipeline, not your logstash.

The _grokparsefailure appears only when I did configure the filters, not before.

9

Uncomment the

stdout { codec => rubydebug }

And show that output

10

I suspect the problem is that most of your [message] field appears to have DOS line endings \r\n, but also has a couple of \n embedded. Are they actually newlines, or is \\n really appropriate? You could try changing the \\n in your pattern to \n. The rubydebug output will make this clear.

1 Like
11

Hi,

This is the output:

{
          "message" => "# Time: 2022-02-22T03:31:09.845432Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5502\n# Query_time: 9.455460  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 81815\nuse backofficevn;\nSET timestamp=1645500669;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650186900, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''22/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''22/02/2022'',''%d/%m/%Y'')  order by s.trx_date DESC LIMIT 0, 10');",
             "host" => {
                   "ip" => [
            [0] "10.25.64.149",
            [1] "fe80::c18:b7ff:fe35:a085"
        ],
        "containerized" => false,
         "architecture" => "x86_64",
             "hostname" => "srv-agente-elastic-mpos",
                   "id" => "b1b67b6e8de14a95b237e45b6e9a4296",
                 "name" => "srv-agente-elastic-mpos",
                   "os" => {
            "codename" => "Karoo",
              "family" => "redhat",
            "platform" => "amzn",
                "name" => "Amazon Linux",
                "type" => "linux",
             "version" => "2",
              "kernel" => "5.10.82-83.359.amzn2.x86_64"
        },
                  "mac" => [
            [0] "0e:18:b7:35:a0:85"
        ]
    },
              "alp" => 103963,
            "agent" => {
                "name" => "srv-agente-elastic-mpos",
                "type" => "filebeat",
             "version" => "8.0.0",
                  "id" => "7dfac496-876a-43f9-a828-01fd474d98ad",
        "ephemeral_id" => "33ed93b6-4129-40b3-a8e1-675f2ad341dc"
    },
    "log.file.path" => "/aws/rds/cluster/db-cluster/slowquery/db-ro",
    "awscloudwatch" => {
            "log_stream" => "db-ro",
             "log_group" => "/aws/rds/cluster/db-cluster/slowquery",
        "ingestion_time" => "2022-02-22T03:31:13.000Z"
    },
          "cliente" => "NIUBIZ",
             "tags" => [
        [0] "logstash-aws-niubiz",
        [1] "beats-input",
        [2] "beats_input_codec_plain_applied",
        [3] "_grokparsefailure"
    ],
         "@version" => "1",
              "ecs" => {
        "version" => "8.0.0"
    },
       "@timestamp" => 2022-02-22T03:31:09Z,
            "input" => {
        "type" => "aws-cloudwatch"
    },
            "event" => {
        "original" => "# Time: 2022-02-22T03:31:09.845432Z\n# User@Host: backoffice[backoffice] @  [10.27.10.23]  Id:  5502\n# Query_time: 9.455460  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 81815\nuse backofficevn;\nSET timestamp=1645500669;\ncall sp_execute_sql_on_server('select  s.id,\r\ns.channel,\r\ns.capture_type,\r\ns.ruc,\r\ns.commerce_id,\r\nc.`name` as commerce_name, \r\nIF(s.id_procesador IS NULL OR s.id_procesador = '''',s.transaction_id, s.id_procesador) as transaction_id,\r\ns.id_resolutor,\r\ns.id_procesador,\r\ns.trx_date,\r\ns.registerFrequent,\r\ns.useFrequent,\r\ns.po_number,\r\ns.po_ext_number,\r\ns.shop_po_number,\r\ns.global_po_number,\r\ns.currency,\r\ns.order_amount, \r\ns.auth_payment,\r\ns.settled_amount,\r\ns.bin,\r\ns.pan,\r\ns.panEnc,\r\ns.expEnc,\r\ns.cardholder,\r\ns.cardholder_email,\r\ns.sub_product,\r\ns.state,\r\ns.action_code,\r\ns.authorization_code,\r\n(select IF(e.message IS NULL, s.authorization_message, e.message) from bo_error_messages  e where s.action_code = CONVERT( e.`code` USING UTF8)) AS authorization_message,\r\ns.authorization_date,\r\ns.annulation_trace,\r\ns.annulation_date,\r\ns.annulation_reason,\r\ns.cavv,\r\ns.eci,\r\ns.xid,\r\ns.cavv_response,\r\ns.cvv2_response,\r\ns.verification_code,\r\nc.mcc,\r\ns.bank,\r\ns.entity_id,\r\ns.pago_link_id,\r\ns.billing_address,\r\ns.aditionalFieldType,\r\ns.confirmation_date,\r\ns.countable,\r\ns.acquirer,\r\ns.brand,\r\ns.expiration_date,\r\ndccIndicator,\r\ndccEligibilityCode,\r\ndccCurrencyCode,\r\ndccCurrencyCodeAlpha,\r\ndccAmount,\r\ndccExponent,\r\ndccExchangeRate,\r\ndccWholeSaleRate,\r\ndccMarkup,\r\ndccRateSource,\r\ndccRateDateTime,\r\nquotaNumber,\r\nquotaAmount,\r\nquotaDeferred,\r\nnoInterestProgram,\r\nnoInterestType,\r\nnoInterestAmount,\r\nnoInterestDiscount,\r\nnoInterestMessage,\r\nsignature,\r\nts.`user` as user,\r\nts.`name` as name,\r\nts.`lastName` as lastName,\r\ns.vaultBlock,\r\ns.additionalData,\r\n(select bi.binType from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `binType`,\r\n(select bi.issuerId from ecommerce.Bines bi WHERE s.`bin` = bi.`binNumber`) AS `emisor` \r\nFROM bo_sales s FORCE INDEX (SALES_IX_004)\r\nLEFT JOIN bo_commerce c ON CAST(s.commerce_id AS CHAR) = c.`commerce_code` \r\nLEFT JOIN bo_telepago_sales ts ON s.`id` = ts.`idSale` \r\nWHERE s.state NOT IN (''Verified'',''Verified/Not Voided'',''Not Verified'') \r\nand s.commerce_id = CONVERT(650186900, UNSIGNED)  and DATE(s.trx_date) >= STR_TO_DATE(''22/02/2022'',''%d/%m/%Y'')  and DATE(s.trx_date) <= STR_TO_DATE(''22/02/2022'',''%d/%m/%Y'')  order by s.trx_date DESC LIMIT 0, 10');",
         "dataset" => "aws_mysql.slowquery",
              "id" => "36695891162224807891131570909577998395594566738075975680",
        "ingested" => "2022-02-22T03:32:09.324Z",
          "module" => "aws_mysql"
    },
            "cloud" => {
        "provider" => "aws",
          "region" => "us-east-1"
    }
}
12

Hi,

When I tested on the kibana grok debugguer it gives me error and I solve it adding the extra: \

1 Like
13

That would be a question about kibana. In logstash you would need \n.

Even though both grok and the grok debugger are written by the elastic team, I believe they are separate code bases and there are corner cases where they do not produce the same results. The last one I can remember was a multiline match for GREEDYDATA.

2 Likes
14

Hi,

I remove the \ from the \n only and I left the \[
And it seems work. :upside_down_face: :smiley: @Badger

{
                                 "host" => {
         "architecture" => "x86_64",
                   "id" => "b1b67b6e8de14a95b237e45b6e9a4296",
                   "os" => {
              "kernel" => "5.10.82-83.359.amzn2.x86_64",
            "platform" => "amzn",
            "codename" => "Karoo",
                "name" => "Amazon Linux",
              "family" => "redhat",
             "version" => "2",
                "type" => "linux"
        },
             "hostname" => "srv-agente-elastic-mpos",
                 "name" => "srv-agente-elastic-mpos",
                  "mac" => [
            [0] "0e:18:b7:35:a0:85"
        ],
                   "ip" => [
            [0] "10.25.64.149",
            [1] "fe80::c18:b7ff:fe35:a085"
        ],
        "containerized" => false
    },
                                "agent" => {
                "name" => "srv-agente-elastic-mpos",
             "version" => "8.0.0",
                  "id" => "7dfac496-876a-43f9-a828-01fd474d98ad",
                "type" => "filebeat",
        "ephemeral_id" => "33ed93b6-4129-40b3-a8e1-675f2ad341dc"
    },
                             "@version" => "1",
                        "awscloudwatch" => {
             "log_group" => "/aws/rds/cluster/db-cluster/slowquery",
            "log_stream" => "db-ro",
        "ingestion_time" => "2022-02-22T04:10:53.000Z"
    },
        "aws_mysql.slowquery.lock_time" => 0.000148,
                                "event" => {
        "original" => "# Time: 2022-02-22T04:10:52.894454Z\n# User@Host: ecore[ecore] @  [34.195.80.178]  Id:  5515\n# Query_time: 31.798795  Lock_time: 0.000148 Rows_sent: 0  Rows_examined: 720237\nSET timestamp=1645503052;\nselect * from backofficevn.bo_sales\r\nwhere acquirer = 'sodexo' \r\norder by trx_date desc\r\nlimit 100;",
        "ingested" => "2022-02-22T04:12:06.554Z",
              "id" => "36695944305993352504334520600271697191465537575894646784",
          "module" => "aws_mysql",
         "dataset" => "aws_mysql.slowquery"
    },
                        "log.file.path" => "/aws/rds/cluster/db-cluster/slowquery/db-ro",
        "aws_mysql.slowquery.rows_sent" => "0",
                                 "tags" => [
        [0] "logstash-aws-niubiz",
        [1] "beats-input",
        [2] "beats_input_codec_plain_applied"
    ],
               "aws_mysql.slowquery.ip" => "34.195.80.178",
            "aws_mysql.slowquery.query" => "select * from backofficevn.bo_sales\r\nwhere acquirer = 'sodexo' \r\norder by trx_date desc\r\nlimit 100",
                           "@timestamp" => 2022-02-22T04:10:52Z,
                                "cloud" => {
          "region" => "us-east-1",
        "provider" => "aws"
    },
                              "message" => "# Time: 2022-02-22T04:10:52.894454Z\n# User@Host: ecore[ecore] @  [34.195.80.178]  Id:  5515\n# Query_time: 31.798795  Lock_time: 0.000148 Rows_sent: 0  Rows_examined: 720237\nSET timestamp=1645503052;\nselect * from backofficevn.bo_sales\r\nwhere acquirer = 'sodexo' \r\norder by trx_date desc\r\nlimit 100;",
                                "input" => {
        "type" => "aws-cloudwatch"
    },
       "aws_mysql.slowquery.query_time" => 31.798795,
               "aws_mysql.slowquery.id" => "5515",
                                  "alp" => 103963,
            "aws_mysql.slowquery.user1" => "ecore",
            "aws_mysql.slowquery.user2" => "ecore",
                                  "ecs" => {
        "version" => "8.0.0"
    },
    "aws_mysql.slowquery.set_timestamp" => "SET timestamp=1645503052",
                              "cliente" => "NIUBIZ",
    "aws_mysql.slowquery.rows_examined" => "720237",
             "aws_mysql.slowquery.date" => "2022-02-22T04:10:52.894454Z"
}

But I have another problem.
I want to parse the data like this:

"aws_mysql.slowquery" => {
        "set_timestamp" => "SET timestamp=1645503052",
                               "id" => 5515,
                        "user1" => "ecore",
		  "user2" => "ecore",
		   "query_time" => 31.798795,
	    "rows_examined" => 720237,
		         "date" => "2022-02-22T04:10:52.894454Z",
				"query" => "select * from backofficevn.bo_sales\r\nwhere acquirer = 'sodexo' \r\norder by trx_date desc\r\nlimit 100",
				   "ip" => 34.195.80.178
    }

And It parses like this:

"aws_mysql.slowquery.query_time" => 31.798795,
               "aws_mysql.slowquery.id" => "5515",
            "aws_mysql.slowquery.user1" => "ecore",
            "aws_mysql.slowquery.user2" => "ecore",

:neutral_face:
Could you help me with this too?

15

@Rossana

Also if you want to call the the ingest pipeline which will do a little more parsing you can do this

Actually if you want to use the normal filebeat index names and execute the ingest pipelines if they exist your output sections should look something like

output {
  if [@metadata][pipeline] {
    elasticsearch {
      hosts => "http://localhost:9200"
      manage_template => false
      index => "%{[@metadata][beat]}-%{[@metadata][version]}"  <!---- the index writer alias
      pipeline => "%{[@metadata][pipeline]}" <!-- Calls ingest pipeline if exist
      user => "elastic"
      password => "secret"
    }
  } else {
    elasticsearch {
      hosts => "http://localhost:9200"
      manage_template => false
      index => "%{[@metadata][beat]}-%{[@metadata][version]}"
      user => "elastic"
      password => "secret"
    }
  }
}
1 Like
16

Hi,
Thank U for the recomendation and for your help so quickly.
@Badger @stephenb

Finally:
This is the final grok pattern :

   grok {
     match => { "message" => "# Time: %{TIMESTAMP_ISO8601:[aws_mysql][slowquery][fecha]:date}\n# User@Host: %{USER:[aws_mysql][slowquery][user1]}\[%{USER:[aws_mysql][slowquery][user2]}\] @  \[%{IP:[aws_mysql][slowquery][query_ip]}\]  Id:  %{NUMBER:[aws_mysql][slowquery][id]:int}\n# Query_time: %{BASE16FLOAT:[aws_mysql][slowquery][query_time]:float}  Lock_time: %{BASE16FLOAT:[aws_mysql][slowquery][lock_time]:float} Rows_sent: %{NUMBER:[aws_mysql][slowquery][rows_sent]:int}  Rows_examined: %{NUMBER:[aws_mysql][slowquery][rows_examined]:int}\n%{GREEDYDATA:[aws_mysql][slowquery][set_timestamp]};\n%{GREEDYDATA:[aws_mysql][slowquery][query]};" }
    }
1 Like
17

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