Script uses an ARP scan to counter the number of devices on the network and saves this information to an SQLite3 database. Outputs the current number of devices to /dev/ttyUSB0.

counter_serial.pl

#!/usr/bin/env perl

#+

# Name:
#   counter.pl

# Language:
#   Perl

# Purpose:
#   Count the number of devices on a network

# Description
#   Script uses an ARP scan to counter the number of devices on the network 
#   and saves this information to an SQLite3 database. Database will be created
#   the first time the script is run.

# External Modules:
#   Getopt::Long
#   DBI
#   DataTime
#   Device::SerialPort

# Authors:
#   ANA: Alasdair Allan (Babilim Light Industries)

# History
#   06-FEB-2017 (ANA):
#     * Moved serial port opening back to start of script. Arduino failed to init quickly enough.
#   06-FEB-2017 (ANA):
#     * Checking for duplicate MAC address with multiple IP. Assuming it's a single device.
#     * Moved opening serial port to just before we write to the port.
#   04-FEB-2017 (ANA):
#     * Added serial output to Arduino.
#   03-FEB-2017 (ANA):
#     * Added optional lookup of mDNS forward address of scanned hosts.
#   02-FEB-2017 (AA):
#     * Original Version

# Copyright:
#   Copyright (C) 2017. Babilim Light Industries.  Released under the MIT License.

#-

# L O A D   M O D U L E S --------------------------------------------------

use strict;
use warnings;

use DBI;
use Getopt::Long;
use DateTime;
use Device::SerialPort;

# O P T I O N S   H A N D L I N G --------------------------------------------

my ( %opt );
my $status = GetOptions("network=s" => \$opt{"network"},
                        "dig"       => \$opt{"dig"});
$opt{"network"} = "network" unless defined $opt{"network"};

# S E R I A L   P O R T -----------------------------------------------------

my $port = Device::SerialPort->new("/dev/ttyUSB0");
$port->baudrate(9600);
$port->databits(8);
$port->parity("none");
$port->stopbits(1);

# R U N   S C A N -----------------------------------------------------------

print "\nSCANNING\n--------\n";

my ($stmt, $sth, $rv);

my $dbfile = "/home/pi/$opt{'network'}.db";
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");

my $scan = `arp-scan --retry=8 --ignoredups -I wlan0 --localnet`;
my @lines = split("\n", $scan);

my $csv = undef;
my $count = 0;
foreach my $line (@lines) {
	chomp($line);
	if ($line =~ /^\s*((?:\d{1,3}\.){3}\d{1,3})\s+((?:[a-fA-F0-9]{2}:){5}[a-fA-F0-9]{2})\s+(\S.*)/) {
		my $ip = $1;
		my $mac = $2;
		my $desc = $3;
		print "IP=$ip, MAC=$mac, DESC=$desc";
		
		# Dig for the mDNS name associated with the IP
		my $mdns = undef;
		if (defined($opt{"dig"})) {
			
			$stmt = qq(CREATE TABLE IF NOT EXISTS mdns(mac TEXT NOT NULL PRIMARY KEY UNIQUE, mdns TEXT););
			$sth = $dbh->prepare( $stmt );
			$rv = $sth->execute();
			$sth->finish();			
			
			my $dig = `dig -x ${ip} \@224.0.0.251 -p 5353`;
			#print $dig;
			
			my @report = split("\n", $dig);
			my $answer = 0;
			my $local = undef;
			foreach my $entry (@report) {
				chomp($entry);
				if ( $answer == 1 ) {
					$local = $entry;
					last;
				}
				if( $entry eq ";; ANSWER SECTION:") {
					$answer = 1;
				}
				
			}
			if ( defined $local ) {
				#print "local name = $local\n";
				( $mdns ) = ($local =~ /\s+(\S+\.local)\.$/);
				print ", LOCAL=$mdns\n";
			
				$stmt = qq(INSERT OR REPLACE INTO mdns (mac, mdns) VALUES ("$mac","$mdns"));
				$sth = $dbh->prepare( $stmt );
				$rv = $sth->execute();
				$sth->finish();	
				
			} else {
				print "\n";
			}
		} else {
			print "\n";
		}
		
		my $duplicate = undef;
		if(defined $csv) {
			if( $csv =~ /$mac/ ) {
				$duplicate = 1;
			}
		}

		if ( !$duplicate ) {
			$stmt = qq(CREATE TABLE IF NOT EXISTS macs(mac TEXT NOT NULL PRIMARY KEY UNIQUE, count INTEGER, description TEXT););
			$sth = $dbh->prepare( $stmt );
			$rv = $sth->execute();
			$sth->finish();

			$stmt = qq(SELECT count FROM macs WHERE mac="$mac";);
			$sth = $dbh->prepare( $stmt );
			$rv = $sth->execute();
			my @row = $sth->fetchrow_array();
			$sth->finish();

			my $previous;
			if (defined( $row[0] )) {
				$previous = $row[0]
			} else {
				$previous = 0;
			}
			print "Previously seen '$previous' times\n";

			$stmt = qq(INSERT OR REPLACE INTO macs (mac, count, description) VALUES ("$mac",$previous+1,"$desc"));
			$sth = $dbh->prepare( $stmt );
			$rv = $sth->execute();
			$sth->finish();

			if (!defined $csv) {
				$csv = "$mac";
			} else {
				$csv = $csv . ",$mac";
			}
			$count++;
		} else {
			print "Ignoring duplicate MAC '$mac'\n";
		}

	}
}

my $time = DateTime->now->iso8601;
print "\nRESULT\n------\n";
print "time = $time\n";
print "count = $count\n";
print "csv = $csv\n";

# S E R I A L   P O R T -----------------------------------------------------

print "Sending a count of $count to the serial port.\n";
$port->write($count);

# S A V E  S C A N  T O  D A T A B A S E ---------------------------------------

$stmt = qq(CREATE TABLE IF NOT EXISTS scan(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, datetime TEXT UNIQUE, count INTEGER, macs TEXT););
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
$sth->finish();

$stmt = qq(INSERT INTO scan (datetime, count, macs) VALUES ("$time",$count,"$csv"));
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
$sth->finish();

# L A S T   O R D E R S -----------------------------------------------------

$stmt = qq(CREATE TABLE IF NOT EXISTS days(date TEXT UNIQUE NOT NULL PRIMARY KEY, average INTEGER, samples TEXT););
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
$sth->finish();

print "\nAVERAGE\n-------\n";
my $day = DateTime->now->ymd('-');
print "Today is $day\n";
$stmt = qq(SELECT samples FROM days WHERE date="$day";);
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
my @row = $sth->fetchrow_array();
$sth->finish();

my $samples;
if (defined( $row[0] )) {
	 $samples = $row[0];
} else {
	 $samples = undef;
}
my @values;
if (!defined $samples) {
	$values[0] = $count;
	$samples = "$count";
} else {
 	@values = split(',', $samples);
	push (@values, $count);
	$samples = $samples . ",$count";
}	

print "Samples = $samples\n";
#print "values = @values\n";
 
my $average = 0; 
foreach my $value (@values) {
	$average = $average + $value;
}  
$average = int($average/scalar(@values));

$stmt = qq(INSERT OR REPLACE INTO days (date, average, samples) VALUES ("$day",$average,"$samples"));
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
$sth->finish();

print "Current running average for today is $average devices on the network.\n";

$port->close();
$dbh->disconnect();
exit;

# ---------------------------------------------------------------------------