Pulling usable data from an rrd file

[SirAldemar]

I am new to cacti development and need some help.

I need to be able to pull data directly from an rrd file.

Here is an example...
When I generate a graph for the monthly traffic on an interface I would also like to see the daily average for each day of that month.

Such as...
{Graph}
1st 123,155,143.22 bytes in 16,123,642.23 bytes out
2nd 123,155,143.22 bytes in 16,123,642.23 bytes out
3rd 123,155,143.22 bytes in 16,123,642.23 bytes out
4th 123,155,143.22 bytes in 16,123,642.23 bytes out
etc...

Can anyone point me in the correct direction for being able to do this?

[gandalf]

You may have a look at the reportit plugin
Reinhard

[SirAldemar]

You may have a look at the reportit plugin
Reinhard

Thank you for this suggestion. I have been trying to modify the Reports plug-in because it has the e-mail option. I did not see this available in the ReportIt plug-in (Maybe I just missed it).

I have a strong need to be able to automattically generate a report on a daily or weekly or monthyl basis with graphs and detailed data and be able to send it directly to specific people/groups.

[gandalf]

Thank you for this suggestion. I have been trying to modify the Reports plug-in because it has the e-mail option. I did not see this available in the ReportIt plug-in (Maybe I just missed it).

I have a strong need to be able to automattically generate a report on a daily or weekly or monthyl basis with graphs and detailed data and be able to send it directly to specific people/groups.

Yep, reportit currently does not support mailing. But as the developers are working hard on a general mailing plugin (currently known as the "settings" plugin), it should be possible to add that feature. The main goal of reportit is to provide tabular reports.

If you're interested in mails including rrdgraphs instead, you may stick to the reports plugin. Please pay attention: there are two of them with the same name! But mailing schedule of this one only includes daily mails, at least last time I've looked at it.

Reinhard

[SirAldemar]

Gandolf,

Thank you again for this information.

I have already modified the Reports plugin to allow for daily, weekly, monthly or yearly e-mails.

I have also developed a PHP script that will pull the data I need. My problem is I have not been able to link the graphs with the correct rrd file.

Would you have any thoughts on how I can link these two items?

[gandalf]

Gandolf,

Thank you again for this information.

De rien.

I have already modified the Reports plugin to allow for daily, weekly, monthly or yearly e-mails.

Would you mind publishing this bit? Which of both reports plugin did you modify (please name the author)

I have also developed a PHP script that will pull the data I need. My problem is I have not been able to link the graphs with the correct rrd file.

Would you have any thoughts on how I can link these two items?

You should be more verbose on this. cigamits reports plugin automatically refers to some graph that in turn is related to some rrd file. And still I'm wondering whether you are interested in graphs or in tabular numerical data
Reinhard

[SirAldemar]

OK. All of your suggestions have been great.

I have everything working now where when I send a report to an e-mail address that report will contain the graph and the tubular data (although I have a little tweaking to do).

My current problem is that if I have a graph the has a custom CDEF applied to it the graph looks fine but my tubular data is just the raw data without the custom CDEF being applied.

I am enclosing the code I used and hope someone can...
1) Maybe help improve my coding since I am "really new" at this
2) Help me add the custom CDEF fix which is currently commented out in this code.

This is my modified code of the functions.php file from the reports plugin.

Code: Select all

<?php

