# Logstash grok pattern question when sql statement has commas in it
**URL:** https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022
**Category:** Logstash
**Created:** [April 7, 2020, 10:50pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022 "2020-04-07T22:50:28Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@kyle\_che](https://discuss.elastic.co/u/kyle_che)
#### Post date: [April 7, 2020, 10:50pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022/1 "2020-04-07T22:50:28Z")
I am trying to get a grok pattern to match the following and i have it besides the fact that i had to leave a field out to get it done. this is postgresql csv pattern and i needed to splice out the duration from the query. you can see that i did this by having a duration field and then the statement field ending with a double comma ,, anyone know how to not have to do a ,, and to be able to get the duration and query statement split out? between the ,, should be a field named "detail" which would be empty in this case. right now i just don't have a detail field but would like to have it.
```auto
2020-04-07 22:32:04.673 UTC,"app_keystore","keystore",14220,"[local]",5e8cf4b2.378c,1748710,"SELECT",2020-04-07 21:46:26 UTC,5/0,0,LOG,00000,"duration: 0.148 ms statement: SELECT ""keystore_app_keyconfig"".""name"", ""keystore_app_keyconfig"".""usage"", ""keystore_app_keyconfig"".""lifetime"", ""keystore_app_keyconfig"".""band_id"", ""keystore_app_keyconfig"".""rotation_method"", ""keystore_app_keyconfig"".""seg_size"", ""keystore_app_keyconfig"".""rotation_time"", ""keystore_app_keyconfig"".""pre_create_keys"", ""keystore_app_keyconfig"".""notes"" FROM ""keystore_app_keyconfig"" WHERE ""keystore_app_keyconfig"".""name"" = 'qa_unit_tests'",,,,,,,,,"app - 10.124.193.84:33116"
(%{TIMESTAMP_ISO8601:timestamp}%{SPACE}%{TZ:time_zone})?,(%{DATA:user_name})?,(%{DATA:database_name})?,(%{NUMBER:process_id})?,("\[%{DATA:connection_from}\]")?,(%{USERNAME:session_id})?,(%{NUMBER:session_line_num})?,("%{DATA:command_tag}")?,(%{TIMESTAMP_ISO8601:timestamp2}%{SPACE}%{TZ:time_zone2})?,(%{DATA:virtual_transaction_id})?,(%{DATA:transaction_id})?,(%{DATA:error_severity})?,(%{NUMBER:sql_state_code})?,"duration:%{SPACE}%{NUMBER:duration}%{SPACE}ms%{SPACE}statement:%{SPACE}%{DATA:statement}",,(%{DATA:hint})?,(%{DATA:internal_query})?,(%{DATA:internal_query_pos})?,(%{DATA:context})?,(%{DATA:query})?,(%{DATA:query_pos})?,(%{DATA:location})?,(%{GREEDYDATA:application_name})?
```
---
### Author:  [@kyle\_che](https://discuss.elastic.co/u/kyle_che)
#### Post date: [April 7, 2020, 10:53pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022/2 "2020-04-07T22:53:06Z")
the output from grok debugger is:
```auto
{
"process_id": "14220",
"time_zone2": "UTC",
"error_severity": "LOG",
"sql_state_code": "00000",
"user_name": "\"app_keystore\"",
"timestamp2": "2020-04-07 21:46:26",
"command_tag": "SELECT",
"query_pos": "",
"duration": "0.148",
"application_name": "\"app - 10.124.193.84:33116\"",
"statement": "SELECT \"\"keystore_app_keyconfig\"\".\"\"name\"\", \"\"keystore_app_keyconfig\"\".\"\"usage\"\", \"\"keystore_app_keyconfig\"\".\"\"lifetime\"\", \"\"keystore_app_keyconfig\"\".\"\"band_id\"\", \"\"keystore_app_keyconfig\"\".\"\"rotation_method\"\", \"\"keystore_app_keyconfig\"\".\"\"seg_size\"\", \"\"keystore_app_keyconfig\"\".\"\"rotation_time\"\", \"\"keystore_app_keyconfig\"\".\"\"pre_create_keys\"\", \"\"keystore_app_keyconfig\"\".\"\"notes\"\" FROM \"\"keystore_app_keyconfig\"\" WHERE \"\"keystore_app_keyconfig\"\".\"\"name\"\" = 'qa_unit_tests'",
"context": "",
"internal_query": "",
"timestamp": "2020-04-07 22:32:04.673",
"transaction_id": "0",
"database_name": "\"keystore\"",
"query": "",
"session_id": "5e8cf4b2.378c",
"session_line_num": "1748710",
"time_zone": "UTC",
"internal_query_pos": "",
"virtual_transaction_id": "5/0",
"connection_from": "local",
"hint": "",
"location": ""
}
```
---
### Author:  [@kyle\_che](https://discuss.elastic.co/u/kyle_che)
#### Post date: [April 7, 2020, 10:56pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022/3 "2020-04-07T22:56:28Z")
currently looks like:
```auto
%{NUMBER:duration}%{SPACE}ms%{SPACE}statement:%{SPACE}%{DATA:statement}",,(%{DATA:hint})?
```
changed to this:
```auto
%{NUMBER:duration}%{SPACE}ms%{SPACE}statement:%{SPACE}%{DATA:statement}",(%{DATA:detail})?,(%{DATA:hint})?
```
output looks like this but it is now all jumbled:
```auto
{
"process_id": "14220",
"time_zone2": "UTC",
"error_severity": "LOG",
"sql_state_code": "00000",
"user_name": "\"app_keystore\"",
"timestamp2": "2020-04-07 21:46:26",
"command_tag": "SELECT",
"query_pos": " \"\"keystore_app_keyconfig\"\".\"\"pre_create_keys\"\"",
"duration": "0.148",
"application_name": ",,,,,,,,\"app - 10.124.193.84:33116\"",
"statement": "SELECT \"\"keystore_app_keyconfig\"\".\"\"name\"",
"context": " \"\"keystore_app_keyconfig\"\".\"\"seg_size\"\"",
"internal_query": " \"\"keystore_app_keyconfig\"\".\"\"band_id\"\"",
"timestamp": "2020-04-07 22:32:04.673",
"transaction_id": "0",
"database_name": "\"keystore\"",
"query": " \"\"keystore_app_keyconfig\"\".\"\"rotation_time\"\"",
"session_id": "5e8cf4b2.378c",
"session_line_num": "1748710",
"time_zone": "UTC",
"internal_query_pos": " \"\"keystore_app_keyconfig\"\".\"\"rotation_method\"\"",
"virtual_transaction_id": "5/0",
"connection_from": "local",
"hint": " \"\"keystore_app_keyconfig\"\".\"\"lifetime\"\"",
"location": " \"\"keystore_app_keyconfig\"\".\"\"notes\"\" FROM \"\"keystore_app_keyconfig\"\" WHERE \"\"keystore_app_keyconfig\"\".\"\"name\"\" = 'qa_unit_tests'\"",
"detail": " \"\"keystore_app_keyconfig\"\".\"\"usage\"\""
}
```
---
### Author:  [@kyle\_che](https://discuss.elastic.co/u/kyle_che)
#### Post date: [April 7, 2020, 10:57pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022/4 "2020-04-07T22:57:32Z")
having the commas in the sql statement throws this grok pattern off and not sure how to get around this so that i capture all of the fields.
---
### Author:  [@kyle\_che](https://discuss.elastic.co/u/kyle_che)
#### Post date: [April 8, 2020, 8:55pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022/5 "2020-04-08T20:55:09Z")
i was able to figure it out. i just needed to use greedydata in my statement field and was then able to add my detail field.
```auto
(%{TIMESTAMP_ISO8601:timestamp}%{SPACE}%{TZ:time_zone})?,(%{DATA:user_name})?,(%{DATA:database_name})?,(%{NUMBER:process_id})?,("\[%{DATA:connection_from}\]")?,(%{USERNAME:session_id})?,(%{NUMBER:session_line_num})?,("%{DATA:command_tag}")?,(%{TIMESTAMP_ISO8601:timestamp2}%{SPACE}%{TZ:time_zone2})?,(%{DATA:virtual_transaction_id})?,(%{DATA:transaction_id})?,(%{DATA:error_severity})?,(%{NUMBER:sql_state_code})?,"duration:%{SPACE}%{NUMBER:duration}%{SPACE}ms%{SPACE}statement:%{SPACE}%{GREEDYDATA:statement}",(%{DATA:details})?,(%{DATA:hint})?,(%{DATA:internal_query})?,(%{DATA:internal_query_pos})?,(%{DATA:context})?,(%{DATA:query})?,(%{DATA:query_pos})?,(%{DATA:location})?,(%{GREEDYDATA:application_name})?
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [May 6, 2020, 8:55pm UTC](https://discuss.elastic.co/t/logstash-grok-pattern-question-when-sql-statement-has-commas-in-it/227022/6 "2020-05-06T20:55:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.