Graylog Mysql Fields


(Jake Smith) #1

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


(Jan Doberstein) #2

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
 

(Jake Smith) #3

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


(Jan Doberstein) #4

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}

(Jake Smith) #5

Hi Jan,

And boom , we have success

Kind Regards

Jake


(Jochen) #6

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


(system) #7

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