#!/usr/local/bin/php

<?php
/**********************************************************/
// This PHP script acquires SYSLOG warning message and sends mail to the corresponding user.

// Add in syslog.conf as: local1.=warning <TAB> | /path/sendreportmail.php
// The warning message is reported by SYSLOG when a MAC address in watchlist table 
// is detected by opengatemd.

// As this script should be used only by the administrators, 
// it should be protected by some access control method.

// Following parameters should be modified properly.
/**********************************************************/

/*** parameters ***/
$mysqlServer="localhost";
$mysqlUser="root";
$mysqlPassword="";
$mailSender="opengate@cc.saga-u.ac.jp";
$reportInterval="6 HOUR";

// open syslog
openlog('sendreportmail', LOG_PID, LOG_LOCAL1);

// get mac address and others from syslog warning message such as 
// "Sep 29 12:34:56 opengate01 opengatemd[1234]: WARN: find mac=11:22:33:44:55:66 ip=192.168.0.10"
list($timestamp, $gatewayName, $macAddress, $ipAddress)=getDataFromSyslog();
if($timestamp=="?") return;

// prepare database 
if(!($link=prepareMysql($mysqlServer, $mysqlUser, $mysqlPassword))) return;

// get mail address and others relating to the mac address from mysql db
if(!(list($device, $mailAddress)=getDataFromMysql($macAddress))){
	mysqli_close($link);
	return;
}

// if reported recently, skip reporting
if(skipReporting($link, $macAddress, $gatewayName, $reportInterval)){
	mysqli_close($link);
	return;
}

// close database
mysqli_close($link);

// send mail to the user
sendMailToUser($mailSender, $mailAddress, $device, $gatewayName, 
		$ipAddress, $timestamp);
return;


/***
get MAC address and others from syslog 
***/
function getDataFromSyslog(){
	$timestamp="?";
	$gatewayName="?";
	$macAddress="?";
	$ipAddress="?";

	// syslog message is acquired from STDIN (piped to syslog output)
	if(($message=fgets(STDIN))==FALSE){
		syslog(LOG_INFO, 'ERR: Fail to read from stdin');
		return FALSE;
	}

	// extract timestamp, gateway and macaddress by regular expression
	if(preg_match('/^(.*) (.*) .* WARN: find mac=(.*) ip=(.*)/',
	 $message, $matches)==1){
		$timestamp = $matches[1];
		$gatewayName = $matches[2];
		$macAddress = $matches[3];
		$ipAddress = $matches[4];
	}else{
		syslog(LOG_INFO, 'ERR: Fail to analyze syslog message');
	}
	return array($timestamp, $gatewayName, $macAddress, $ipAddress);
}

/***
prepare mysql connection
***/
function prepareMysql($mysqlServer, $mysqlUser, $mysqlPassword){

	// connect and access to MySql DB
	$link = mysqli_connect($mysqlServer, $mysqlUser, $mysqlPassword);
	if (!$link){
		syslog(LOG_INFO, 'ERR: Cannot connect DB '.mysqli_error());
		return FALSE;
	}

	// use opengatem database
	$db_selected = mysqli_select_db($link, 'opengatem');
	if (!$db_selected){
		syslog(LOG_INFO, 'ERR: Cannot select DB '.mysqli_error());
		return FALSE;
	}
	mysqli_set_charset($link, 'utf8');
	return $link;
}

/***
get mail address and others corresponding to the MAC address from mysql 
***/
function getDataFromMysql($macAddress){
	$device="?";
	$mailAddress="?";

	// query
	$result = mysqli_query($link, 'SELECT device, mailAddress FROM macaddrs 
		WHERE macAddress="'.$macAddress.'" AND status!="D"');
	if (!$result){
		syslog(LOG_INFO, 'ERR: Fail DB query '.mysqli_error());
		return FALSE;
	}

	// get result
	if($row = mysqli_fetch_row($result)){
		$device = $row[0];
		$mailAddress = $row[1];
	}else{
		syslog(LOG_INFO, 'ERR: Fail to get mail address from DB');
		return FALSE;
	}

	return array($device, $mailAddress);
}

/***
To avoid to send too many mails, 
 skip if there are recent logs having same macaddress and same gateway. 
If you want to change the report period, $reportPeriod is defined at the top of this file.
***/
function skipReporting($link, $macAddress, $gatewayName, $reportInterval){
	
	// query
	$result = mysqli_query($link, 'SELECT count(*) FROM sessionmd '
		.'WHERE EXISTS (SELECT * FROM sessionmd '
		.'WHERE macAddress="'.$macAddress.'" '
		.'AND gatewayName LIKE "'.$gatewayName.'.%" '
		.'AND openTime > NOW() - INTERVAL '.$reportInterval.' '
		.'AND openTime < NOW() - INTERVAL 1 MINUTE '
		.')'
		);

	if (!$result){
		return FALSE; // if table doesn't exist, no skip 
	}

	// get data
	if($row = mysqli_fetch_row($result)) $count = $row[0];
	else	$count = 0;

	// if recent logs exist, skip is true
	if($count>0)return TRUE;
	else return FALSE;
}

/***
send mail to the user mail address
***/
function sendMailToUser($mailSender, $mailAddress, $device, $gatewayName, 
		$ipAddress, $timestamp){
	
	$to=$mailAddress;
	$subject="Your device is detected";
	$message="Your device ".$device
	." is detected as ip=".$ipAddress
	." on the subnet under ".$gatewayName
	." at ".$timestamp
	.". "
	." If it is not your use, please contact to the administrator.";
	$headers="From: ".$mailSender."\n";
	$parameters="-f ".$mailSender;

	if(mb_send_mail($to, $subject, $message, $headers, $parameters)){
		syslog(LOG_INFO, 'INFO: Success to send mail');
		return TRUE;
	}else{
		syslog(LOG_INFO, 'ERR: Fail to send mail');
		return FALSE;
	}
}
?>