Rock your everyday

I have used a nice, simple script to export the rrd values into excel using PHP 5. You can find the script here:

http://code.google.com/p/php-excel/

Here is my code for the rrd export (here you'll find two functions - calculate_rrd() and format_bits(), these are described in my previous blog):

/*
Author: Md.Monjurul Hasan

Description
- RRD data exporter - output values into excel file
*/
/***************included files*******/

#include the export-xls.class.php file
require('export-xls.class.php');

$filename = 'rrd_export'.rand(0,10000).'.xls'; // a random filename chosen
#create an instance of the class
$xls = new ExportXLS($filename);

//read config file and get variables
$rrd_array = array ('rra/test1.rrd', 'rra/test2.rrd');
//export to excel
$header = " RRD Export"; // single first col text
$xls->addHeader($header);

#add blank line
$header = null;
$xls->addHeader($header);

#add 2nd header as an array of 3 columns
$header[] = "RRD Name";
$header[] = "Cur In";
$header[] = "";
$header[] = "Cur Out";
$header[] = "";
$header[] = "Avg In";
$header[] = "";
$header[] = "Avg Out";
$header[] = "";
$header[] = "Max In";
$header[] = "";
$header[] = "Max Out";
$header[] = "";
$header[] = "95% In";
$header[] = "";
$header[] = "95% Out";
$header[] = "";

$xls->addHeader($header);

$count = 0;
for ($c = 0; $c < count($rrd_array); $c++) {
$ret = calculate_rrd($rrd_array[$c], $graph_type); // graph type is daily/weekly/yearly and monthly
$row = array();
$row[] = $rrd_array[$c];
$result = format_bits($ret['current_in']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['current_out']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['average_in']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['average_in']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['max_in']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['max_out']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['percentile_in']);
$row[] = $result[0];
$row[] = $result[1];
$result = format_bits($ret['percentile_out']);
$row[] = $result[0];
$row[] = $result[1];
$xls->addRow($row);
}
//output the excel file
$xls->sendFile();

?>

I needed the rrd values in tabular format rather than graph. So i fetched the rrd values using rrd_fetch() function of PHP-RRDTool API. Then I calculated the required Current, Average, Max and 95th Percentile using the following function which may save time of others:

For percentile calculation, I followed the following article:
http://forums.cacti.net/post-95140.html&highlight=

To represent the values in bps/kbps/Mbps, you can use the following function:

/***
Function format value from bit to bps/kbps/Mbps

@input : bits
@output array: array[0] = value
array[1] = bps/kbps/Mbps

***/
function format_bits ($bits) {
$round_precision = 2;
if ($bits < 1000) {
$result[0] = round($bits,$round_precision);
$result[1] = "bps";
}
else if ($bits >= 1000 && $bits < 100000) {
$kbits = $bits/1000;
$result[0] = round($kbits,$round_precision);
$result[1] = "kbps";
}
else {
$mbits = $bits/(1000*1000);
$result[0] = round ($mbits, $round_precision);
$result[1] = "Mbps";
}
return $result;
}