# \[Logstash\] Grok Filter does not work \_grokparsefailure

**URL:** <https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823>\
**Category:** Logstash\
**Created:** [February 22, 2022, 1:49am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823 "2022-02-22T01:49:33Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 1:49am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/1 "2022-02-22T01:49:33Z")

</div>

Hi,

I am trying to parse this:

```auto
# 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:

```auto
# 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:

```auto
 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"  
],

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [February 22, 2022, 1:59am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/2 "2022-02-22T01:59:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 2:03am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/3 "2022-02-22T02:03:44Z")

</div>

Thank U

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [February 22, 2022, 2:09am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/4 "2022-02-22T02:09:34Z")

</div>

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",
```

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [February 22, 2022, 2:13am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/5 "2022-02-22T02:13:08Z")

</div>

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

test file

```auto
# 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

```auto
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

```auto
{
             "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"
}

```

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 2:45am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/6 "2022-02-22T02:45:42Z")

</div>

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? ☹

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.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [February 22, 2022, 3:05am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/7 "2022-02-22T03:05:29Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 3:13am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/8 "2022-02-22T03:13:23Z")

</div>

Hi @stephenb ,

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

```auto
{

  "_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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [February 22, 2022, 3:26am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/9 "2022-02-22T03:26:31Z")

</div>

Uncomment the

`stdout { codec => rubydebug }`

And show that output

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [February 22, 2022, 3:31am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/10 "2022-02-22T03:31:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 3:32am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/11 "2022-02-22T03:32:50Z")

</div>

Hi,

This is the output:

```auto
{
          "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"
    }
}

```

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 3:49am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/12 "2022-02-22T03:49:20Z")

</div>

Hi,

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

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [February 22, 2022, 3:59am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/13 "2022-02-22T03:59:43Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 4:47am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/14 "2022-02-22T04:47:58Z")

</div>

Hi,

I remove the \ from the \n only and I left the \[  
And it seems work. 🙃 😃 @Badger

```auto
{
                                 "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:

```auto
"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:

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

```

😐  
**Could you help me with this too?**

---

<div class="post-metadata">

**Author:** ![stephenb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/stephenb/32/40856_2.png) [@stephenb](https://discuss.elastic.co/u/stephenb)\
**Post date:** [February 22, 2022, 4:52am UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/15 "2022-02-22T04:52:10Z")

</div>

@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

```auto
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"
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Rossana](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rossana/32/94216_2.png) [@Rossana](https://discuss.elastic.co/u/Rossana)\
**Post date:** [February 22, 2022, 5:54pm UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/16 "2022-02-22T17:54:26Z")

</div>

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

Finally:  
This is the final grok pattern :

```auto
   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]};" }
    }

```

---

<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 22, 2022, 5:54pm UTC](https://discuss.elastic.co/t/logstash-grok-filter-does-not-work-grokparsefailure/297823/17 "2022-03-22T17:54:43Z")

</div>

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