scripts/isc2ippool.pl

   1 #!/usr/bin/perl
   2
   3 # isc2ippool    Insert ISC DHCPD lease entries into SQL database (ippool).
   4 #
   5 #    This program is free software; you can redistribute it and/or modify
   6 #    it under the terms of the GNU General Public License as published by
   7 #    the Free Software Foundation; either version 2 of the License, or
   8 #    (at your option) any later version.
   9 #
  10 #    This program is distributed in the hope that it will be useful,
  11 #    but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 #    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 #    GNU General Public License for more details.
  14 #
  15 #    You should have received a copy of the GNU General Public License
  16 #    along with this program; if not, write to the Free Software
  17 #    Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
  18 #
  19 #    Copyright (C) 2012 Arran Cudbard-Bell (a.cudbardb@freeradius.org)
  20
  21 use warnings;
  22 use strict;
  23
  24 use DateTime;
  25 use DateTime::Format::Strptime;
  26 use DateTime::Format::DBI;
  27
  28 use Getopt::Long;
  29 use Text::DHCPLeases;
  30 use DBI;
  31
  32 my $lease_file          = '/var/db/dhcpd.leases';
  33 my $sql_type            = 'mysql';
  34 my $sql_host            = 'localhost';
  35 my $sql_user            = 'radius';
  36 my $sql_pass            = 'radpass';
  37 my $sql_database        = 'radius';
  38 my $sql_table           = 'radippool';
  39 my $pool_name           = '';
  40 my $pool_key            = 'Calling-Station-Id';
  41 my $insert_only         = 0;
  42
  43 my $verbose;
  44 my $help;
  45
  46 sub error {
  47         print STDERR @_, "\n";
  48         exit(64);
  49 }
  50
  51 sub notice {
  52         if ($verbose) {
  53                 printf(shift . "\n", @_);
  54         }
  55 }
  56
  57 sub help {
  58         my @this = split('/', $0);
  59         print <<HELP
  60 $this[$#this] [options] <pool>
  61
  62 Options:
  63         -leases <lease file>    - The lease file to parse (defaults to '$lease_file')
  64         -pool-key <attribute>   - The attribute used to identify the user (defaults to '$pool_key')
  65         -no-update              - Don't update existing lease entries
  66         -type                   - SQL database type (defaults to '$sql_type')
  67         -table                  - SQL table (defaults to '$sql_table')
  68         -h | -host              - SQL host to connect to
  69         -u | -user              - SQL user
  70         -p | -pass              - SQL password
  71         -v                      - Verbose
  72         -help                   - This help text
  73 HELP
  74 ;
  75         exit(0);
  76 }
  77
  78 GetOptions (
  79         'leases=s'      => \$lease_file,
  80         'pool-key=s'    => \$pool_key,
  81         'no-update'     => \$insert_only,
  82         'type=s'        => \$sql_type,
  83         'table=s'       => \$sql_table,
  84         'h|host=s'      => \$sql_host,
  85         'u|user=s'      => \$sql_user,
  86         'p|pass=s'      => \$sql_pass,
  87         'v'             => \$verbose,
  88         'help'          => \$help
  89 ) or error('Failed parsing options');
  90
  91 #
  92 # Poolname must be provided, and we need at least some arguments...
  93 #
  94 help if !scalar @ARGV or ($pool_name = $ARGV[$#ARGV]) =~ /^-/;
  95
  96 -r $lease_file or
  97         error("Lease file ($lease_file) doesn\'t exist or isn't readable");
  98
  99 my $leases = Text::DHCPLeases->new(file => $lease_file) or
 100         error("Failed parsing leases file (or lease file empty)");
 101
 102 my $handle = DBI->connect(
 103         "DBI:$sql_type:database=$sql_database;host=$sql_host",
 104         $sql_user, $sql_pass, {RaiseError => 1}
 105 );
 106
 107 my $dt_isc = DateTime::Format::Strptime->new(pattern => '%Y/%m/%d %H:%M:%S');
 108 my $dt_sql = DateTime::Format::DBI->new($handle);
 109
 110 for my $lease ($leases->get_objects) {
 111         next unless ($lease->binding_state && $lease->binding_state eq 'active');
 112
 113         my($query, @result);
 114         eval {
 115                 $query = $handle->prepare("
 116                         SELECT expiry_time, framedipaddress FROM $sql_table
 117                         WHERE pool_name = ?
 118                         AND callingstationid = ?;"
 119                         , undef);
 120
 121                 $query->bind_param(1, $pool_name);
 122                 $query->bind_param(2, $lease->mac_address);
 123
 124                 $query->execute();
 125
 126                 @result = $query->fetchrow_array();
 127         };
 128
 129         error($@) if $@;
 130
 131         my $ends_isc = $dt_isc->parse_datetime($lease->ends =~ m{^(?:[0-9]+) (.+)});
 132
 133         if (!$query->rows) {
 134                 eval {
 135                         $handle->do("
 136                                 INSERT INTO $sql_table (
 137                                         pool_name, framedipaddress,
 138                                         calledstationid, callingstationid
 139                                         expiry_time, pool_key)
 140                                 VALUES (?, ?, ?, ?, ?, ?);"
 141                                 , undef
 142                                 , $pool_name
 143                                 , $lease->ip_address
 144                                 , '00:00:00:00:00:00'
 145                                 , $lease->mac_address
 146                                 , $dt_sql->format_datetime($ends_isc)
 147                                 , $pool_key
 148                         );
 149                 };
 150
 151                 error($@) if $@;
 152
 153                 notice("MAC:'%s' inserted with IP:'%s'.",
 154                         $lease->mac_address, $lease->ip_address);
 155
 156                 next;
 157         }
 158
 159         my $ends_sql = $dt_sql->parse_datetime($result[0]);
 160
 161         if ($insert_only && (($result[1] ne $lease->ip_address) ||
 162                 (DateTime->compare($ends_sql, $ends_isc) < 0))) {
 163
 164                 eval {
 165                         $handle->do("
 166                                 UPDATE $sql_table
 167                                 SET
 168                                         framedipaddress = ?, expiry_time = ?
 169                                 WHERE pool_name = ?
 170                                 AND callingstationid = ?;"
 171                                 , undef
 172                                 , $lease->ip_address
 173                                 , $dt_sql->format_datetime($ends_isc)
 174                                 , $pool_name
 175                                 , $lease->mac_address
 176                         );
 177                 };
 178
 179                 error($@) if $@;
 180
 181                 notice("MAC:'%s' updated. ISC-TS: '%s', SQL-TS: '%s', ISC-IP: '%s', SQL-IP: '%s'.",
 182                         $lease->mac_address,
 183                         $dt_sql->format_datetime($ends_isc),
 184                         $dt_sql->format_datetime($ends_sql),
 185                         $lease->ip_address,
 186                         $result[1]);
 187
 188                 next;
 189         }
 190
 191         notice("MAC:'%s' skipped (no update %s). ",
 192                 $lease->mac_address, $insert_only ? 'allowed' : 'needed');
 193 }
 194
 195 exit(0);