Page 1 of 1
Nagios Report CSV
Posted: Thu Aug 03, 2017 6:59 am
by peter19x
Hi,
I am using the Export CSV option for my services as per the screen shot attached. But when I open the file with excel I get 2 columns, one Time stamp and the other Value as per below :
timestamp value
1496854800 275.9963333
1496856600 189.99525
1496858400 44.88225
1496860200 24.927
1496862000 16.60863636
1496863800 9.297252525
Which is not human readable. Is there a way I could extract data from the Linux command line and save it as a CSV file? this way I might be able to tweak the outputs.
Thanks,
Peter
Re: Nagios Report CSV
Posted: Thu Aug 03, 2017 9:35 am
by mcapra
I
think that all the Highcharts stuff uses PhantomJS to export the CSV. Hopefully someone will correct me if I'm wrong, but fudging that from the command-line to leverage something like awk ahead of the writes would be difficult I think.
Here's a simple PHP script that takes a tab-delimited file with column names in the first line and converts the timestamp column to a proper date relative to the system:
Code: Select all
<?php
error_reporting(E_ERROR | E_PARSE);
if((sizeof($argv) < 1) || !file_exists($argv[1])) {
echo "Invalid file: " . $argv[1] . PHP_EOL;
exit(2);
}
$input = fopen($argv[1],"r");
$output = fopen(pathinfo($argv[1])['dirname'] . DIRECTORY_SEPARATOR . 'date_conversion_' . time() . '.csv',"w+");
$headers = fgetcsv($input,0,"\t");
fputcsv($output,$headers,"\t");
while(! feof($input))
{
$line = fgetcsv($input,0,"\t");
$line[0] = date('Y-m-d H:i', $line[0]);
fputcsv($output,$line,"\t");
}
fclose($input);
fclose($output);
?>
Assumptions this script makes:
- Your headers are the first line of the file
- The file is delimited by single tabs (I used the text in your OP as reference)'
- The first column is a unix epoch
- The output file is written to the same path as the source file, named date_conversion_[epoch].csv
In action:
Code: Select all
[root@nagios tmp]# ls -al
total 8
drwxrwxrwt. 7 root root 129 Aug 3 09:43 .
dr-xr-xr-x. 18 root root 268 Aug 3 09:40 ..
-rwxr-xr-x 1 root root 543 Aug 3 09:43 csv_epoch.php
drwxrwxrwt. 2 root root 6 May 23 11:27 .font-unix
drwxrwxrwt. 2 root root 6 May 25 11:12 .ICE-unix
-rw-r--r-- 1 root root 144 Aug 3 09:43 out.csv
drwxrwxrwt. 2 root root 6 May 23 11:27 .Test-unix
drwxrwxrwt. 2 root root 6 May 25 13:23 .X11-unix
drwxrwxrwt. 2 root root 6 May 23 11:27 .XIM-unix
[root@nagios tmp]# cat out.csv
timestamp value
1496854800 275.9963333
1496856600 189.99525
1496858400 44.88225
1496860200 24.927
1496862000 16.60863636
1496863800 9.297252525
[root@nagios tmp]# php -q csv_epoch.php /tmp/out.csv
[root@nagios tmp]# ls -al
total 12
drwxrwxrwt. 7 root root 167 Aug 3 09:43 .
dr-xr-xr-x. 18 root root 268 Aug 3 09:40 ..
-rwxr-xr-x 1 root root 543 Aug 3 09:43 csv_epoch.php
-rw-r--r-- 1 root root 211 Aug 3 09:43 date_conversion_1501771435.csv
drwxrwxrwt. 2 root root 6 May 23 11:27 .font-unix
drwxrwxrwt. 2 root root 6 May 25 11:12 .ICE-unix
-rw-r--r-- 1 root root 144 Aug 3 09:43 out.csv
drwxrwxrwt. 2 root root 6 May 23 11:27 .Test-unix
drwxrwxrwt. 2 root root 6 May 25 13:23 .X11-unix
drwxrwxrwt. 2 root root 6 May 23 11:27 .XIM-unix
[root@nagios tmp]# cat date_conversion_1501771435.csv
timestamp value
"2017-06-07 17:00" 275.9963333
"2017-06-07 17:30" 189.99525
"2017-06-07 18:00" 44.88225
"2017-06-07 18:30" 24.927
"2017-06-07 19:00" 16.60863636
"2017-06-07 19:30" 9.297252525
"1970-01-01 00:00"
Otherwise there's probably some VB functions within Excel that could be leveraged to convert an epoch to a date+time. I imagine if things are already happening in Excel, that might be an easier route:
http://spreadsheetpage.com/index.php/ti ... imestamps/
Re: Nagios Report CSV
Posted: Thu Aug 03, 2017 4:12 pm
by bolson
Hello peter19x,
Does this answer your question?