Nagios Report CSV

[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

[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/

[bolson]

Hello peter19x,

Does this answer your question?