element14 Community
element14 Community
    Register Log In
  • Site
  • Search
  • Log In Register
  • Members
    Members
    • Benefits of Membership
    • Achievement Levels
    • Members Area
    • Personal Blogs
    • Feedback and Support
    • What's New on element14
  • Learn
    Learn
    • Learning Center
    • eBooks
    • STEM Academy
    • Webinars, Training and Events
    • More
  • Technologies
    Technologies
    • 3D Printing
    • FPGA
    • Industrial Automation
    • Internet of Things
    • Power & Energy
    • Sensors
    • More
  • Challenges & Projects
    Challenges & Projects
    • Design Challenges
    • element14 presents
    • Project14
    • Arduino Projects
    • Raspberry Pi Projects
    • More
  • Products
    Products
    • Arduino
    • Dev Tools
    • Manufacturers
    • Raspberry Pi
    • RoadTests & Reviews
    • Avnet Boards Community
    • More
  • Store
    Store
    • Visit Your Store
    • Choose Another Store
      • Europe
      •  Austria (German)
      •  Belgium (Dutch, French)
      •  Bulgaria (Bulgarian)
      •  Czech Republic (Czech)
      •  Denmark (Danish)
      •  Estonia (Estonian)
      •  Finland (Finnish)
      •  France (French)
      •  Germany (German)
      •  Hungary (Hungarian)
      •  Ireland
      •  Israel
      •  Italy (Italian)
      •  Latvia (Latvian)
      •  
      •  Lithuania (Lithuanian)
      •  Netherlands (Dutch)
      •  Norway (Norwegian)
      •  Poland (Polish)
      •  Portugal (Portuguese)
      •  Romania (Romanian)
      •  Russia (Russian)
      •  Slovakia (Slovak)
      •  Slovenia (Slovenian)
      •  Spain (Spanish)
      •  Sweden (Swedish)
      •  Switzerland(German, French)
      •  Turkey (Turkish)
      •  United Kingdom
      • Asia Pacific
      •  Australia
      •  China
      •  Hong Kong
      •  India
      •  Korea (Korean)
      •  Malaysia
      •  New Zealand
      •  Philippines
      •  Singapore
      •  Taiwan
      •  Thailand (Thai)
      • Americas
      •  Brazil (Portuguese)
      •  Canada
      •  Mexico (Spanish)
      •  United States
      Can't find the country/region you're looking for? Visit our export site or find a local distributor.
  • Translate
  • Profile
Personal Blogs
  • Members
  • More
Personal Blogs
Legacy Personal Blogs Raspberry Pi Network Spy - Part 2 - D/b Setup + PHP for the scanner
  • Blogs
  • Documents
  • Mentions
  • Sub-Groups
  • Tags
  • More
  • Cancel
  • New
Blog Post Actions
  • Subscribe by email
  • More
  • Cancel
  • Share
  • Subscribe by email
  • More
  • Cancel
Group Actions
  • Group RSS
  • More
  • Cancel
Engagement
Author: mcollinge
Date Created: 15 Sep 2014 9:56 AM
Views: 137
Likes: 1
Comments: 2
  • rpibeginner
  • raspberrypi
Related
Recommended

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

mcollinge
mcollinge
15 Sep 2014

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.

Anonymous

Top Comments

  • mcb1
    mcb1 over 7 years ago +1

    Well done sir.

    That does make sense.

     

    Thanks

     

    Mark

  • glacket
    glacket over 4 years ago

    I know this is an old blog, im hoping its still active.im trying to get this working, i got everything setup, however when i run arp.php i get no data back from arpscan. i commented out line 6 and uncommented line 5. if i test the arp-scan function by itself, and specify print_r, i get data in the console. i only see data when i use the fake array, that works fine and data is written to the DB and console properly. Any idea what i am doing wrong?

    • Cancel
    • Up 0 Down
    • Reply
    • More
    • Cancel
  • mcb1
    mcb1 over 7 years ago

    Well done sir.

    That does make sense.

     

    Thanks

     

    Mark

    • Cancel
    • Up +1 Down
    • Reply
    • More
    • Cancel
Element14

element14 is the first online community specifically for engineers. Connect with your peers and get expert answers to your questions.

  • Members
  • Learn
  • Technologies
  • Challenges & Projects
  • Products
  • Store
  • About Us
  • Feedback & Support
  • FAQs
  • Terms of Use
  • Privacy Policy
  • Legal and Copyright Notices
  • Sitemap
  • Cookies

An Avnet Company © 2022 Premier Farnell Limited. All Rights Reserved.

Premier Farnell Ltd, registered in England and Wales (no 00876412), registered office: Farnell House, Forge Lane, Leeds LS12 2NE.

ICP 备案号 10220084.

Follow element14

  • Facebook
  • Twitter
  • linkedin
  • YouTube