Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris

put dhcp leases into database

by varalaxmibbnl (Acolyte)
on Jan 17, 2014 at 10:25 UTC ( #1070930=perlquestion: print w/replies, xml ) Need Help??
varalaxmibbnl has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks

i have a dhcp server which gives ip to the as soon as the lease is created i want to put that lease details into database...

#!/usr/bin/perl use POSIX qw(strftime); use File::Copy; #use strict; use Fcntl qw(:flock); open(my $script_fh, '<', $0) or die("Unable to open script source: $!\n"); unless (flock($script_fh, LOCK_EX|LOCK_NB)) { print "$0 is already running. Exiting.\n"; exit(1); } copy("/var/lib/dhcp3/dhcpd.leases","presentfile") or die "Copy failed: + $!" ; #open (STDERR,">","dhcp_err"); open (LEASE, ">","leftleases"); open (FILE,"<","prevfile"); open (MYFILE,"presentfile"); open (SFILE,">","dhcp.sql"); $line1 = <MYFILE>; @cnt_file = <FILE>; $cnt = @cnt_file; while ($line1 ne ""){ $line1 = <MYFILE>; print LEASE "$line1" if ($. > $cnt); } open(LEASE, "<","leftleases"); foreach my $line (<LEASE>) { chomp($line); if ($line =~ /^lease/) { $ip = (split(" ", $line))[1]; } if ($line =~ /^ starts/) { ($sdate, $stime) = (split(" ", $line))[2,3]; } if ($line =~ /^ ends/) { ($edate, $etime) = (split(" ", $line))[2,3]; } if ($line =~ /^ hardware ethernet/) { $mac = (split(" ", $line))[2]; $mac =~ s/;//; } if ($line =~ /^ client-hostname/) { $client = (split(/\"/, $line))[1]; } if ($line =~ /^ binding state/) { $binding_state = (split(" ", $line))[2]; $binding_state =~ s/;//; } if ($line =~ /^ next binding state/) { $next_binding_state = (split(" ", $line))[3]; $next_binding_state =~ s/;//; } if ($line =~ /^}/) { print SFILE ("INSERT INTO dhcp_leases \(start_date,start_time,end_date +,end_time,ip_address,mac_address,binding_state,next_binding_state,cli +ent_hostname\)VALUES\(\'$sdate\',\'$stime\',\'$edate\',\'$etime\',\'$ +ip\',\'$mac\',\'$binding_state\',\'$next_binding_state\',\'$client\'\ +)\n"); $ip = ""; $sdate = ""; $stime = ""; $edate = ""; $etime = ""; $mac = " +"; $client = ""; $binding_state = ""; $next_binding_state = ""; } } close(LEASE); unlink("prevfile"); copy("presentfile","prevfile"); open(FILE,"<","$path/log/dhcp.sql"); my @host=<FILE>; chomp(@host); foreach my $host(@host) { my $driver = "mysql"; my $database = "dhcp"; my $dsn = "DBI:$driver:database=$database"; my $userid = "root"; my $password = 'password'; my $dbh = DBI->connect($dsn, $userid, $password ) or die $DBI::errstr; my $sth = $dbh->prepare("$host"); $sth->execute() or die $DBI::errstr; $sth->finish(); }

i tried with this but i have to put this script in cronjob for every 5min but i want to automate it using any dhcp module can anyone please please help me

thank you very much in advance

Replies are listed 'Best First'.
Re: put dhcp leases into database
by karlgoethebier (Prior) on Jan 17, 2014 at 12:07 UTC

    Doesn't your script work?

    Or does your cronjob fail but it works on the command line?

    Or don't you know how to cron?

    Or what else is your question?

    Best regards, Karl

    «The Crux of the Biscuit is the Apostrophe»

      Thanks for the reply

      my script works for but some times it repeats the data in database... i just don't want to repeat the data in soon as the lease is created i want it to be written it into database...

        " repeats the data in database..."

        Does that mean the same data is written twice or more to your database?

        If so, i guess a bit :-( Can't you remember the mtime of dhcpd.leases and write the data only to your DB if dhcpd.leases was updated?

        But this might get a little bit more complicated:

        "...Every time a lease is acquired, renewed or released, its new value is recorded at the end of the lease file. So if more than one declaration appears for a given lease, the last one in the file is the current one..."


        "...In order to prevent the lease database from growing without bound, the file is rewritten from time to time. First, a temporary lease database is created and all known leases are dumped to it. Then, the old lease database is renamed /var/lib/dhcpd/dhcpd.leases~. Finally, the newly written lease database is moved into place..."

        See also dhcpd.leases.

        Update: Net::ISC::DHCPd::Leases might be interesting. I didn't use it but perhaps it's worth to take a look at.

        Regards, Karl

        «The Crux of the Biscuit is the Apostrophe»

Re: put dhcp leases into database
by Anonymous Monk on Feb 26, 2014 at 00:17 UTC
    Can you post mysql Structure as i would like to try to get this working for me. ie mysql < /file.format.mysql

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1070930]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (5)
As of 2018-01-21 07:46 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (227 votes). Check out past polls.