Page 3 of 5
Re: How to export query results to a text file?
Posted: Mon Sep 12, 2016 9:59 am
by 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.
Re: How to export query results to a text file?
Posted: Mon Sep 12, 2016 10:45 am
by scottwilkerson
sooz4u wrote: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?
Re: How to export query results to a text file?
Posted: Mon Sep 12, 2016 10:47 am
by 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).
Re: How to export query results to a text file?
Posted: Mon Sep 12, 2016 4:43 pm
by tmcdonald
sooz4u wrote: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.
Re: How to export query results to a text file?
Posted: Mon Sep 12, 2016 7:37 pm
by 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.
Re: How to export query results to a text file?
Posted: Tue Sep 13, 2016 9:43 am
by mcapra
onthax wrote: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?
Re: How to export query results to a text file?
Posted: Tue Nov 15, 2016 6:27 pm
by onthax
mcapra wrote:onthax wrote: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.
Re: How to export query results to a text file?
Posted: Wed Nov 16, 2016 12:20 am
by 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?
Re: How to export query results to a text file?
Posted: Wed Nov 16, 2016 11:06 am
by 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
Re: How to export query results to a text file?
Posted: Wed Jul 26, 2017 9:42 pm
by 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?