Graylog Mysql Fields

Dear All,

We are shipping mariadb (10.3.8 and 5.5.56) logs on Centos 7 to our Gralog server using NxLog as shown below:-

I know the field format of the mariadb log.

[timestamp],[serverhost],[username],[host],[connectionid],
[queryid],[operation],[database],[object],[retcode]

What I want to do is to insert key pairs to the value of fields.

Example
Original
20180710 11:01:21,centos2,root,localhost,3,12,QUERY,customers,‘select * from users’,0

Becomes
timestamp=20180710 11:01:21,hostname=centos2,user=root,connect_id=localhost,query_type=QUERY,database=customers,query=‘select * from users’,ret_code=0

Could I do this using a GROK filter or maybe a pipeline rule, but I found there are no keys in the original message to use the pipeline key value extractor for example. The fields are fixed.

Any help appreciated.

Kind Regards

Jake

He,

if the fields are fixed, then just create a GROK Pattern or similar to extract them. That should be the easiest way to get your data seperated.

I have something like the following to extract mySQL Slow logs

rule "mysql: extract slow query log"
when
  has_field("type") && to_string($message.type) == "mysql-slow"
then
 let message_field = to_string($message.message);
 
 let action = grok(pattern: "(?s) User@Host: (?:%{USERNAME:mysql_clientuser})(?:%{GREEDYDATA}) @ (?:%{DATA:mysql_clienthost}) \\[(?:%{DATA:mysql_clientip}\\]) %{GREEDYDATA} Query_time: %{NUMBER:mysql_querytime}(?:%{SPACE})Lock_time: %{NUMBER:mysql_locktime}(?:%{SPACE})Rows_sent: %{NUMBER:mysql_rowssent}(?:%{SPACE})Rows_examined: %{NUMBER:mysql_rowsexamined}(?:%{SPACE})(?:%{GREEDYDATA})SET timestamp=%{NUMBER:mysql_timestamp}\\;(?:%{GREEDYDATA:mysql_slow_query})\\;", value: message_field, only_named_captures: true);
 set_fields(action);
 
end
 

Hi Jan,

Is this about right for the GROK pattern, it is my first time writing one. It is a little weird as I am used to regex!

Original data
20180710 11:01:21,centos2,root,localhost,3,12,QUERY,customers,‘select * from users’,0

GROK Pattern
%{GREEDYDATA:datestamp}\s%{TIME:time},%{HOSTNAME:host},%{USERNAME:user},%{WORD:connected_id},%{NUMBER:query_id1},%{NUMBER:query_id2},%{WORD:database},%{GREEDYDATA:query_executed},%{NUMBER:ret_code}

This would give us as a pipeline rule

rule “mysql: extract query log”
when
has_field(“type”) && to_string($message.type) == “mysql”
then
let message_field = to_string($message.message);

let action = grok(pattern: “%{GREEDYDATA:datestamp}\s%{TIME:time},%{HOSTNAME:host},%{USERNAME:user},%{WORD:connected_id},%{NUMBER:query_id1},%{NUMBER:query_id2},%{WORD:database},%{GREEDYDATA:query_executed},%{NUMBER:ret_code};”, value: message_field, only_named_captures: true);
set_fields(action);

end

What do you think?

Cheers

Jake

According to the GROKdebugger that should work - when removing the last ; and replace the \s with %{SPACE}

%{NUMBER:datestamp}%{SPACE}%{TIME:time},%{HOSTNAME:host},%{USERNAME:user},%{WORD:connected_id},%{NUMBER:query_id1},%{NUMBER:query_id2},%{WORD:database},%{GREEDYDATA:query_executed},%{NUMBER:ret_code}

Hi Jan,

And boom , we have success

Kind Regards

Jake

Make sure to share your extractors and pipelines on the Graylog Marketplace: https://marketplace.graylog.org/ :wink:

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