Script to convert ISC dhcp lease files into sqlippool entries.

diff --git a/scripts/isc2ippool.pl b/scripts/isc2ippool.pl
new file mode 100755 (executable)
index 0000000..899ea69
--- /dev/null
+++ b/scripts/isc2ippool.pl
@@ -0,0 +1,195 @@
+#!/usr/bin/perl
+
+# isc2ippool   Insert ISC DHCPD lease entries into SQL database (ippool).
+#
+#    This program is free software; you can redistribute it and/or modify
+#    it under the terms of the GNU General Public License as published by
+#    the Free Software Foundation; either version 2 of the License, or
+#    (at your option) any later version.
+#
+#    This program is distributed in the hope that it will be useful,
+#    but WITHOUT ANY WARRANTY; without even the implied warranty of
+#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+#    GNU General Public License for more details.
+#
+#    You should have received a copy of the GNU General Public License
+#    along with this program; if not, write to the Free Software
+#    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
+#
+#    Copyright (C) 2012 Arran Cudbard-Bell (a.cudbardb@freeradius.org)
+
+use warnings;
+use strict;
+
+use DateTime;
+use DateTime::Format::Strptime;
+use DateTime::Format::DBI;
+
+use Getopt::Long;
+use Text::DHCPLeases;
+use DBI;
+
+my $lease_file         = '/var/db/dhcpd.leases';
+my $sql_type           = 'mysql';
+my $sql_host           = 'localhost';
+my $sql_user           = 'radius';
+my $sql_pass           = 'radpass';
+my $sql_database       = 'radius';
+my $sql_table          = 'radippool';
+my $pool_name          = '';
+my $pool_key           = 'Calling-Station-Id';
+my $insert_only        = 1;
+
+my $verbose;
+my $help;
+
+sub error {
+       print STDERR @_, "\n";  
+       exit(64);
+}
+
+sub notice {
+       if ($verbose) {
+               printf(shift . "\n", @_);
+       }
+}
+
+sub help {
+       my @this = split('/', $0);
+       print <<HELP
+$this[$#this] [options] <pool>
+
+Options:
+       -leases <lease file>    - The lease file to parse (defaults to '$lease_file')
+       -pool-key <attribute>   - The attribute used to identify the user (defaults to '$pool_key')
+       -no-update              - Don't update existing lease entries
+       -type                   - SQL database type (defaults to '$sql_type')
+       -table                  - SQL table (defaults to '$sql_table')
+       -h | -host              - SQL host to connect to
+       -u | -user              - SQL user
+       -p | -pass              - SQL password
+       -v                      - Verbose
+       -help                   - This help text
+HELP
+;
+       exit(0);
+}
+
+GetOptions (
+       'leases=s'      => \$lease_file,
+       'pool-key=s'    => \$pool_key,
+       'no-update'     => \$insert_only,
+       'type=s'        => \$sql_type,
+       'table=s'       => \$sql_table,
+       'h|host=s'      => \$sql_host,
+       'u|user=s'      => \$sql_user,
+       'p|pass=s'      => \$sql_pass,
+       'v'             => \$verbose,
+       'help'          => \$help
+) or error('Failed parsing options');
+
+#
+# Poolname must be provided, and we need at least some arguments...
+#
+help if !scalar @ARGV or ($pool_name = $ARGV[$#ARGV]) =~ /^-/;
+
+-r $lease_file or
+       error("Lease file ($lease_file) doesn\'t exist or isn't readable");
+
+my $leases = Text::DHCPLeases->new(file => $lease_file) or
+       error("Failed parsing leases file (or lease file empty)");
+
+my $handle = DBI->connect(
+       "DBI:$sql_type:database=$sql_database;host=$sql_host",
+       $sql_user, $sql_pass, {RaiseError => 1}
+);
+
+my $dt_isc = DateTime::Format::Strptime->new(pattern => '%Y/%m/%d %H:%M:%S');
+my $dt_sql = DateTime::Format::DBI->new($handle);
+
+for my $lease ($leases->get_objects) {
+       next unless ($lease->binding_state && $lease->binding_state eq 'active');
+       
+       my($query, @result);
+       eval {
+               $query = $handle->prepare("
+                       SELECT expiry_time, framedipaddress FROM $sql_table
+                       WHERE pool_name = ?
+                       AND callingstationid = ?;"
+                       , undef);
+
+               $query->bind_param(1, $pool_name);
+               $query->bind_param(2, $lease->mac_address);
+               
+               $query->execute();
+               
+               @result = $query->fetchrow_array();
+       };
+       
+       error($@) if $@;
+       
+       my $ends_isc = $dt_isc->parse_datetime($lease->ends =~ m{^(?:[0-9]+) (.+)});
+       
+       if (!$query->rows) {
+               eval {
+                       $handle->do("
+                               INSERT INTO $sql_table (
+                                       pool_name, framedipaddress,
+                                       calledstationid, callingstationid
+                                       expiry_time, pool_key)
+                               VALUES (?, ?, ?, ?, ?, ?);"
+                               , undef
+                               , $pool_name
+                               , $lease->ip_address
+                               , '00:00:00:00:00:00'
+                               , $lease->mac_address
+                               , $dt_sql->format_datetime($ends_isc)
+                               , $pool_key
+                       );
+               };
+               
+               error($@) if $@;
+               
+               notice("MAC:'%s' inserted with IP:'%s'.", 
+                       $lease->mac_address, $lease->ip_address);
+               
+               next;
+       }
+
+       my $ends_sql = $dt_sql->parse_datetime($result[0]);
+       
+       if ($insert_only && (($result[1] ne $lease->ip_address) ||
+               (DateTime->compare($ends_sql, $ends_isc) < 0))) {
+
+               eval {
+                       $handle->do("
+                               UPDATE $sql_table
+                               SET 
+                                       framedipaddress = ?, expiry_time = ?
+                               WHERE pool_name = ?
+                               AND callingstationid = ?;"
+                               , undef
+                               , $lease->ip_address
+                               , $dt_sql->format_datetime($ends_isc)
+                               , $pool_name
+                               , $lease->mac_address
+                       );
+               };
+               
+               error($@) if $@;
+
+               notice("MAC:'%s' updated. ISC-TS: '%s', SQL-TS: '%s', ISC-IP: '%s', SQL-IP: '%s'.",
+                       $lease->mac_address,
+                       $dt_sql->format_datetime($ends_isc),
+                       $dt_sql->format_datetime($ends_sql),
+                       $lease->ip_address,
+                       $result[1]);
+               
+               next;
+       }
+       
+       notice("MAC:'%s' skipped (no update %s). ",
+               $lease->mac_address, $insert_only ? 'allowed' : 'needed');
+}
+
+exit(0);