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

counter.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 count 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, tables will be created on the fly when
#   needed if they don't already exist.

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

# Authors:
#   ANA: Alasdair Allan (Babilim Light Industries, Ltd.)

# History
#   03-FEB-2017 (ANA):
#     Added optional lookup of mDNS forward address of scanned hosts.
#   02-FEB-2017 (ANA):
#     Original version of the script.

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

# 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.

#-

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

use strict;
use warnings;

use DBI;
use Getopt::Long;
use DateTime;

# 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"};

# 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";
		}
		
		$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++;
	}
}

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

$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";

$dbh->disconnect();
exit;

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