Collectd で取得した Oracle の統計情報を、Apps のところに表示するパッチ

observium-collectd-oracle.patch

Fri Jul 11 22:29:02 JST 2014  komeda@situs.co.jp
  * oracle
diff -rN -u old-observium/html/includes/graphs/application/oracle_blks.inc.php new-observium/html/includes/graphs/application/oracle_blks.inc.php
--- old-observium/html/includes/graphs/application/oracle_blks.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_blks.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,46 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$opts = array();
+#$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_sysstat', 'physical_reads', $opts);
+
+global $config;
+$sources = array();
+
+$title = $device['hostname']."/".'oracle'.(!is_null($vars['instance']) ? "-".$vars['instance'] : '')."/oracle_sysstat";
+if (!isset($opts['title']))
+	$opts['title'] = $title;
+#$opts['rrd_opts'] = array('-v', 'Mails');
+
+$files = array();
+$opts['colors'] = array(
+	'logical_reads' => '00e000',
+	'physical_reads' => 'a0e000',
+	'physical_writes' => 'a00050'
+	);
+
+$type_instances = array(
+	'session_logical_reads' => 'logical_reads',
+	'physical_reads' => 'physical_reads',
+	'physical_writes' => 'physical_writes');
+while (list($inst, $name) = each($type_instances)) {
+	$file  = '';
+	foreach ($config['datadirs'] as $datadir)
+		if (is_file($datadir.'/'.$title.'-'.$inst.'.rrd')) {
+			$file = $datadir.'/'.$title.'-'.$inst.'.rrd';
+			break;
+		}
+	if ($file == '')
+		continue;
+
+	$legend = str_replace('_', ' ', $name);
+	$sources[] = array('name'=>$name, 'file'=>$file, 'ds'=>'value', 'legend' => $legend);
+}
+
+$rrd_options .= collectd_draw_meta_line($opts, $sources);
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_connects.inc.php new-observium/html/includes/graphs/application/oracle_connects.inc.php
--- old-observium/html/includes/graphs/application/oracle_connects.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_connects.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,10 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_logons');
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_mem.inc.php new-observium/html/includes/graphs/application/oracle_mem.inc.php
--- old-observium/html/includes/graphs/application/oracle_mem.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_mem.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,42 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$opts = array();
+
+global $config;
+$sources = array();
+
+$title = $device['hostname']."/".'oracle'.(!is_null($vars['instance']) ? "-".$vars['instance'] : '')."/oracle_freememory";
+if (!isset($opts['title']))
+	$opts['title'] = $title;
+$opts['number_format'] = '%6.1lf%s';
+
+$files = array();
+$opts['colors'] = array(
+	'shared_pool' => '00e000',
+	'large_pool'   => 'a0e000',
+	'java_pool' => 'a00050'
+	);
+
+$type_instances = array('shared_pool', 'large_pool', 'java_pool');
+while (list($k, $inst) = each($type_instances)) {
+	$file  = '';
+	foreach ($config['datadirs'] as $datadir)
+		if (is_file($datadir.'/'.$title.'-'.$inst.'.rrd')) {
+			$file = $datadir.'/'.$title.'-'.$inst.'.rrd';
+			break;
+		}
+	if ($file == '')
+		continue;
+
+	$legend = str_replace('_', ' ', $inst);
+	$sources[] = array('name'=>$inst, 'file'=>$file, 'ds'=>'bytes', 'legend' => $legend);
+}
+
+$rrd_options .= collectd_draw_meta_line($opts, $sources);
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_redo_size.inc.php new-observium/html/includes/graphs/application/oracle_redo_size.inc.php
--- old-observium/html/includes/graphs/application/oracle_redo_size.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_redo_size.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,10 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_sysstat', 'redo_size');
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_redo_writes.inc.php new-observium/html/includes/graphs/application/oracle_redo_writes.inc.php
--- old-observium/html/includes/graphs/application/oracle_redo_writes.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_redo_writes.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,10 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_sysstat', 'redo_writes');
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_tablesapces.inc.php new-observium/html/includes/graphs/application/oracle_tablesapces.inc.php
--- old-observium/html/includes/graphs/application/oracle_tablesapces.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_tablesapces.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,15 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+
+$opts = array();
+if (isset($vars['legend']) && $vars['legend'] == 'no') {
+    $opts['tinylegend'] = TRUE;
+}
+$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_tablespace_pct', $vars['tinstance'], $opts);
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_tablesapces_blk.inc.php new-observium/html/includes/graphs/application/oracle_tablesapces_blk.inc.php
--- old-observium/html/includes/graphs/application/oracle_tablesapces_blk.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_tablesapces_blk.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,15 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+
+$opts = array();
+if (isset($vars['legend']) && $vars['legend'] == 'no') {
+    $opts['tinylegend'] = TRUE;
+}
+$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_tablespace_blk', $vars['tinstance'], $opts);
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_tablesapces_size.inc.php new-observium/html/includes/graphs/application/oracle_tablesapces_size.inc.php
--- old-observium/html/includes/graphs/application/oracle_tablesapces_size.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_tablesapces_size.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,15 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+
+$opts = array();
+if (isset($vars['legend']) && $vars['legend'] == 'no') {
+    $opts['tinylegend'] = TRUE;
+}
+$rrd_options .= collectd_draw_rrd($device['hostname'], 'oracle', $vars['instance'], 'oracle_tablespace_size', $vars['tinstance'], $opts);
+
+?>
diff -rN -u old-observium/html/includes/graphs/application/oracle_xact.inc.php new-observium/html/includes/graphs/application/oracle_xact.inc.php
--- old-observium/html/includes/graphs/application/oracle_xact.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/includes/graphs/application/oracle_xact.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,42 @@
+<?php
+
+include("includes/graphs/common.inc.php");
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$opts = array();
+
+global $config;
+$sources = array();
+
+$title = $device['hostname']."/".'oracle'.(!is_null($vars['instance']) ? "-".$vars['instance'] : '')."/oracle_sysstat";
+if (!isset($opts['title']))
+	$opts['title'] = $title;
+#$opts['rrd_opts'] = array('-v', 'Mails');
+
+$files = array();
+$opts['colors'] = array(
+	'user_calls' => '00e000',
+	'user_commits'   => 'a0e000',
+	'user_rollbacks' => 'a00050'
+	);
+
+$type_instances = array('user_calls', 'user_commits', 'user_rollbacks');
+while (list($k, $inst) = each($type_instances)) {
+	$file  = '';
+	foreach ($config['datadirs'] as $datadir)
+		if (is_file($datadir.'/'.$title.'-'.$inst.'.rrd')) {
+			$file = $datadir.'/'.$title.'-'.$inst.'.rrd';
+			break;
+		}
+	if ($file == '')
+		continue;
+
+	$legend = str_replace('_', ' ', $inst);
+	$sources[] = array('name'=>$inst, 'file'=>$file, 'ds'=>'value', 'legend' => $legend);
+}
+
+$rrd_options .= collectd_draw_meta_line($opts, $sources);
+
+?>
diff -rN -u old-observium/html/pages/device/apps/oracle.inc.php new-observium/html/pages/device/apps/oracle.inc.php
--- old-observium/html/pages/device/apps/oracle.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/html/pages/device/apps/oracle.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,67 @@
+<?php
+
+/**
+ * Observium
+ *
+ *   This files is part of Observium.
+ *
+ * @package    observium
+ * @subpackage applications
+ * @copyright  (C) 2006 - 2013 Adam Armstrong
+ *
+ */
+
+require('includes/collectd/config.php');
+require('includes/collectd/functions.php');
+
+$plugins = collectd_list_plugins($device['hostname']);
+if (in_array('oracle', $plugins)) {
+    $instances = collectd_list_pinsts($device['hostname'], 'oracle');
+
+    foreach ($instances as $pinst) {
+        #$types = collectd_list_types($device['hostname'], 'oracle', $pinst);
+        #foreach ($types as $type) {
+        #$tinst = collectd_list_tinsts($device['hostname'], 'oracle', $pinst, $type);
+        #foreach ($tinst as $i) {
+        #echo "<p>".$pinst." ".$type." ".$i."</p>";
+        #}
+        #}
+    }
+
+    $app_sections = array(
+                      'live' => "Live",
+		      'stats' => "Stats",
+                      'space' => "Tablespaces(%)",
+                      'block' => "Tablespaces(blocks)");
+
+    $app_graphs['stats'] = array(
+			'oracle_xact:'.$pinst => $pinst.': Oracle Commit Count',
+                        'oracle_blks:'.$pinst => $pinst.': Oracle I/O Count',
+                        'oracle_redo_size:'.$pinst => $pinst.': Oracle REDO Size',
+                        'oracle_redo_writes:'.$pinst => $pinst.': Oracle REDO Write Count',
+                         );
+
+    $app_graphs['live'] = array(
+			'oracle_connects:'.$pinst => $pinst.': Oracle Connection Count',
+                        'oracle_mem:'.$pinst => $pinst.': Oracle Free Memory',
+                        );
+
+    $tablespaces = collectd_list_tinsts($device['hostname'], 'oracle', $pinst, 'oracle_tablespace_pct');
+
+    $app_graphs['space'] = array();
+    foreach ($tablespaces as $tinst) {
+        #$filename = collectd_identifier($device['hostname'], 'oracle', $pinst, 'oracle_tablespace', $tinst);
+        $app_graphs['space']['oracle_tablesapces:'.$pinst.':'.$tinst] = $pinst.': '.$tinst.' Tablespace';
+
+    }
+
+    $tablespaces = collectd_list_tinsts($device['hostname'], 'oracle', $pinst, 'oracle_tablespace_blk');
+
+    $app_graphs['block'] = array();
+    foreach ($tablespaces as $tinst) {
+        #$filename = collectd_identifier($device['hostname'], 'oracle', $pinst, 'oracle_tablespace', $tinst);
+        $app_graphs['block']['oracle_tablesapces_blk:'.$pinst.':'.$tinst] = $pinst.': '.$tinst.' Tablespace';
+
+    }
+}
+
diff -rN -u old-observium/html/pages/device/apps.inc.php new-observium/html/pages/device/apps.inc.php
--- old-observium/html/pages/device/apps.inc.php	2014-12-19 23:15:49.000000000 +0900
+++ new-observium/html/pages/device/apps.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -69,6 +69,14 @@
     echo '<table class="table table-striped table-hover table-bordered table-condensed">';
 
     foreach ($app_graphs[$vars['app_section']] as $key => $text) {
+      $karray = explode(":", $key, 3);
+      if (count($karray) >= 2) {
+	  $key = $karray[0];
+          $graph_array['instance'] = $karray[1];
+          if (count($karray) == 3) {
+              $graph_array['tinstance'] = $karray[2];
+	  }
+      }
       $graph_type            = $key;
       $graph_array['to']     = $config['time']['now'];
       $graph_array['id']     = $app['app_id'];
diff -rN -u old-observium/includes/polling/applications/oracle.inc.php new-observium/includes/polling/applications/oracle.inc.php
--- old-observium/includes/polling/applications/oracle.inc.php	1970-01-01 09:00:00.000000000 +0900
+++ new-observium/includes/polling/applications/oracle.inc.php	2014-12-19 23:15:50.000000000 +0900
@@ -0,0 +1,25 @@
+<?php
+
+if (!empty($agent_data['app']['oracle']))
+{
+    $oracle = $agent_data['app']['oracle'];
+
+    foreach (explode("\n",$oracle) as $line) {
+        list($item,$value) = explode(":",$line,2);
+        $oracle_data[trim($item)] = trim($value);
+    }
+    $rrd_filename = $config['rrd_dir']."/".$device['hostname']."/app-oracle-".$app['app_id'].".rrd";
+
+    if (!is_file($rrd_filename)) {
+        // version, ccount, tDbs, tUsr, tHst, idle, select, update, delete, other
+        // rrdtool_create($rrd_filename, " ");
+    }
+
+    // rrdtool_update($rrd_filename, );
+
+    unset($rrd_data);
+    unset($oracle);
+    unset($oracle_data);
+}
+
+?>