Code Project: Network Map Webpage

I want to start off by saying, there isn’t going to be a ton of code here, and if there is code, it’s going to be super dirty. I’m fairly good at making code for “private use” that is pretty insecure, and not so great at code that’s scrubbed up and user friendly to distribute to others.

I’ve been working a bit on some local code projects, specifically for my little private “Dashboard” that runs on my file server. One project I’ve wanted to try for a while is a dynamic network tracker tool. I’ve looked into some options available, and they all seem to run as a plug in for some complicated 3rd party analytics software that often has some goofy complicated set up procedure that’s beyond “apt-get” or even just dumping a bunch of files in a web server directory.

This project is both kind of simple and not. It was fairly simple in set up and execution, but it’s somewhat complex in design. The first job was getting a list of currently connected devices on the network. This is easily done via the command line with an arp-scan request.

sudo arp-scan --localnet

The output of which looks something like this:

Using a pipe, I can shove all of this into a text file, which contains everything above.

sudo arp-scan --localnet | scan.txt

The trick is, how to display this output on a webpage. One way would be to pull it from a database. Pulling data from MySQL is pretty easy, dumping it to a pretty looking table is also easy. The harder part is getting the output of arp-scan to MySQL in a useful manner.

This is where Python comes into play. I am sure there are other methods or languages available, but like Python, and mostly know how to use Python. Essentially, I wrote a script that would open the file, scan.txt, that was created above. I am only concerned with lines that contain IP addresses, so I used the function “is_number()” to check if the first character of each line is numeric, if it is, it runs through a couple of operations.

Firstly, the output of arp-scan is tab delimited, so I can use the “split” function on “\t”, and dump the result into an array. This gives me an array of the IP address, MAC address, and Manufacturer. This sticks a new line in with the Manufacturer, so I did a “replace” on \n in the third item of the list. Lastly, I wanted the IPs to be uniformly formatted, so I write a little function that would add in leading zeros to the IP octets.

Finally, the Python builds an SQL statement from the line’s list, and make a call to the server to insert the values. A modified version of this code that just displays the resulting SQL commands instead of executing them is below.


# Open a file

def is_number(s):
                return True                                                         except ValueError:
                return False

def format_ip(ipstring):
        octets = ipstring.split(".")
        for i in octets:
        return octets[0]+"."+octets[1]+"."+octets[2]+"."+octets[3]
        #return ipstring

def add_zero(shortstring):                                                          return "0"+shortstring

import MySQLdb

