# Parsing SQL Error Logs & Best Practices

**URL:** <https://discuss.elastic.co/t/parsing-sql-error-logs-best-practices/74716>\
**Category:** Logstash\
**Created:** [February 10, 2017, 9:42pm UTC](https://discuss.elastic.co/t/parsing-sql-error-logs-best-practices/74716 "2017-02-10T21:42:34Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![locolbd](https://avatars.discourse-cdn.com/v4/letter/l/ce73a5/32.png) [@locolbd](https://discuss.elastic.co/u/locolbd)\
**Post date:** [February 10, 2017, 9:42pm UTC](https://discuss.elastic.co/t/parsing-sql-error-logs-best-practices/74716/1 "2017-02-10T21:42:34Z")

</div>

I am in the early stages of collecting SQL logs from the sql errrorlog file. My DBAs are slowing giving me information as to the type of data they would like parsed into fields etc.

I would like to parse the domain and user into their own fields respectively. However, with SQL logs the domain and user is not always written. In order to pull the user and domain should i create several conditionals to ensure I correctly parse the logs I would be interested in that contain user and domain info. Ex I would like to add user and domain fields whenever there are failed logins, trace is started, trace is stopped etc. Is conditionals the answer or is there another more effective way to handle such.

The following is a snippet from my filter, however the last match =\> does not seem to create the fields. Was this done correctly or am i missing something?  
grok {  
add\_tag =\> ["grok\_dba\_sql"]  
match =\> { "message" =\> "^%{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{WORD:sq\_source}%{SPACE}%{GREEDYDATA:message}" }  
overwrite =\> ["message"]  
match =\> { "message" =\> "%{GREEDYDATA}"%{WORD:domain}\%{WORD:username}"." }  
}

Below is my filter  
filter {  
if (("sql-beats" in [tags]) and ("Trace stopped" in [message])) {  
grok {  
add\_tag =\> ["grok\_dba\_sql"]  
match =\> { "message" =\> "^%{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{WORD:sq\_source}%{SPACE}%{SQL\_MESSAGE:message}.%{SPACE}%{WORD}%{SPACE}%{WORD}%{SPACE}=%{SPACE}'%{DIGIT:traceid}'.%{SPACE}%{WORD}%{SPACE}%{WORD}%{SPACE}=%{SPACE}'%{WORD:domain}\%{WORD:username}'." }  
overwrite =\> ["message"]  
}  
} else if (("sql-beats" in [tags]) and ("Login failed" in [message])) {  
grok {  
add\_tag =\> ["grok\_dba\_sql"]  
match =\> { "message" =\> "^%{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{WORD:sq\_source}%{SPACE}%{GREEDYDATA:message}" }  
overwrite =\> ["message"]  
match =\> { "message" =\> "%{GREEDYDATA}'%{WORD:domain}\%{WORD:username}'%{GREEDYDATA}" }  
}  
} else if (("sql-beats" in [tags]) and ("SQL Trace ID" in [message])) {  
grok {  
add\_tag =\> ["grok\_dba\_sql"]  
match =\> { "message" =\> "^%{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{WORD:sq\_source}%{SPACE}%{GREEDYDATA:message}" }  
overwrite =\> ["message"]  
match =\> { "message" =\> "%{GREEDYDATA}"%{WORD:domain}\%{WORD:username}"." }  
}  
} else {  
grok {  
add\_tag =\> ["grok\_dba\_sql"]  
match =\> { "message" =\> "^%{TIMESTAMP\_ISO8601:timestamp}%{SPACE}%{WORD:sq\_source}%{SPACE}%{GREEDYDATA:message}" }  
overwrite =\> ["message"]  
}  
}  
date {  
match =\> ["timestamp", "YYYY-MM-dd HH:mm:ss.SS","YYYY-MM-dd HH:mm:ss","MMM d HH:mm:ss","MMM dd HH:mm:ss","MM/dd/YYYY HH:mm:ss.SS", "MM/dd/YYYY HH:mm:ss.SS aa", "MM/dd/YYYY HH:mm:ss", "MM/dd/YYYY HH:mm:ss aa", "ISO8601"]  
target =\> "@timestamp"  
remove\_field =\> ["timestamp"]  
}  
}

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [February 13, 2017, 8:35am UTC](https://discuss.elastic.co/t/parsing-sql-error-logs-best-practices/74716/2 "2017-02-13T08:35:24Z")

</div>

Can you reformat the code so it's a bit easier to read? 🙂

---

<div class="post-metadata">

**Author:** ![locolbd](https://avatars.discourse-cdn.com/v4/letter/l/ce73a5/32.png) [@locolbd](https://discuss.elastic.co/u/locolbd)\
**Post date:** [February 13, 2017, 6:24pm UTC](https://discuss.elastic.co/t/parsing-sql-error-logs-best-practices/74716/3 "2017-02-13T18:24:28Z")

</div>

See attached link [http://pastebin.com/FrGtMJbt](http://pastebin.com/FrGtMJbt)

---

<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 13, 2017, 6:25pm UTC](https://discuss.elastic.co/t/parsing-sql-error-logs-best-practices/74716/4 "2017-03-13T18:25:15Z")

</div>

Closed on March 13, 2017, 6:25pm UTC

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