From: jm <jeffm@...>
Date: 2005-02-04T12:06:58+09:00
Subject: Re: yarv and dbi

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
>>
>>
>