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" ]
}
}
