Sending logs to .csv file using logstash?


(Yaswanth ) #1

Hi
ES 2.4.0
Logstash 5.1.1
I want to send the slowlogs to .csv file using logstash . my config file is like this

input {
file {
path => "D:\logstash-5.1.1\logstash-5.1.1\bin\slowlog.log"
start_position => "beginning"
}
}

filter {
grok { # parses the common bits
match => [ "message", "[%{TIMESTAMP_ISO8601:TIMESTAMP}][%{LOGLEVEL:LEVEL}%{SPACE}][%{DATA:QUERY}]%{SPACE}[%{DATA:QUERY1}]%{SPACE}[%{DATA:INDEX-NAME}][%{DATA:SHARD}]%{SPACE}took[%{DATA:TOOK}],%{SPACE}took_millis[%{DATA:TOOKM}], types[%{DATA:types}], stats[%{DATA:stats}], search_type[%{DATA:search_type}], total_shards[%{NUMBER:total_shards}], source[%{DATA:source_query}], extra_source[%{DATA:extra_source}],"]
}
}
output {
csv {
fields => ["TIMESTAMP","LOGLEVEL","QUERY","QUERY1","INDEX-NAME","SHARD","TOOK","took_millis","types","stats","search_type","total_shards","source_query","extra_source"]
path => "D:\logstash-5.1.1\logstash-5.1.1\bin\final.csv"
}
stdout { codec => rubydebug }
}

my output is coming in a irrregular format like

2017-01-17T06:35:26.236Z PC147594 [2017-01-13 12:58:09 843][WARN ][index.search.slowlog.query] [Spectra] [testindex-stats][2] took[15.3ms] took_millis[15] types[] stats[] search_type[QUERY_THEN_FETCH] total_shards[5] source[{"query":{"match":{"text":"ronin"}}}] extra_source[]

My logs format is
[2017-01-13 21:21:03,956][WARN ][index.search.slowlog.query] [Yaswanth] [bank][0] took[28.9ms], took_millis[28], types[], stats[], search_type[QUERY_THEN_FETCH], total_shards[5], source[], extra_source[],

My output should be like every field in logs squarebrackets should in one ,one column like 2017-01-13 21:21:03,956 in one column WARN in one column index.search.slowlog.query in one column so on..

can anyone help me in solving this?


(Magnus Bäck) #2

my output is coming in a irrregular format like mixing with other fields.

Please don't attempt to describe what you get. Show us. Use copy/paste.


(Yaswanth ) #3

hey,
I edited the question using the copy paste but i think the picture is not clear.
Let me know if you want the screenshot of it so that you will get the clear picture of my output.
Thanks


(Magnus Bäck) #4

I don't think the input is correctly parsed. Please show the results of your stdout { codec => rubydebug } output for one sample input message. No screenshots.


(Yaswanth ) #5

sure magnusbaeck..
It is like this
{
"total_shards" => "5",
"LEVEL" => "DEBUG",
"message" => "[2017-01-13 14:37:48,943][DEBUG][index.search.slowlog.query] [Spec
tra] [test][2] took[46.1micros], took_millis[0], types[], stats[], search_type[QUERY_THEN
_FETCH], total_shards[5], source[], extra_source[], \r",
"SHARD" => "2",
"search_type" => "QUERY_THEN_FETCH",
"tags" => [],
"QUERY1" => "Spectra",
"TOOK" => "46.1micros",
"path" => "D:\logstash-5.1.1\logstash-5.1.1\bin\slowlog.log",
"TOOKM" => "0",
"@timestamp" => 2017-01-17T06:35:26.594Z,
"INDEX-NAME" => "test",
"QUERY" => "index.search.slowlog.query",
"TIMESTAMP" => "2017-01-13 14:37:48,943",
"@version" => "1",
"host" => "PC147594"


(Magnus Bäck) #6

Okay, that doesn't look unreasonable. And what does the resulting .csv file look like? No screenshots, use copy/paste from the text file.


(Yaswanth ) #7

OUTPUT .CSV FILE:
The below are the columns that are getting in my output .csv file.

columns
2017-01-17T06:35:26.236Z PC147594 [2017-01-13 12:58:09
843][WARN ][index.search.slowlog.query] [Spectra] [testindex-stats][2] took[15.3ms]
took_millis[15]
types[]
stats[]
search_type[QUERY_THEN_FETCH]
total_shards[5]
source[{"query":{"match":{"text":"ronin"}}}]
extra_source[]

But what i want is like this,i want the log contents in [ ] thats it

columns
2017-01-17T06:35:26.236Z
PC147594
2017-01-13 12:58:09,843
WARN
index.search.slowlog.query
And so on..

Thanks..


(Magnus Bäck) #8

Okay. There's either a bug in the csv output or there's something going on in your configuration that I don't have time or patience to debug.

In the future, please do exactly what's requested. Do not replace commas with newline characters unless asked to.


(Yaswanth ) #9

Hi ,

One thing i want to know is that the csv has the default separator as "," how can i change it to "[ ]"

Because that why the output format is not correct .

Thanks.


(system) #10

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