pfsense-dhcp-leases-mysql-import.php

<?php

/*
 * 
 * The MIT License (MIT)
 * 
 * Copyright (c) 2015 Kim Henriksen
 * 
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 *
 * ISC DHCPD CLIENT LEASE IMPORTER
 * 
 * For use on eg. Pfense 
 * 
 * PHP Dependencies:
 *              https://github.com/jpereira/php-dhcpdleases
 *              php-mysql (pfense: https://doc.pfsense.org/index.php/How_do_I_get_PHP_support_for_mysql,_sqlite,_sockets,_etc)
 * 
 * 
 * DATABASE AND TABLE STRUCTURE
 * 
 * CREATE DATABASE IF NOT EXISTS `dhcp` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
 * USE `dhcp`;
 * 
 * CREATE TABLE IF NOT EXISTS `leases` (
 * `id` int(11) NOT NULL,
 *   `ip` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `time_start` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `time_end` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `binding_state` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `next_binding_state` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `hardware_ethernet` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `uid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `circuit_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 *   `client_hostname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
 * ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
 * 
 * 
 * ALTER TABLE `leases`
 *  ADD PRIMARY KEY (`id`);
 * 
 * 
 * ALTER TABLE `leases`
 * MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
 */

// Load dhcpd lease file parser dependency
require_once("class.DhcpdLeases.php");

// Create connection
$mysql = new mysqli('host', 'username', 'password', 'dhcp');

// Check connection
if ($mysql->connect_error)
{
    die("Connection failed: " . $mysql->connect_error);
} 

// Parse dhcpd lease file
$dhcpdleases = new DhcpdLeases('/var/dhcpd/var/db/dhcpd.leases');

// Process dhcpd lease fille
if(!$dhcpdleases->process() < 1)
{
    die("Failed to parse dhcpd lease database");
}

// Clear current data
if (!$mysql->query('TRUNCATE TABLE `leases`;'))
{
    die("Query failed: " . $mysql->error)
}

// Loop each lease
foreach($dhcpdleases->GetResultArray() as $lease)
{
    
    // Insert each lease into lease table
    if (!$mysql->query("INSERT INTO `leases` (ip, time_start, time_end, binding_state, next_binding_state, hardware_ethernet, uid, circuit_id, client_hostname) VALUES ('" . $lease['ip'] . "', '" . $lease['time-start'] . "', '" . $lease['time_end'] . "', '" . $lease['binding-state'] . "', '" . $lease['next-binding-state'] . "', '" . $lease['hardware-ethernet'] . "', '" . $lease['uid'] . "', '" . $lease['circuit-id'] . "', '" . $lease['client-hostname'] . "')")
    {
        die("Query failed: " . $mysql->error);
    }
}

?>