Perl script that gathers all data from network.

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.)
#   TN: Thomas Norberg 

# History
#   11-NOV-2018 (TN): 
#     Added optional lookup of macvendor.
#     Added mdns to macs table.
#     Added interface option.
#   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;
use LWP::Simple;

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

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

# 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 $opt{"interface"} --localnet`;
my @lines = split("\n", $scan);

# create the macs table
$stmt = qq(CREATE TABLE IF NOT EXISTS macs(mac TEXT NOT NULL PRIMARY KEY UNIQUE, count INTEGER, description TEXT, ip_address TEXT, mac_vendor TEXT, mdns_local TEXT, online INTEGER););
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
$sth->finish();

#Update the online values to 0
$stmt = qq(UPDATE macs SET online=0;);
$sth = $dbh->prepare( $stmt );
$rv = $sth->execute();
$sth->finish();
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(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";

		my $contents = "";
		if (defined($opt{"maclookup"})) {
			$contents = get("https://macvendors.co/api/vendorname/$mac");
		}

		if (!defined( $mdns )) {
			$stmt = qq(SELECT mdns_local FROM macs WHERE mac="$mac";);
			$sth = $dbh->prepare( $stmt );
			$rv = $sth->execute();
			my @row = $sth->fetchrow_array();
			$sth->finish();
			if(defined( $row[0] )){
				$mdns = $row[0];
			} else {
				$mdns = "";
			}
		}
		$stmt = qq(INSERT OR REPLACE INTO macs (mac, count, description, ip_address, mac_vendor, mdns_local, online) VALUES ("$mac",$previous+1,"$desc","$ip","$contents","$mdns", 1));
		$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;

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

Adding online status