Биллинговая система Nodeny

#!/usr/bin/perl

## (c) 2009 Andrey Zentavr <zentavr@linet.zp.ua>
## This script sync NoDeny MySQL DB with LDAP DHCP Config

##
## ChangeLog
##

## 2009-08-20
## Edited MySQL query mac != ''
##

## 2009-08-18
## Edited SQL query
##

## 2009-08-15 v0.1
## First Release
##



use Sys::Syslog;
use DBI;
use Net::LDAP;
use POSIX qw(strftime);

my %config = (
    # MySQL
    'mysql_host' => 'localhost',
    'mysql_user' => 'bill_kernel',
    'mysql_password' => 'zxcvbn',
    'mysql_database' => 'bill',
    # LDAP
    'ldap_host' => '127.0.0.1',
    'ldap_port' => '389',
    'ldap_base_dn' => 'DC=nodeny,DC=linet,DC=zp,DC=ua',
    'ldap_login' => 'CN=Manager,DC=nodeny,DC=linet,DC=zp,DC=ua',
    'ldap_password' => 'zxcvbn',
    'ldap_version' => '3',
    # NoDeny MAC dopfield id
    'dopfield_id' => '4',
    # Other
    'now' => strftime("%s",localtime)
);


sub trim {
    my($string)=@_;
    for ($string) {
        s/^\s+//;
        s/\s+$//;
    }
    return $string;
}


##
## Lets Go!
##
openlog("dhcpmysqlldap", "ndelay,pid", "local8");
syslog(LOG_WARNING, "Process started");

##
## LDAP init
##
syslog(LOG_WARNING, "Connecting to LDAP ".$config{'ldap_host'});
$ldap = Net::LDAP->new($config{'ldap_host'}, version => $config{'ldap_version'}) or die "$@";

$mesg = $ldap->bind (
        $config{'ldap_login'},
        password => $config{'ldap_password'}
);

##
## MySQL init
##
$dbh = DBI->connect("dbi:mysql:dbname=".$config{'mysql_database'}.";host=".$config{'mysql_host'}, $config{'mysql_user'}, $config{'mysql_password'});

# Select All ip <-> Mac pair
$query="SELECT u.ip AS ip, d.field_value AS mac FROM users u, dopvalues d, (
                SELECT u.ip AS ip, MAX(d.revision) AS rev FROM users u, dopvalues d
                    WHERE u.id=d.parent_id AND d.dopfield_id=".$config{'dopfield_id'}."
                    GROUP BY ip
                ) AS tmp
            WHERE u.id=d.parent_id
            AND d.dopfield_id=".$config{'dopfield_id'}."
            AND u.ip=tmp.ip
            AND d.revision=tmp.rev
            AND TRIM(d.field_value) != ''";


($sth = $dbh->prepare($query)) or die $DBI::errstr;
($sth->execute) or die $DBI::errstr;

# Removing all pairs from LDAP
$mesg = $ldap->search(
                        base    => 'cn=DHCP Config, '.$config{'ldap_base_dn'},
                        filter  => '(objectClass=dhcpHost)',
                        scope   => 'sub'
);
# working with found DN's
for( $i=0; $i < $mesg->count; $i++) {
    #print $mesg->entry($i)->dn ."\n";
    $ldap->delete($mesg->entry($i)->dn);
}
# removed


# Processing MySQL ip-mac pairs
while(($ip,$mac)=$sth->fetchrow){
    $ip=trim($ip); $mac=trim($mac);
    # Have New mac
    syslog(LOG_NOTICE, "..got $ip -> $mac from MySQL query");
    print "$ip -> $mac\n";

    # Adding pair into LDAP server
    $result = $ldap->add( 'cn='.$ip.', cn=DHCP Config, '.$config{'ldap_base_dn'},
                    attr => [
                                'cn' => $ip,
                                'dhcpHWAddress' => "ethernet ".$mac,
                                'dhcpStatements' => "fixed-address ".$ip,
                                'objectClass' => [ 'top', 'dhcpHost', 'dhcpOptions'],
                                'dhcpOption' => "addtime \"".$config{'now'}."\""
                            ]
    );
    $result->code && warn "Failed to add entry ".$ip."/".$mac.": ", $result->error ;
}
# End cycle

$mesg = $ldap->unbind;

syslog(LOG_WARNING, "All done!");
closelog();