Raspberry Pi Network Spy - Part 2 - D/b Setup + PHP for the scanner

In the first part of this series, I took my new Raspberry Pi B+Raspberry Pi B+ (with translucent casetranslucent case and a default installation of NOOBSNOOBS) and set it up with a web server & gave you an overview of the database schema.

Database Creation

@Mark_Beckett flagged up that I should mention how I created the database. I'm primarily a Windows developer, so what I ended up doing was opening up access to MySQL so that I could connect to the database remotely...

mysql -u root -p
 

mysql> GRANT ALL PRIVILEGES ON *.* TO user_name@'%' IDENTIFIED BY ‘pass_word’;
mysql> flush privileges;
 

This opens up root access from any remote client.. you'll most likely want to lock this back down after you're done.

Now that it's remotely accessible, I used my laptop and a (paid-for) application called Navicat to design the database, tables, and browse the data. There are plenty of other front ends out there; MySQL Workbench is probably a good (free) bet.. http://www.mysql.com/products/workbench/

Those kind of front end tools make it super easy to create & manage your database. For this project you can just copy what you see in my table screenshots (in Part 1). You'll want to create a user account for your PHP code to access the database through, e.g.

Assign access priveleges for that user to the pispy database..

PHP Code

Now it's time for the PHP code that calls arp-scan, parses the the results, and loads them into the database. The PHP I've written isn't that elegant.. I've simply bashed this together in an hour so that I can get the project up and running.

First off we've got 3 files which can be included into our main PHP code;

_variables.php -- all the configuration used by the project, e.g. database connection details

<?php

    $dbhost="localhost";
    $dbusername="pispy";
    $dbpassword="pispy";
    $dsn="pispy";

?>
 

_db.php -- open up the d/b connection

<?php

    global $dbhost,$dbusername,$dbpassword,$dsn;
    $db = mysql_connect($dbhost,$dbusername,$dbpassword) or die(mysql_error());
    mysql_select_db($dsn) or die(mysql_error());

?>
 

_functions.php -- this contains all the useful functions the project uses

<?php

function callArp() {

        // Call arp-scan with whatever arguments we need. Returns the output in an array
        $pathToArp = "sudo /usr/bin/arp-scan 192.168.1.0/24";
        exec($pathToArp, $returned);
        return $returned;
}

function callArpDummy() {

        // Simulated data to help with testing (means we don't have to scan every time!)

        $ret = array();

        $ret[] = 'Interface: eth0, datalink type: EN10MB (Ethernet)';
        $ret[] = 'Starting arp-scan 1.8.1 with 256 hosts';
        $ret[] = '192.168.1.11  00:03:78:b8:a9:23       HUMAX Co., Ltd.';
        $ret[] = '192.168.1.16  e8:ab:fa:03:8a:6d       (Unknown)';
        $ret[] = '192.168.1.65  00:1f:1f:25:8d:a0       Edimax Technology Co. Ltd.';
        $ret[] = '192.168.1.66  70:d4:f2:2f:1f:d7       (Unknown)';
        $ret[] = '192.168.1.70  00:24:d7:0d:1f:c4       Intel Corporate';
        $ret[] = '192.168.1.202 54:04:a6:d2:ad:85       (Unknown)';
        $ret[] = '192.168.1.77  6c:ad:f8:9d:24:8a       (Unknown)';
        $ret[] = '192.168.1.254 34:8a:ae:93:70:fa       (Unknown)';
        $ret[] = '';
        $ret[] = '9 packets received by filter, 0 packets dropped by kernel';
        $ret[] = 'Ending arp-scan 1.8.1: 256 hosts scanned in 3.309 seconds (77.36 hosts/sec). 8 responded';

        return $ret;
}

function processArpLine($line) {

        // Process each line from arp-scan and break it into an array containing IP/MAC/Name

        $arpline = Array();
        if (strlen($line)>10 && substr($line, 3, 1)=='.') {
                $arpline[] = trim(substr($line, 0, 13));
                $arpline[] = trim(substr($line, 13, 18));
                $arpline[] = trim(substr($line, 31));
        }
        return $arpline;
}

function processArpReturn($ret) {

        // Loop over all the output from arp-scan and extract lines mentioning devices

        $arpEntries = Array();

        foreach($ret as $line) {
                $arpEntry = processArpLine($line);
                if (sizeof($arpEntry)>0) $arpEntries[] = $arpEntry;
        }

        return $arpEntries;
}

