#!/usr/bin/ruby
#! ruby -EUTF-8
# -*- mode:ruby; coding:utf-8 -*-

require 'pry'
require 'mysql'
require 'net/ssh'
require 'net/scp'
require File.dirname(__FILE__) + "/../module/Common.rb"
require File.dirname(__FILE__) + "/Db.rb"

class Rrd
	include Common
# <---- Rrdパスリスト取得
	def get_rrdlist
	# <---- cactiからrrdファイルパス取得
		_sql = "
select
	host.hostname as snmp_ipaddr,
	host.description as hostname,
	data_template_data.data_source_path
from
	host
	inner join data_local on host.id = data_local.host_id
	inner join data_template_data on data_local.id = data_template_data.local_data_id
where
	data_template_data.data_source_path like '%path_rra%'
"
		_jrrd = Dbope.new.sqlexec("10.199.16.149","cactiuser","usen440","cacti",_sql)
		_srrd = Dbope.new.sqlexec("10.210.136.4","cactiuser","usen440","cacti",_sql)
		_mrrd = Dbope.new.sqlexec("10.199.16.138","cactiuser","usen440","cacti",_sql)
	# ---->
		@rrdlist = []
		@rrdlist.push "hostname,rrdpath,ipaddr"
		_jrrd.each {|_r|
			@rrdlist.push [
				_r[:hostname],
				_r[:data_source_path].gsub('<path_rra>','10.199.16.149:/var/www/cacti/rra'),
				_r[:snmp_ipaddr]
			].join(",")
		}
		_srrd.each {|_r|
			@rrdlist.push [
				_r[:hostname],
				_r[:data_source_path].gsub('<path_rra>','10.210.136.4:/var/www/cacti/rra'),
				_r[:snmp_ipaddr]
			].join(",")
		}
		_mrrd.each {|_r|
			@rrdlist.push [
				_r[:hostname],
				_r[:data_source_path].gsub('<path_rra>','10.199.16.138:/var/www/cacti/rra'),
				_r[:snmp_ipaddr]
			].join(",")
		}
		@tmpfile = Tempfile.new(File.basename(__FILE__),"/dev/shm")
		@rrdlist.each { |_row| @tmpfile.write "#{_row}\n" }
		@tmpfile.close
		return CSV.table(@tmpfile,col_sep:',')
	end
# ---->
# <---- trafficのRrdパスリスト取得
	def get_trafficrrdlist
	# <---- cactiからrrdファイルパス取得
		_sql = "
select
	host.hostname,
      	data_template_data.data_source_path,
        graph_templates_graph.title_cache
from
  	host,
       	data_local,
       	data_template_data,
       	graph_local,
       	graph_templates_graph,
       	host_snmp_query
where
       	host.id = data_local.host_id and
       	host.id = host_snmp_query.host_id and
       	host.id = graph_local.host_id and
       	host_snmp_query.snmp_query_id = data_local.snmp_query_id and
	host_snmp_query.snmp_query_id = graph_local.snmp_query_id and
	data_local.snmp_index = graph_local.snmp_index and
       	data_local.id = data_template_data.local_data_id and
       	data_local.host_id = graph_local.host_id and
       	graph_local.id = graph_templates_graph.local_graph_id and
	graph_templates_graph.title_cache like '%Traffic%'
