The script only runs dbi to extract data user details from an existing database for caching the queries are of the form "select * from table". And this stage only takes about 5 seconds to run of 5-7 minutes. By far the majority of the time is spent on the actual processing.
The file reads as shown below. I've changed some of the variable that contained usernames, passwords, database, and table names. The only libraries that are from out side are dbi and socket. The main loop down the bottom loops over each filename supplied on the command line - looping over each flow record and tying that to a user bases on certain rules, then it prints it out in a radius detail file format.
Feel free to tear it apart. I intend making more of this publicly available in some form when it's finished.
#!/usr/bin/ruby
···
#
$LOAD_PATH << File.join(File.dirname(__FILE__),"lib")
OUTFILE = './details'
require 'Vflow'
require 'socket'
require 'dbi'
require 'velinfo'
require 'zones'
require 'radacct'
require 'traffic_record'
require 'iphash'
# both velinfo and zones use the same database.
dbh = if `hostname`.chomp! == 'testmachine'
DBI.connect('dbi:Mysql:db','root','')
else
DBI.connect('dbi:Mysql:db:127.0.0.1','user_test','testpasswd')
end
# build database of user information
puts "vinfo"
$vinfo = Velinfo.new(dbh)
$vinfo.loadcache
# build database of zone information
puts "zones"
$zones = Zones.new(dbh)
$zones.loadcache
# build database of radonline
puts "radacct"
$ra = Radacct.new(dbh)
$ra.online_table = 'db2.online_table' unless `hostname`.chomp! == 'testmachine'
$ra.loadcache
# make a trie to hold records
$traf_record = Hash.new
def print_usage()
File.open(OUTFILE,File::CREAT|File::APPEND|File::RDWR,0644) do |fp|
$traf_record.each_value do |r|
fp.puts r
fp.puts
end
end
end
def find_user_info(only_when_on,user_addr)
# 1. entry in radonline -> record_usage
# 2. entry in velradiatorauth
# a. address is primary address
# i. if not only when online record_usage
# b. address is network address
# i. if not only when online record_usage
# ii. use primary address to find radonline entry
# iii. if found record_usage
rauser = $ra.find_by_ip(user_addr)
#puts "#{__LINE__} #{rauser and rauser.framed_ip_address.class}"
return rauser unless rauser.nil?
if (viuser = $vinfo.find_by_ip(user_addr))
#puts "#{__LINE__} #{viuser.framed_ip_address.class}"
return viuser unless only_when_on
if viuser.replyattr.has_key?(:Framed_IP_Address) and !only_when_on
if viuser.replyattr[:Framed_IP_Address] == ip
return viuser
else
return nil
end
end
if viuser.replyattr.has_key?(:Subnet)
subnet = viuser.replyattr[:Subnet]
subnet_gateway = viuser.replyattr[:Subnet_Gateway]
if subnet.include?(user_addr)
return viuser unless only_when_on
return viuser if $ra.find_by_ip(subnet_gateway)
end
end
end
return nil
end
def record_usage(vrec)
srcaddr = IPSocket.getaddress(vrec.srcaddr)
dstaddr = IPSocket.getaddress(vrec.dstaddr)
zdstinfo = $zones.find_info_by_ip(dstaddr)
zsrcinfo = $zones.find_info_by_ip(srcaddr)
# forward traffic
if zsrcinfo and zsrcinfo.recordable
#puts "#{__LINE__}"
if (userinfo = find_user_info(zsrcinfo.only_when_on,srcaddr))
#puts "#{__LINE__} #{userinfo.framed_ip_address.class}"
zbilling = $zones.find_billing_forward(vrec)
ipkey = userinfo.framed_ip_address or userinfo.replyattr[:Subnet]
traf_rec = $traf_record[ipkey.hton]
traf_rec = TrafficRecord.new(userinfo,ipkey) if traf_rec.nil?
traf_rec.timestamp = Time.at(vrec.end_time)
traf_rec.add_zone_traffic($zones.find_zone_by_ip(dstaddr),vrec.doctets,0)
traf_rec.add_to_total(vrec.doctets,0)
traf_rec.add_to_acct(vrec.doctets,0) if zbilling.billable
$traf_record[ipkey.hton] = traf_rec
end
end
# reverse traffic
if zdstinfo and zdstinfo.recordable
#puts "#{__LINE__}"
if (userinfo = find_user_info(zdstinfo.only_when_on,dstaddr))
#puts "#{__LINE__} #{userinfo.framed_ip_address.class}"
zbilling = $zones.find_billing_reverse(vrec)
ipkey = userinfo.framed_ip_address or userinfo.replyattr[:Subnet]
traf_rec = $traf_record[ipkey.hton]
traf_rec = TrafficRecord.new(userinfo,ipkey) if traf_rec.nil?
traf_rec.timestamp = Time.at(vrec.end_time)
traf_rec.add_zone_traffic($zones.find_zone_by_ip(srcaddr),0,vrec.doctets)
traf_rec.add_to_total(0,vrec.doctets)
traf_rec.add_to_acct(0,vrec.doctets) if zbilling.billable
$traf_record[ipkey.hton] = traf_rec
end
end
end
# start reading netflow file
puts "vflow"
vf = Vflow.new
ARGV.each do |vfile|
vf.open(vfile)
count = 0
vf.each do |vfent|
count+=1
#break if count > 50
#puts "#{vfent.srcaddr} -> #{vfent.dstaddr}"
record_usage(vfent)
end
vf.close
puts "file #{vfile} contained #{count} records"
print_usage()
end
Jeff.
On 04/02/2005, at 1:15 PM, Michael Walter wrote:
As Charles pointed out, the string-fumbling should merely be a fallback path.
Michael
On Fri, 4 Feb 2005 04:11:02 +0900, Michael Neumann <mneumann@ntecs.de> > wrote:
Charles Mills wrote:
Michael Neumann wrote:
(...)
Well, I think Ruby/DBI is not the fastest. It parses (and splits)
each
SQL statement, then joins it back into a string, even if you don't
use
'?' parameter markers. This should be delayed and omitted if no
parameters were given. Not sure whether this is the reason for the
slowness.
This may be a dumb question, but why does ruby/dbi do that? For
databases that don't support precompiled statements and binding
parameters?
Yeah, good question. It tries to abstract over the database. Well,
sometimes it's nicer to write:
dbh.execute("INSERT INTO tab values (?, ?, ?)", a, b, c)
instead of:
dbh.execute("INSERT INTO tab values (#{ quote(a) }, #{ quote(b) }, #{
quote(c) })")
Regards,
Michael