function report_preview($report) {
	global $config, $colors;

	$f = 0;
	$day_avg_in = 0;
	$day_avg_out = 0;
	$rep = db_fetch_assoc("select * from reports where id = $report");
	$template_list = db_fetch_assoc("select * from reports_data where reportid = $report order by gorder");

	html_start_box("", "80%", $colors["header"], "1", "center", "");
	print "<table bgcolor='#FFFFFF' width='100%'><tr><td>";

	print "<h3><center>" . $rep[0]['name'] . "</center></h3><hr color=#000080 size=2>";
	print "<center><b><u>Comments</u></b><br>" . $rep[0]['comment'] . "</center><br><ul style='text-indent: 0; word-spacing: 0; line-height: 100%; margin: 0'>";
	$etime = time();
	$multi = array(86400,604800,2592000,31536000);
	foreach ($template_list as $template) {
		$stime = $etime - $multi[$template['type']-1];
		print "<center><img src='../../graph_image.php?local_graph_id=" . $template['local_graph_id'] . "&rra_id=" . $template['type'] . "&graph_start=$stime&graph_end=$etime'></center><br>";
		// Add support to print tubular date for each graph based on graph type
		if ($template['details'] == 1) {
			$temp_graph_id = db_fetch_cell("select data_template_rrd.local_data_id 
											from (graph_templates_item,data_template_rrd) 
											where graph_templates_item.task_item_id=data_template_rrd.id 
											and graph_templates_item.local_graph_id={$template['local_graph_id']} limit 1");
			$temp_datasource_path = get_data_source_path($temp_graph_id,true);
			switch ($template['type']-1) {
				case '0':
					$DspHeader = "<font size=2>Daily details broken down by hourly average</font>";
					$det_opts = array("AVERAGE", "--start", strtotime("midnight - 1 day"), "--end", strtotime("midnight - 1 minute"));
					$average = 'hourly';
					break;
				case '1':
					$DspHeader = "<font size=2>Weekly details broken down by daily average</font>";
					$det_opts = array("AVERAGE", "--start", strtotime("midnight - 1 week"), "--end", strtotime("midnight - 1 minute"));
					$average = 'daily';
					break;
				case '2':
					$DspHeader = "<font size=2>Monthly details broken down by daily average</font>";
					$det_opts = array("AVERAGE", "--start", strtotime("midnight - 30 days"), "--end", strtotime("midnight - 1 minute"));
					$average = 'daily';
					break;
				case '3':
					$DspHeader = "<font size=2>Yearly details broken down by monthly average</font>";
					$det_opts = array("AVERAGE", "--start", strtotime("midnight - 365 days"), "--end", strtotime("midnight - 1 minute"));
					$average = 'monthly';
					break;
			}
			$details = rrd_fetch($temp_datasource_path, $det_opts, count($det_opts));
			?><div align="center">
				<table border="1" cellspacing="0" cellpadding="1" id="details_table">
					<thead>
						<tr>
							<th colspan=<?php echo count($details['ds_namv'])+1?> bgcolor="#000080">
								<font color="#FFFFFF"><b><center><?php echo $DspHeader;?></center></b></font>
							</th>
						</tr>
						<tr>
							<th bgcolor="#C0C0C0"><font size=2>Date </font>
							<?php 
								if ($average == 'hourly') {
									echo "<font size=1>(hour)</font>";
								}
							?></th>
							<?php 
							foreach ($details['ds_namv'] as $ds_name) {
								echo '<th bgcolor="#C0C0C0"><font size=2>'.$ds_name.'</font></th>';
							}
							?>
						</tr>
					</thead>
					<?php 
						if (is_array($details)) {
							unset($data_array);
							// Set the data formats..
							if ($average == 'hourly') {
								$d_fmt = 'M d, Y (H)';
							} elseif ($average == 'daily'){
								$d_fmt = 'M d, Y';
							} elseif ($average == 'monthly'){
								$d_fmt = 'M, Y';
							}
							$break = FALSE;
							$ts_key = $details['start'];
							// this is the magic that loops through the big array from the rra_fetch cmd.
							while ($break == FALSE) { 
								// This loops through the datasources and will write out the values in order.
								foreach ($details['ds_namv'] as $ds_key => $ds_name) {
									if (! list($key,$value) = each($details['data'])) {
										$break = TRUE;
										break;
									}
									$grp_date = date($d_fmt,$ts_key);
									if (is_numeric(trim($value))) {
										$data_array[$ds_name][$grp_date][] = trim($value);
										// Uncomment below for troubleshooting to make sure that your DS data matches up..
										# echo "$key: DS:$ds_name DATE:$grp_date TS:".($ts_key)." ($value)\n<br>";
									} else {
										$data_array[$ds_name][$grp_date][] = "";
									}
								}
								$ts_key = $ts_key+$details['step'];
							}
							// Drop the last entry off of each data source array
							foreach ($details['ds_namv'] as $ds_name) {
								array_pop($data_array[$ds_name]);
							}
							/* 
								Run through the data_array which contains all of our data sorted into buckets 
								by the date range that we're looking for.
							*/
							$ds_key_1=$details['ds_namv'][0];		
							$col_cnt=1;
							echo "<tr>"; ## </tr will be ended later on...
							foreach ($data_array[$ds_key_1] as $grp_date=>$values) {
								echo "<td><font size=2>$grp_date</font></td>";
								foreach ($details['ds_namv'] as $ds_name) {
									if (@is_array($data_array[$ds_name][$grp_date])) {
										echo "<td><font size=2>";
										if ($template['cdef_id'] >= 1) {
//											$temp = db_fetch_assoc("select type, value from cdef_items order by sequence where cdef_id = " . $template['cdef_id']);
/*											foreach ($temp) {
												switch ($temp['type']) {
													case '1':
														form_dropdown($data_array[$ds_name][$grp_date], $cdef_functions, "", "", (isset($data_array[$ds_name][$grp_date]) ? $data_array[$ds_name][$grp_date] : ""), "", "");
														break;
													case '2':
														form_dropdown($data_array[$ds_name][$grp_date], $cdef_operators, "", "", (isset($data_array[$ds_name][$grp_date]) ? $data_array[$ds_name][$grp_date] : ""), "", "");
														break;
													case '4':
														form_dropdown($data_array[$ds_name][$grp_date], $custom_data_source_types, "", "", (isset($data_array[$ds_name][$grp_date]) ? $data_array[$ds_name][$grp_date] : ""), "", "");
														break;
													case '5':
														form_dropdown($data_array[$ds_name][$grp_date], db_fetch_assoc("select name,id from cdef order by name"), "name", "id", (isset($data_array[$ds_name][$grp_date]) ? $data_array[$ds_name][$grp_date] : ""), "", "");
														break;
													case '6':
														form_text_box($data_array[$ds_name][$grp_date], (isset($data_array[$ds_name][$grp_date]) ? $data_array[$ds_name][$grp_date] : ""), "", "255", 30, "text", (isset($_GET["id"]) ? $_GET["id"] : "0"));
														break;
												}
											}
*/											echo number_format(array_sum($data_array[$ds_name][$grp_date])/count($data_array[$ds_name][$grp_date]),2,".",",");
//											}
										} else {
											echo number_format(array_sum($data_array[$ds_name][$grp_date])/count($data_array[$ds_name][$grp_date]),2,".",",");
										}
										echo "</font></td>";
									} else {
										echo "<td><font size=2>No Data</font></td>";
									}
									$col_cnt++;
									if ($col_cnt > count($details['ds_namv'])) {
										echo "</tr>\n<tr>";
										$col_cnt=1;
									}
								}
							}
						} else {
							$err=rrd_error();
							echo "fetch() ERROR: $err\n";
						}
						print ("</tr><tr>");
						?>
					</tr>
					<tr>
						<td colspan=<?php echo count($details['ds_namv'])+1?>><center><b><font size=2>*** END of report ***</font></b></td>
					</tr>
				</table>
			<div><br><br>
		<?php } else { 
			print "<center><b>No details requested for this report</b><br><br>";
		}
	}
	print "</ul></td></tr></table>";
	html_end_box();
}

function generate_report($report) {
	global $config;
	print "<center>Generating Report!\n</center>";
	$from = read_config_option("thold_from_email");
	if ($from == '')
		$from = "IowaTelecomCDSReports@" . $_SERVER['HOSTNAME'];
	$subject = $report['name'];
	$message = "<h3><center>" . $report['name'] . "</center></h3><hr color=#000080 size=3><center>" . $report['comment'] . "</center><ul>";
	$etime = time();
	$multi = array(86400,604800,2592000,31536000);
	$file_array = array();
	$queryrows = db_fetch_assoc("select * from reports_data where reportid=". $report['id'] . ' order by gorder');
	if ($queryrows) {
		foreach($queryrows as $row) {
			if ($row['details'] == 1) {
				$email_details = 1;
				$temp_graph_id = db_fetch_cell("select data_template_rrd.local_data_id from (graph_templates_item,data_template_rrd) where graph_templates_item.task_item_id=data_template_rrd.id and graph_templates_item.local_graph_id={$row['local_graph_id']} limit 1");
				$temp_datasource_path = get_data_source_path($temp_graph_id,true);
				switch ($row['type']-1) {
					case '0':
						$email_RptHeader = "Daily details broken down by hourly average";
						$det_opts = array("AVERAGE", "--start", strtotime("midnight - 1 day"), "--end", strtotime("midnight - 1 minute"));
						$average = 'hourly';
						break;
					case '1':
						$email_RptHeader = "Weekly details broken down by daily average";
						$det_opts = array("AVERAGE", "--start", strtotime("midnight - 1 week"), "--end", strtotime("midnight - 1 minute"));
						$average = 'daily';
						break;
					case '2':
						$email_RptHeader = "Monthly details broken down by daily average";
						$det_opts = array("AVERAGE", "--start", strtotime("midnight - 30 days"), "--end", strtotime("midnight - 1 minute"));
						$average = 'daily';
						break;
					case '3':
						$email_RptHeader = "Yearly details broken down by monthly average";
						$det_opts = array("AVERAGE", "--start", strtotime("midnight - 365 days"), "--end", strtotime("midnight - 1 minute"));
						$average = 'monthly';
						break;
				}
				$details = rrd_fetch($temp_datasource_path, $det_opts, count($det_opts));
				if (is_array($details)) {
					unset($data_array);
					if (@is_array($temp_RptArray['avg_data'])) unset($temp_RptArray['avg_data']);
					// Set the data formats..
					if ($average == 'hourly') {
						$d_fmt='M d, Y (H)';
					} elseif ($average == 'daily'){
						$d_fmt='M d, Y';
					} elseif ($average == 'monthly'){
						$d_fmt='M, Y';
					}
					$break=FALSE;
					$ts_key=$details['start'];
					// this is the magic that loops through the big array from the rra_fetch cmd.
					while ($break==FALSE) { 
						// This loops through the datasources and will write out the values in order.
						foreach ($details['ds_namv'] as $ds_key=>$ds_name) {
							if (! list($key,$value)=each($details['data'])) {
								$break=TRUE;
								break;
							}
							$grp_date=date($d_fmt,$ts_key);
							if (is_numeric(trim($value))) {
								$data_array[$ds_name][$grp_date][]=trim($value);
								// Uncomment below to for troubleshooting to make sure that your DS data matches up..
								# echo "$key: DS:$ds_name DATE:$grp_date TS:".($ts_key)." ($value)\n<br>";
							}
						}
						$ts_key=$ts_key+$details['step'];
					}

					// Drop the last entry off of each data source array
					foreach ($details['ds_namv'] as $ds_name) {
						array_pop($data_array[$ds_name]);
					}
					//$temp_RptArray['data']=$data_array;
					$temp_RptArray["details"] = 1;
					$temp_RptArray["report_header"] = $email_RptHeader;
					$ds_key_1=$details['ds_namv'][0];		
					foreach ($data_array[$ds_key_1] as $grp_date=>$values) {
						foreach ($details['ds_namv'] as $ds_name) {
							if (@is_array($data_array[$ds_name][$grp_date])) {
								$temp_RptArray['avg_data'][$ds_name][$grp_date]=number_format(array_sum($data_array[$ds_name][$grp_date])/count($data_array[$ds_name][$grp_date]),2,".",",");
							}
						}
					}
					$temp_RptArray["report_footer"] = "*** END of report ***";
				} else {
					$err=rrd_error();
				}
			} else {
				$temp_RptArray["details"] = 0;
			}

			$stime = $etime - $multi[$row['type']-1];
			$rra = $row['type'];
			$graph = $row['local_graph_id'];
			$file_array[] = array(
				'graph_start'	 => $stime,
				'graph_end'	 => $etime,
				'local_graph_id' => $graph,
				'rra_id' 	 => $rra,
//				'file' 		 => "$httpurl/graph_image.php?local_graph_id=$graph&rra_id=$rra&graph_start=$stime&graph_end=$etime",
				'file' 		 => "",
				'mimetype' 	 => 'image/png',
				'filename'	 => $graph,
				'RptArray'   => $temp_RptArray);
		}
		report_mail($report['email'], $from, $subject, $message, $file_array);
		$temp = db_fetch_assoc("update reports set lastsent = " . time() . " where id = " . $report['id']);
	}
}

/* Sends a group of graphs to a user, also used for thresholds */

function report_mail($to, $from, $subject, $message, $filename, $headers = '') {
    global $config;
    include_once($config["base_path"] . "/plugins/reports/class.phpmailer.php");
    $mail = new PHPMailer();

    $how = read_config_option("thold_how");
    if ($how < 1 && $how > 3)
        $how = 1;
    if ($how == 1) {
        $mail->IsMail();                                      // set mailer to use PHPs Mailer Class
    } else if ($how == 2) {
        $mail->IsSendmail();                                  // set mailer to use Sendmail
        $sendmail = read_config_option("thold_sendmail_path");
        if ($sendmail != '')
            $mail->Sendmail = $sendmail;
    } else if ($how == 3) {
        $mail->IsSMTP();                                      // set mailer to use SMTP
        $smtp_host = read_config_option("thold_smtp_host");
        $smtp_port = read_config_option("thold_smtp_port");
        $smtp_username = read_config_option("thold_smtp_username");
        $smtp_password = read_config_option("thold_smtp_password");
	if ($smtp_username != '' && $smtp_password != '') {
            $mail->SMTPAuth = true;
            $mail->Username = $smtp_username;
            $mail->Password = $smtp_password;
        } else {
            $mail->SMTPAuth = true;
	}
	$mail->Host = $smtp_host;
	$mail->Port = $smtp_port;
    }

    if ($from == '') {
        $from = read_config_option("thold_from_email");
        $fromname = read_config_option("thold_from_name");
	if ($from == "")
            $from = "IowaTelecomCDSReports@" . $_SERVER['HOSTNAME'];
	if ($fromname == "")
            $fromname = "IowaTelecom CDS Reports";

        $mail->From = $from;
        $mail->FromName = $fromname;
    } else {
        $mail->From = $from;
	$mail->FromName = 'IowaTelecom CDS Reports';
    }
    $to = explode(',',$to);

    foreach($to as $t)
        $mail->AddAddress($t);

    $mail->WordWrap = 50;                                 // set word wrap to 50 characters
    $mail->IsHTML(true);                                  // set email format to HTML

    $mail->Subject = $subject;
    $mail->Body    = $message . '<br>';
    $mail->AltBody = strip_tags($message);
    $mail->CreateHeader();
    if (is_array($filename)) {
        foreach($filename as $val) {
            $graph_data_array = array("output_flag" => RRDTOOL_OUTPUT_STDOUT, 'graph_start' => $val['graph_start'], 'graph_end' => $val['graph_end']);
            $data = rrdtool_function_graph($val['local_graph_id'], $val['rra_id'], $graph_data_array);
            if ($data != "") {
                $cid = md5(uniqid(time()));
                $mail->AddStringEmbedAttachment($data, $val['filename'].'.png', $cid, 'base64', $val['mimetype']);    // optional name
                $mail->Body .= "<br><br><img src='cid:$cid'>";
				if ( (is_array($val['RptArray'])) && ($val['RptArray']['details'] == 1)) {
					if ($average == 'hourly') {
						$prnt_avg = "<font size=1>(hour)</font>";
					} else {
						$prnt_avg = "";
					}
	                $mail->Body .= "<br><div align=left>
										<table border=1 cellspacing=0 cellpadding=0>
											<thead>
												<tr>
													<th colspan=".(count($val['RptArray']['avg_data'])+1)." bgcolor=#000080>
														<font color=#FFFFFF><b><center><font size=2>".$val['RptArray']['report_header']."</font></center></b></font>
													</th>
												</tr>
												<tr>
													<th bgcolor=#C0C0C0><font size=2>Date </font>" .  $prnt_avg . "</th>";
					$tmp_keys=array_keys($val['RptArray']['avg_data']);
					foreach($tmp_keys as $col_name) {
						$mail->Body .="<th bgcolor=#C0C0C0><font size=2>$col_name</font></th>\n";
					}
					$firstkey=$tmp_keys[0];
					$mail->Body .="</tr>\n</thead><!--$firstkey-->";
					foreach($val['RptArray']['avg_data'][$firstkey] as $date=>$traffic) {
						$mail->Body .= "<tr>\n";
						$mail->Body .= "<td><font size=2>".$date."</font></td>\n";
						foreach($tmp_keys as $col_name) {
//							if ($val['RptArray']['avg_data'][$col_name][$date] <= 0) {
								$mail->Body.="<td><font size=2>".number_format($val['RptArray']['avg_data'][$col_name][$date],2,".",",")."</font></td>";
//							} else {
//								$mail->Body.="<td><font size=2>No Data</font></td>";
//							}
						}
						$mail->Body .= "</tr>";
					}
					$mail->Body .= "<tr><td colspan=".(count($val['RptArray']['avg_data'])+1).">
										<center><b><font size=2>*** END of report ***</font></b>
									</td></tr></table></div>";
				}
            } else {
                $mail->Body .= "<br><img src='" . $val['file'] . "'>";
                $mail->Body .= "<br>Could not open!<br>" . $val['file'];
            }
        }
        $mail->AttachAll();
    }
    // echo "<!--".$mail->Body."-->";
    if(!$mail->Send()) {
        echo "<br><center>Message could not be sent. </center><p align=center>";
        echo "<center>Mailer Error: " . $mail->ErrorInfo . "</center>";
        return;
    } else {
		echo "<br><center>Email Sent!\n</center>";
/*		print "<pre>";
		print_r ($filename);
		print "</pre>";
*/		return;
    }
}

?>

Thank you all for any help...

[gandalf]

You may have a look at the new data export function featured with cacti 0.8.6k beta2. This will later be published as 0.8.7. I'm not sure, whether CDEF will work on this new function.
Reinhard

[SirAldemar]

Gandalf,

Will I not still need the reports plug-in for v0.8.6K in order to be able to e-mail my reports to specific e-mail address?

[gandalf]

Yep. I was under the impression that you might improve this plugin even more. My last statement was meant as giving you a direction were to look
Reinhard

[SirAldemar]

Yep. I was under the impression that you might improve this plugin even more. My last statement was meant as giving you a direction were to look
Reinhard

Thank you gandalf.

Do you know if there has been or plans to be any updates to the Reports plugin to bring it in line with this newer release?

I am still very much an amature at this type of programming and do not want to re-invent any wheels. Plus I am sure there is a lot more involved with this plug-in then I am aware of.

BTW: Are there any good doc's out there for developing plug-ins? One that would explain the different functions and where to find them?

[gandalf]

The best resource currently is http://cactiusers.org. You may start by copying a very basic plugin, e.g. "Update".
Reinhard