"
		@rrdlist = []
		_jrrd = Dbope.new.sqlexec("10.199.16.149","cactiuser","usen440","cacti",_sql)
		_srrd = Dbope.new.sqlexec("10.210.136.4","cactiuser","usen440","cacti",_sql)
		_mrrd = Dbope.new.sqlexec("10.199.16.138","cactiuser","usen440","cacti",_sql)
	# ---->
	# <---- racktablesからホスト-サービス名紐付け
		_sql="
			select
				SERVICE.string_value as service_name ,
				SERVICE.name as host_name,
				IPv4Allocation.name as nic_name,
				inet_ntoa(IPv4Allocation.ip) as ipaddr
			from
				(select
					RackObject.id
				from
					RackObject,
					Attribute,
					AttributeValue
				where
					RackObject.id = AttributeValue.object_id and
					AttributeValue.attr_id = Attribute.id and
					Attribute.id = 10117 and
					AttributeValue.string_value = '本番' ) as ENV,
				(select
					RackObject.id,
					RackObject.name,
					AttributeValue.string_value
				from
					RackObject,
					Attribute,
					AttributeValue
				where
					RackObject.id = AttributeValue.object_id and
					AttributeValue.attr_id = Attribute.id and
					Attribute.id = 10020) as SERVICE,
				IPv4Allocation
			where
				SERVICE.id = ENV.id and
				SERVICE.id = IPv4Allocation.object_id
"
	_service = Dbope.new.sqlexec("10.198.131.139","root","stdpass!!!","racktables_db",_sql)
	# ---->
		@rrdlist.push "ipaddr,rrdpath,title"
		_jrrd.each {|_r|
			@rrdlist.push [
				_r[:hostname],
				_r[:data_source_path].gsub('<path_rra>','10.199.16.149:/var/www/cacti/rra'),
				_r[:title_cache]
			].join(",")
		}
		_srrd.each {|_r|
			@rrdlist.push [
				_r[:hostname],
				_r[:data_source_path].gsub('<path_rra>','10.210.136.4:/var/www/cacti/rra'),
				_r[:title_cache]
			].join(",")
		}
		_mrrd.each {|_r|
			@rrdlist.push [
				_r[:hostname],
				_r[:data_source_path].gsub('<path_rra>','10.199.16.138:/var/www/cacti/rra'),
				_r[:title_cache]
			].join(",")
		}
		@traffic_csv = []
		_service.each { |_r|
			unless _r[:nic_name].nil? then
				_tmp = @rrdlist.select{|_r2| 
					_r2.include?(_r[:host_name])
				}.select{|_r2| 
					_r2.include?(_r[:nic_name].split(":")[0])
				}.join(",").split(",")
				unless _tmp.empty? then
					_tmp = [
						_r[:host_name],
						_r[:ipaddr],
						_r[:service_name]
					].concat(_tmp)
					@traffic_csv.push [
						_tmp[0],
						_tmp[1],
						_tmp[3],
						_tmp[2],
						_tmp[4]
					].join(",")
				end
			end
		}
		@traffic_csv = @traffic_csv.sort
		@traffic_csv.unshift "host_name,ipaddr,ipaddr_snmp,service_name,rrdpath"
		return @traffic_csv
	end
# ---->
# <---- RRDファイルからのデータ抽出
	def getinfo(_hostfilepath)
		_host = _hostfilepath.split(":")[0]
		$filepath = _hostfilepath.split(":")[1]
		$sshdata = []
		Net::SSH.start(_host,'usen') do |_ssh|
			$sshdata.push _ssh.exec!("rrdtool fetch #{$filepath} AVERAGE -r 86400 -s -1day | head -1").split
			$sshdata.push _ssh.exec!("rrdtool fetch #{$filepath} AVERAGE -r 86400 -s -1day | tail -n +3 | head -1").split(":")[1].split
			$sshdata.push _ssh.exec!("rrdtool fetch #{$filepath} MAX -r 86400 -s -1day | tail -n +3 | head -1").split(":")[1].split
			$sshdata.push _ssh.exec!("rrdtool fetch #{$filepath} MIX -r 86400 -s -1day | tail -n +3 | head -1").split(":")[1].split
		end
		@tmpfile = Tempfile.new(File.basename(__FILE__),"/dev/shm")
		$sshdata.each { |_row|
			@tmpfile.write _row.join(",")
			@tmpfile.write "\n"
		}
		@tmpfile.close
		return CSV.table(@tmpfile,col_sep:',')
	end
end
