IP/VPN

Материал из BiTel WiKi

Скрипт для сбора и прописывания интерфейсов для модуля IPN нужно прописать ID модуля, адрес базы данных, логин/пароль в нее и SNMP community

#!/usr/bin/env ruby -wKU

require 'rubygems'
gem 'snmp'
require 'snmp'
require 'active_record/vendor/mysql'

@mid=9
@community='public'

@dbh = Mysql.connect('192.168.1.1', 'user', 'pass', 'bgbilling')
unless @dbh
  puts "Can't connect to database"
  exit 1
end
@dbh.query("set names utf8");

# get all sources 
@ipn_src = {}
@dbh.query("select id,host_or_dir from source where mid='#{@mid}'").each { |row|  @ipn_src[row[1]] = row[0] }

def update(src_id,i_id,i_name,z_id)
  id=0
  res = @dbh.query("select id from ipn_iface_#{@mid} where source_id='#{src_id}' and number='#{i_id}'");
  res.each do |row|
    id = row[0]
  end
  @dbh.query("replace into ipn_iface_#{@mid}(id, source_id, number, title, zone_id) values(#{id},#{src_id},#{i_id}, '#{i_name}', #{z_id})")
  #puts "#{id}, #{i_id}, '#{i_name}', #{z_id}"
end

@ipn_src.each do |host, id|
  @dbh.query("start transaction")
  @dbh.query("delete from ipn_iface_#{@mid} where source_id='#{id}'")
  @dbh.query("replace into ipn_iface_#{@mid}(id, source_id, number, title, zone_id) values(0,#{id},-1, 'ANY', -3)")
  begin
    SNMP::Manager.open(:Host => host, :Community=>@community, :Timeout => 0.7, :Retries => 5 ) do |m|
      m.walk("ifName") do |row|
        row.each do |x|
          x.name.to_s =~ /(\d+)$/
          update(id, $1, x.value, -1)
        end
      end
    end
  rescue SNMP::RequestTimeout, Errno::ECONNREFUSED, Errno::EHOSTUNREACH, Errno::ENOBUFS
    @dbh.query("rollback")
  end
  @dbh.query("commit")
  # validate check that all interfaces in ipn_user_source_? are present
    
  res = @dbh.query("select c.title, c.comment, sn.title, s.iface " + 
    "from ipn_user_range_#{@mid} r join ipn_user_source_#{@mid} s on r.id=s.aid "+
    "join contract c on r.cid=c.id join source sn on sn.id=s.source_id "+
    "where s.source_id=#{id} and s.iface not in (select number from ipn_iface_9 where source_id=#{id})")
  res.each { |row| STDERR.puts "ERROR: #{row[0]} #{row[1]} interface #{row[2]}:#{row[3]} not defined" }
end