How to export query results to a text file?

[tmcdonald]

As a workaround, there are ways to convert JSON to CSV:

https://konklone.io/json/

or on the CLI:

https://github.com/zemirco/json2csv

I have used the first link but not the second, so your mileage may vary.

[scottwilkerson]

I can't believe this fundamental "export" or "dump" is not yet implemented. It's either hard or no one really cares. As long as I can't get reports for my Executives ... this solution won't fly.

This thread has taken on several avenues and to be honest, I have seen several things asked for. So to be clear, are you looking for an export of just the contents of the table panel in a particular dashboard, or are you looking for an export or dump of everything in log server?

[eloyd]

I'm in full agreement that this seems like it should be trivial to do. We use NLS and NNA as part of our intrusion detection system and we run reports for customers on errors from NLS information. At this time, we have to search for stuff in NLS then go back to the original logs to extract the information we're reporting on, which is, well, you know what it is; I don't need to say what it is here.

I'm all for development cycles and so forth, but if I had to vote for a new NLS feature, my #1 (and only) selection would be "exportable reports" (of what shows up in a table from a query).

[tmcdonald]

After a slight bit of experimetation ... if you change the "Paging" setting on the Events panel to the maximum number of records you expect, then use select, copy/paste into a text file, then open that file in Excel - the fields are tab delimited and this actually does the trick. I now have a dashboard (with different fields) in the Events panel just for this purpose. It's a bit of work but does what I need. Anxiously awaiting a real data dumper type of function in NLS.

Posting this here for clarity since the original post was updated.

[onthax]

For me, If we need to provide logs to a vendor, we are unable to provide this in txt format (as it looks on the source)

So at the moment we maintain 2 sets of logs, 1 in nagios 1 on a source in case we need to escalate to a vendor.

[mcapra]

For me, If we need to provide logs to a vendor, we are unable to provide this in txt format (as it looks on the source)

Just so I can fully understand your use case, you're saying that a component that exports the raw message for a set of events would be what you need?

[onthax]

For me, If we need to provide logs to a vendor, we are unable to provide this in txt format (as it looks on the source)

Just so I can fully understand your use case, you're saying that a component that exports the raw message for a set of events would be what you need?

We would need to have the final output in raw syslog format (txt)

so the vendor could read it just like any other syslog server, not just message as it would need source, timestamp etc.

[dbgong]

Any update ?

I installed last log server on aws for testing. but I can't find to export raw data on dashboard.
so is anything tip on this?

[mcapra]

We're still evaluating what will happen with Kibana for NLS 2, which impacts exporting of dashboard data.

I wrote a PHP script that can take a generic elasticsearch query and export the results into a CSV file. This script comes with no particular guarantees or endorsements and is not an addition to Nagios Log Server as a product; just something I did in my free time for fun. If you find it useful, great!:

Code: Select all

<?php

$ch = curl_init();

curl_setopt($ch, CURLOPT_URL,            'http://localhost:9200/logstash-*/_search?size=' . $argv[1]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1 );
curl_setopt($ch, CURLOPT_POST,           1 );
curl_setopt($ch, CURLOPT_POSTFIELDS,     $argv[3]); 
curl_setopt($ch, CURLOPT_HTTPHEADER,     array('Content-Type: text/plain')); 

$result=curl_exec ($ch);

$arr = json_decode($result, true);

$keys = array();

foreach($arr['hits']['hits'] as $hit) {
	$keys = array_unique(array_merge(array_keys($hit['_source']), $keys));
}


$fp = fopen($argv[2], 'w');

//write fields to top of csv
fputcsv($fp, $keys);

foreach($arr['hits']['hits'] as $hit) {
	fputcsv($fp, $hit['_source']);
}


fclose($fp);

?>

Usage:

Code: Select all

php -q get_query.php <response_size> <output_file> <query>

Example with an elasticsearch query to get the most recent 250 entries in the last 24 hours:

Code: Select all

php -q get_query.php 250 '/tmp/out.csv' '{"query":{"filtered":{"query":{"bool":{"should":[{"query_string":{"query":"*"}}]}},"filter":{"bool":{"must":[{"range":{"@timestamp":{"from":"now-24h","to":"now"}}}]}}}}}'

Which produces a CSV file similar to this:

2016_11_16_10_09_12_out.csv_OpenOffice_Calc.png

[onthax]

Cheers mcapra,

will give that a go,
We get around this by storing all the logs in 2 places, once in nagios, one at the source, which is a bit useless but it seems to be the way to make this work.

Any update from the devs on if this is being added to the product or not?