mydb = MySQLdb.connect(

mycursor = mydb.cursor()

fo = open("scan.txt", "r")
#print ("Name of the file: ",

# read each line of the list
for line in fo:
        #check for lines that contain IP addresses
        if is_number(line[0]):                                                              #Convert lines into list
                line_list = line.split("\t")
                #remove line delimitors
                #Make IP Octets 3 digits
                line_list[0] = format_ip(line_list[0])
                SQL = "INSERT INTO arpscans (ip, mac, mfg) VALUES ("+line_l$                print SQL                                                   

It’s not super pretty, but it was a quick way to make sure everything came out looking correct. The table I used is called “arpscans” and contains columns called, “ip”, “mac”, “mfg”, and “last_seen”. The time stamp is an automatically generated time stamp.

I then created a shell script that would run the arp-scan piped into scan.txt then runt he python script. I set up this script in the root crontab to run once every half hour. Root is required to run the arp-scan command, so my user crontab wouldn’t cut it. Everything ran fine when I manually did a run of the script using sudo. The PHP on the other end out output the latest values based on the time stamp to a webpage.

This is where I ran into my first major hurdle. The script wasn’t running in cron. After a lot of digging and futzing, I found that basically, when cron runs the script, it works off of different environmental variables. I had to specify in ,y bash file, specifically where each command existed. The end result looks something like this:

#!/usr/bin/env bash
/usr/sbin/arp-scan --localnet > /home/ramen/scripts/arp_sql/scan.txt
/usr/bin/python /home/ramen/scripts/arp_sql/

Eventually the scan was running and posting data automatically as expected. After a few days, I ran into my second major issue. There was simply put, way too much data for my crappy old “server” to handle. The webpage slowed to a crawl as the table contained something like 9000+ entries. It’s possible and likely that my query was also rubbish, but rather than stress more figuring it out, I modified all of the code again.

Instead of adding a new entry for every MAC address every scan, I changed it to check if there already was an entry, and simply update the last_seen time. I had originally designed the system with the idea of getting legacy data for attached devices, but decided I only really cared about a generic history.

The new webpage table now displays all devices, current and previously seen, with the last seen date.

A few issues came up on the output end as well, though none of them were super hard to correct. One, I wanted a way to sort the table by clicking the headers. There are several scripts you can toss in your code to do this online.

I also wanted more data about each device, so I added a form where I could fill in more data about each device. Specifically, the network name, if there was one, a description of what the device is, the User of the device (which family member or if it’s just a network device). This also checks and updates based on MAC address.

I also ran into an issue with MAC addresses and my Network extender. When devices are connected to the Network Extender, the first half of the MAC is replaced with the first part of the Extender’s MAC, though they retain the last half. I may eventually write some code to detect and merge these entries, but for now, I’ve simply been labeling them in the description as “(Extender)”, so I know it’s the same device on the other connection.

The final end result looks something like this:

I used to have the network super organized before I moved, but the new router doesn’t work nicely with my Pi DHCP server, so I have not gotten things quite as nicely sorted as I would like. Everything in the picture is sorted, but above .100, it’s a mess. I also can’t assign IPs to some devices at all, like the DirecTV gear or my Amazon Echos, which is really annoying.

One of my future projects will hopefully correct this, as I want to put a second router on the network with DD-WRT, between the ISP gateway and everything else.

Overall, it’s been a fun little exercise in coding that combined a lot different techniques together in a fun way.

Building A Cross Platform App with Xamarin

So, I made an app.  A for real, runs on things app.  More accurately, I followed a little tutorial to make an app, as part of the Xamarin Challenge over at  The app itself is a little weather app, it even includes location based weather and forecasting.  I know this isn’t particularly impressive but I think it’s pretty neat.

Part of the point of the exercise is that it show off the cross platform ability of Xamarin as a development plaform.  The end app uses the same code and runs on Windows 10, iOS and Android.  Unfortunately, I don’t own a MAC or an iOS device to test the iOS code but I was able to run both the Android and Windows 10 versions of the App.  I even ran the Android APK on my Fire Tablet.

I can’t say I learned a ton about how to actually make apps using Xamarin, though I plan to poke through the code provided more later.  What I learned more of was how to trouble shoot Visual Studio, which seems to be a bit more than buggy.  There is a forum set up for people looking for assistance on this contest and there are a lot of issues that all seem unrelated to each other but all related to issues with Visual Studio.

For example, I had issues getting the location based weather to work, until I went through and updated the Android Emulator files and build an emulator that ran on Android 7.  In a later step I found several of the NuGet packages weren’t installing properly, I never really figured out why but I ended up having to add them individually to each of the app platforms rather than the blanket “Install this on all platforms” system.

In the end, I did manage to get through and the app says everything was submitted and accepted.


Next Thing CHiP as a Twitter Bot

twitter-logoThere was a post that came across on Medium recently, How to Make a Twitter Bot in Under an Hour.  It’s pretty straight forward, though it seems to be pretty geared towards non “techie” types, mostly because it’s geared towards people making the bot on a Mac and it uses something called Heroku to run the bot.  Heroku seems alright, except that this sort of feels like an abuse of their free tier, and it’s not free for any real projects.

I already have a bunch of IOT stuff floating around that’s ideal for running periodic services.  I also have a VPS is I really wanted something dedicated.  So I adapted the article for use in a standard Linux environment.  I used one of my CHiPs but this should work on a Raspberry Pi, an Ubuntu box, a VPS, or pretty much anything running Linux.

The first part of the article is needed, set up a new Twitter account, or use one you already have if you have extras.  Go to, create an app and keys, keep it handy.

Install git and python and python’s twitter extension.

sudo apt-get install git

sudo apt-get install python-twitter

This should set up everything we’ll need later.  Once it’s done, close the repository.

git clone

This should download the repository and it’s files.  Next it’s time to set up the configuration files.

cd heroku_ebooks



This should open up an editor with the settings file open.  It’s pretty straight forwards, you’ll need to copy and paste the keys from Twitter into the file, there are 4 of them total, make sure you don’t leave any extra spaces inside the single quotes.  You’ll also need to add one or more accounts for the bot to model itself after.  You’ll also need to change DEBUG = TRUE to DEBUG = FALSE as well as adding your bot’s username to the TWEET_ACCOUNT=” entry at the bottom.

Once that is all done do a Control+O to write out the file and Control+X to exit.  Now it’s time to test out the bot with the following…


It may pause for a second while it does it’s magic.  If you get the message ” No, sorry, not this time.” it means the bot decided not to tweet, just run the command again until it tweets, since we’re testing it at the moment.  If it worked, it should print a tweet to the command line and the tweet should show up in the bot’s timeline.  If you get some errors, you may need to do some searching and troubleshooting, and double check the settings file.

Next we need to automate the Twitter Bot Tweets.  This is done using Linux’s built in cron.  But first we need to make our script executable.

 chmod 755

Next, enter the following….

sudo crontab -e

Then select the default option, which should be nano.  This will open the cron scheduler file.  You’ll want to schedule the bot to run according to whatever schedule you want.  Follow the columns above as a guide.  For example:

# m h  dom mon dow   command

*/15 * * * * python /home/chip/heroku_ebooks/

m = minutes = */15 = every 15 minutes of an hour (0, 15, 30, 45)

h = hour = * (every hour)

dom = day of month = * = every day and so on.  The command to run, in this case, is “python /home/chip/heroku_ebooks/”.  If you’re running this on a Raspberry Pi, or your own server, you will need to change “chip” to be the username who’s directory has the files.  Or, if you want to put the files elsewhere, it just needs to b e the path to the files.  For example, on a Raspberry Pi, it would be “python /home/pi/heroku_ebooks/”.

If everything works out, the bot should tweet on schedule as long as the CHIP is powered on and connected.  Remember, by default the bot only tweets 1/8th of the time when the script is run (this can be adjusted in the settings file), so you may not see it tweet immediately.

This is also a pretty low overhead operation, you could conceivably run several Twitter Bots on one small IOT device, with a staggered schedule even.  Simply copy the heruko_ebooks directory to a new directory, change the keys and account names and set up a new cron job pointing to the new directory.

Pushing Arduino Data to MySQL via PHP Part 2: The Server

tempdataIn the last post, I talked about how to send data from the Arduino to the MySQL server.

Today, I’ll cover how I’m receiving and displaying that data on the server side.  I’ll put the code for the webpages into this post but I’ll put everything together in a nice little package at the end of everything.  I mentioned last post that the end trick was to let the server worry about processing the data instead of the Arduino.  I’m running this on my NAS but it could be run on any standard web server with PHP and SQL.

For the purposes of this code, I’ve placed the files in a directory called “temps” on the root of the web server.  If you want to put them elsewhere, such as “temperaturelog” or “home/temps” or wherever, you’d need to alter the code of the Arduino in the previous points to replace the temps directory with the directory you plan to use.  I’m going to assume that the reader has a basic LAMP (Linux, Apache, MySQL, PHP) stack style server and knows the basics of how to create databases and run things on it.  If not you can Google the basic set up, though I may do a quick write up and reference it here eventually.

You’ll need to create a database called “housetemps” and import the linked structure file into it.  (If you know what you’re doing in PHP you can rename housetemps)   This will create a table int he database called “temperature” with the following columns: id, event, sensor, celsius, humidity, datestamp.  This will give you the basic structure needed to run the php code on the server.  A quick rundown of what these values are used for…

  • id – A standard auto incriminating id value for SQL
  • event – Time and date of when the event happened
  • sensor – A text based identifier for each probe.  This example uses one probe but could be altered for more.
  • celsius – The temperature reading, it doesn’t actually have to be Celsius, that’s just the default of the probe.
  • humidity – Humidity reading in percent humidity
  • datestamp – A date only time stamp, used for sorting the data when you review it later.

Now that you have the core database structure set up, the server needs to know the credentials for connecting to the database.  Since this is used by several files, it’s best to put the info in it’s own file and use an include statement int he PHP to add it.

In the “temps” directory create a file dbconnect.php and add int he following code, changing the values listed for the values used when setting up the database…

$MyUsername = “USERNAME”;  // enter your username for mysql
$MyPassword = “PASSWORD”;  // enter your password for mysql
$MyHostname = “localhost”;      // this is usually “localhost” unless your database resides on a different server

$dbh = mysql_pconnect($MyHostname , $MyUsername, $MyPassword);
$selected = mysql_select_db(“housetemps”,$dbh);

Next you’ll need a way to get data into the table.  This is done using add_data.php, which is called by the Arduino in the code shown previously.

Make a file in the “temps” directory called add_data.php, and add the following code.

// Connect to MySQL

$dateget = date(“Y-m-d”);
// Prepare the SQL statement
$SQL = “INSERT INTO housetemps.temperature (sensor ,celsius, humidity, datestamp) VALUES (‘”.$_GET[“serial”].”‘, ‘”.$_GET[“temperature”].”‘, ‘”.$_GET[“humid”].”‘, ‘$dateget’)”;

// Execute SQL statement

// Go to the review_data.php (optional)
header(“Location: review_data.php”);

The code is pretty straight forward. The first section includes our previously created log on credentials.  The last section forwards the page on to review the data.  The meat is in the middle with the SQL statement.  The line $dateget = date(“Y-m-d”); creates a Year-Month-Day date for sorting later.  The next line creates the SQL query using variables passed via the url, the third part executes this query adding the data to the database.

The final piece of this whole thing is the review_data.php file.  This file displays the results in a nice looking table.

// Start MySQL Connection

<title>Arduino Temperature Log</title>
<style type=”text/css”>
.table_titles, .table_cells_odd, .table_cells_even {
padding-right: 20px;
padding-left: 20px;
color: #000;
.table_titles {
color: #FFF;
background-color: #666;
.table_cells_odd {
background-color: #CCC;
.table_cells_even {
background-color: #FAFAFA;
table {
border: 2px solid #333;
body { font-family: “Trebuchet MS”, Arial; }

<h1>Arduino Temperature Log</h1>

<p>Select Date:

$sql = “SELECT DISTINCT datestamp FROM temperature”;
$result = mysql_query($sql);

echo “<select name=’datestamp’ onchange=’location = this.options[this.selectedIndex].value;'”;
while ($row = mysql_fetch_array($result)) {
$current = $row[‘datestamp’];
echo “<option value=’review_data.php?dateselect=$current’>$current</option>”;
echo “</select>”;

<table border=”0″ cellspacing=”0″ cellpadding=”4″>
<!–    <td class=”table_titles”>ID</td> –>
<td class=”table_titles”>Date and Time</td>
<td class=”table_titles”>Sensor Serial</td>
<td class=”table_titles”>Temperature in Celsius</td>
<td class=”table_titles”>Percent Humidity</td>
// Retrieve all records and display them
$SQL = “SELECT * FROM temperature WHERE datestamp LIKE ‘”.$_GET[“dateselect”].”%’ ORDER BY id ASC”;

//Execute the SQL
$result = mysql_query($SQL);

// Used for row color toggle
$oddrow = true;

// process every record
while( $row = mysql_fetch_array($result) )
if ($oddrow)
$css_class=’ class=”table_cells_odd”‘;
$css_class=’ class=”table_cells_even”‘;

$oddrow = !$oddrow;

echo ‘<tr>’;
//       echo ‘   <td’.$css_class.’>’.$row[“id”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“event”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“sensor”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“celsius”].'</td>’;
echo ‘   <td’.$css_class.’>’.$row[“humidity”].'</td>’;
echo ‘</tr>’;

The core of this file was lifted from the previously mentioned guide on Tweaking4All.  The problem I had with their results was that it simply displayed an endless list.  I am polling every 15 minutes, so this list tends to grow unwieldy very quickly.  This is where the datestamp marker comes into play.  I added this little chunk of code at the top of the table.


$sql = “SELECT DISTINCT datestamp FROM temperature”;
$result = mysql_query($sql);

echo “<select name=’datestamp’ onchange=’location = this.options[this.selectedIndex].value;'”;
while ($row = mysql_fetch_array($result)) {
$current = $row[‘datestamp’];
echo “<option value=’review_data.php?dateselect=$current’>$current</option>”;
echo “</select>”;

This creates a menu based on unique values of datestamp.  Selecting a value forwards you to review_data.php with a date attached, then review_data.php only shows data that matches that datestamp.  This allows a single day to be viewed easily.  In the future I may add a bit more to this menu, eventually, the list of dates will also become unwieldy in length.  I also plan to run numerous sensors at once with different names so I’ll likely also add a second menu so the list can be sorted down by sensor name.

I’m also looking to add a bit more functionality to the code in the form of a graph, so it will be easier to see trend lines in the data.  The data I have now is from my office, in a well insulated basement room, so the trend lines are rather boring, but when there are several sensors in different rooms in the main area of the house, or even outside, this data becomes more interesting and useful.  I’ll go into this at a later day though…



Pushing Arduino Data to MySQL via PHP

Arduino+EthernetThis is part of my little ongoing project of learning with the Arduino.  I want to give a mention to Tweaking4all’s guide to PHP, SQL and Arduino, because I started out using it as a base for this section of the project, though I altered a few parts to work with my sensors and Ethernet board.  That guide definitely pointed me in the right direction and made me realize just how simple it would be to actually push data to the server.

My initial approach to the problem was that I needed the Arduino to run some SQL queries and interact directly with the database and insert readings and other variables.  In the end, all I needed was a PHP file on the server to interact with the database, and the proper call from the Arduino to the PHP file.

I started out with basic code to dump dummy data (ie not variables) to a database running on my laptop.  I couldn’t get UIPEthernet linked int he Tweaking4all guide to work so I just used the default Arduino Ethernet libraries.  I also simplified the code down to one probe, though adding more temperature probes will be trivial.  After I managed to get the dummy data to post reliably, I moved on to adding variables in place of the dummy values.  Once again, I couldn’t get the OneWire library to load properly and work with my senors, so I went back to the basic DHT-11 library that I knew work.  This actually simplified things considerably, I used the same calls I had done previously in testing and instead of pushing them directly tot he serial port, I dumped them to some variables which are then passed to the SQL statement.

I also added variables and functions to read Humidity, since he original article doesn’t have humidity readings included.

The final hurdle I came across, for some reason, the delay() function wasn’t working properly.  I set it to poll every 15 minutes (in milliseconds) but it never posted a new update beyond the initial one when powered on.  If I tried a shorter interval, such as 5 minutes (in ms), I got new readings every 30 seconds or so.   In the end, I used a better method of handling time with currentmills.  This reads the current number of milliseconds since the last reading.  By reading currentmills and comparing it to the last reading “time” I can verify if it’s been 15 minutes since the last reading.  This method is not super precise and has some play on interval but I’m not doing anything requiring perfect timing with this project.

In the end, I ended up with the following code for the Arduino:

#include <SPI.h>
#include <Ethernet.h> // Used for Ethernet
#include “dht.h”

dht DHT;

#define DHT11_PIN 5

// Arduino Uno pins: 10 = CS, 11 = MOSI, 12 = MISO, 13 = SCK
// Ethernet MAC address – must be unique on your network – MAC Reads T4A001 in hex (unique in your network)
byte mac[] = { 0x54, 0x34, 0x41, 0x30, 0x30, 0x31 };
// For the rest we use DHCP (IP address and such)

EthernetClient client;
char server[] = “SERVERIP“; // IP Adres (or name) of server to dump data to
int  interval = 360000; // Wait between dumps
unsigned long previousMillis=0;

void setup() {


Serial.println(“RamenJunkie’s Ethernet Temperature Probe based on Tweaking4All Probe”);
Serial.print(“IP Address        : “);
Serial.print(“Subnet Mask       : “);
Serial.print(“Default Gateway IP: “);
Serial.print(“DNS Server IP     : “);

void loop() {

unsigned long currentMillis=millis();

if((currentMillis – previousMillis) > 900000)
Serial.print(“DHT11, \t”);
int chk = DHT.read11(DHT11_PIN);
switch (chk)
Serial.print(“Checksum error,\t”);
Serial.print(“Time out error,\t”);
Serial.print(“Unknown error,\t”);

// if you get a connection, report back via serial:
if (client.connect(server, 80)) {
Serial.println(“-> Connected”);
// Make a HTTP request:
client.print( “GET /temps/add_data.php?”);
client.print( “Probe1” );
//      client.print(“88”);
//      client.print(“88″);
client.println( ” HTTP/1.1″);
client.print( “Host: ” );
client.println( “Connection: close” );
else {
// you didn’t get a connection to the server:
Serial.println(“–> connection failed/n”);


Serial.print(”  “);


A few notes to anyone wanting to try to use this.

  • You will need to set the server IP to the IP of your database.
  • You can alter the time interval between readings with the interval variable as well.
  • If you plan to use more than one Arduino probe (which you can), you’ll want to change the MAC address settings.  it can be any MAC value really since you’re just assigning it manually but having multiple devices with the same MAC will cause issues.  I’d recommend simply increasing the last value (31) by one.
  • You can change the line “client.print( “Probe1″ );” to name the probe whatever you want.  The original code read serial numbers but I’m not sure if DHT-11 handles serial numbers or not.  I’d also recommend changing it if you run more than one Arduino probe to the same database.

This will create basic output from the Arduino.  The other half of this is to set up a server running SQL to receive the data, but I’ll cover that in the next entry.  I based it off the code linked above in Tweaking4all but altered it to take in the Humidity as well as allow for viewing individual dates.