function getMacAddressesFromArpEntries($arpEntries) {

        // Extract just the MAC addresses from the arp-scan data

        $macArray = Array();
        foreach ($arpEntries as $arp) {
                $macArray[] = $arp[1];
        }

        return $macArray;
}

function populateDevicesTable($arpMacAddresses) {

        // Query the d/b to see which devices we've seen before

        global $db;
        $macCSV = "'".implode("', '", $arpMacAddresses)."'";
        $sql = "SELECT mac FROM devices WHERE mac IN ($macCSV)";

        $knownMacAddresses = Array();
        $query = mysql_query($sql);
        while ($row=@mysql_fetch_array($query)) {
                $knownMacAddresses[] = $row['mac'];
        }

        // Find devices that are in the arp-scan, that aren't in the d/b
        $newDevices = array_diff($arpMacAddresses, $knownMacAddresses);

        // Add each new device into the devices table with default alias (TODO - to pull in the name from arp-scan)
        foreach ($newDevices as $newDevice) {
                $sql = "INSERT INTO devices (mac, alias) VALUES ('$newDevice', 'New device');";
                $query = mysql_query($sql);
        }
}

function getAllKnownMACs() {

        global $db;
        $knownMACs = Array();
        $sql = "SELECT mac FROM devices;";
        $query = mysql_query($sql);
        while ($row=@mysql_fetch_array($query)) {
                $knownMACs[] = $row['mac'];
        }
        return $knownMACs;
}

function processNewlyOnlineDevices($arpMacAddresses) {

        global $db;

        foreach ($arpMacAddresses as $macAddress) {
                $sql = "SELECT count(*) as Count FROM activity WHERE mac = '$macAddress' AND dttm_disappeared IS NULL;";
                $query = mysql_query($sql);
                $result = mysql_fetch_assoc($query);
                $count = $result['Count'];
                // Device has appeared online
                if ($count == 0) {
                        $sql = "INSERT INTO activity (mac, dttm_appeared) VALUES ('$macAddress', NOW());";
                        $query = mysql_query($sql);
                }
        }
}

function processOfflineDevices($offlineMacAddresses) {

        global $db;

        foreach ($offlineMacAddresses as $macAddress) {
                $sql = "UPDATE activity SET dttm_disappeared = NOW() WHERE mac='$macAddress' AND dttm_disappeared IS NULL;";
                $query = mysql_query($sql);
        }
}

?>
 

Now here's the main file for the scan; this script will be called by a cron job and we'll set that up later.

arp.php

<?php
        include("_variables.php");
        include("_functions.php");
        include("_db.php");
        //$ret = callArp();
        $ret = callArpDummy();

        $arpEntries = processArpReturn($ret);

        $arpMacAddresses = getMacAddressesFromArpEntries($arpEntries);
print("MAC adresses in arp-scan:\n");
print_r($arpMacAddresses);

        populateDevicesTable($arpMacAddresses);

        $allKnownMacAddresses = getAllKnownMACs();
print("All known devices (from d/b):\n");
print_r($allKnownMacAddresses);

        // Find all the offline devices
        $offlineDevices = array_diff($allKnownMacAddresses, $arpMacAddresses);
print("All offline devices\n");
print_r($offlineDevices);

        // Add activities for newly online devices
        processNewlyOnlineDevices($arpMacAddresses);

        // Update activities for offline devices
        processOfflineDevices($offlineDevices);

?>
 

You can test this works by running it through PHP at the command prompt.

php arp.php
 

That'll send some debug out to the console showing you what it found (mostly showing you MAC addresses). On line 5 of arp.php you'll want to comment that line back in & comment out line 6 so that it enables a proper network scan, and not just a test.

Cron job

To run the scan on a schedule, you can use cron.. this command runs the cron editor;

crontab -e
 

Then you can add new items to the list.. this entry will run our PHP scanner every 5 minutes...

*/5 * * * * sudo /usr/bin/php /var/www/arp.php
 

Check it works

You should now see the activity & devices tables getting populated with data. Go into devices and you can name your stuff by setting the alias field.

To bring back the current status for each known device by running SQL like this;

select a1.*, d.alias from activity a1, devices d
where id = (select max(id) from activity a2 where a2.mac = a1.mac)
and a1.mac = d.mac
 

In the next part, I plan to extend this project to include a web service, and possibly some admin pages so that you can set the name of your devices without having to go into the database.