4 /**********************************************************/
5 // This PHP script acquires SYSLOG warning message and sends mail to the corresponding user.
7 // Add in syslog.conf as: local1.=warning <TAB> | /path/sendreportmail.php
8 // The warning message is reported by SYSLOG when a MAC address in watchlist table
9 // is detected by opengatemd.
11 // As this script should be used only by the administrators,
12 // it should be protected by some access control method.
14 // Following parameters should be modified properly.
15 /**********************************************************/
18 $mysqlServer="localhost";
21 $mailSender="opengate@cc.saga-u.ac.jp";
22 $reportInterval="6 HOUR";
25 openlog('sendreportmail', LOG_PID, LOG_LOCAL1);
27 // get mac address and others from syslog warning message such as
28 // "Sep 29 12:34:56 opengate01 opengatemd[1234]: WARN: find mac=11:22:33:44:55:66 ip=192.168.0.10"
29 list($timestamp, $gatewayName, $macAddress, $ipAddress)=getDataFromSyslog();
30 if($timestamp=="?") return;
33 if(!($link=prepareMysql($mysqlServer, $mysqlUser, $mysqlPassword))) return;
35 // get mail address and others relating to the mac address from mysql db
36 if(!(list($device, $mailAddress)=getDataFromMysql($macAddress))){
41 // if reported recently, skip reporting
42 if(skipReporting($link, $macAddress, $gatewayName, $reportInterval)){
50 // send mail to the user
51 sendMailToUser($mailSender, $mailAddress, $device, $gatewayName,
52 $ipAddress, $timestamp);
57 get MAC address and others from syslog
59 function getDataFromSyslog(){
65 // syslog message is acquired from STDIN (piped to syslog output)
66 if(($message=fgets(STDIN))==FALSE){
67 syslog(LOG_INFO, 'ERR: Fail to read from stdin');
71 // extract timestamp, gateway and macaddress by regular expression
72 if(preg_match('/^(.*) (.*) .* WARN: find mac=(.*) ip=(.*)/',
73 $message, $matches)==1){
74 $timestamp = $matches[1];
75 $gatewayName = $matches[2];
76 $macAddress = $matches[3];
77 $ipAddress = $matches[4];
79 syslog(LOG_INFO, 'ERR: Fail to analyze syslog message');
81 return array($timestamp, $gatewayName, $macAddress, $ipAddress);
85 prepare mysql connection
87 function prepareMysql($mysqlServer, $mysqlUser, $mysqlPassword){
89 // connect and access to MySql DB
90 $link = mysqli_connect($mysqlServer, $mysqlUser, $mysqlPassword);
92 syslog(LOG_INFO, 'ERR: Cannot connect DB '.mysqli_error());
96 // use opengatem database
97 $db_selected = mysqli_select_db($link, 'opengatem');
99 syslog(LOG_INFO, 'ERR: Cannot select DB '.mysqli_error());
102 mysqli_set_charset($link, 'utf8');
107 get mail address and others corresponding to the MAC address from mysql
109 function getDataFromMysql($macAddress){
114 $result = mysqli_query($link, 'SELECT device, mailAddress FROM macaddrs
115 WHERE macAddress="'.$macAddress.'" AND status!="D"');
117 syslog(LOG_INFO, 'ERR: Fail DB query '.mysqli_error());
122 if($row = mysqli_fetch_row($result)){
124 $mailAddress = $row[1];
126 syslog(LOG_INFO, 'ERR: Fail to get mail address from DB');
130 return array($device, $mailAddress);
134 To avoid to send too many mails,
135 skip if there are recent logs having same macaddress and same gateway.
136 If you want to change the report period, $reportPeriod is defined at the top of this file.
138 function skipReporting($link, $macAddress, $gatewayName, $reportInterval){
141 $result = mysqli_query($link, 'SELECT count(*) FROM sessionmd '
142 .'WHERE EXISTS (SELECT * FROM sessionmd '
143 .'WHERE macAddress="'.$macAddress.'" '
144 .'AND gatewayName LIKE "'.$gatewayName.'.%" '
145 .'AND openTime > NOW() - INTERVAL '.$reportInterval.' '
146 .'AND openTime < NOW() - INTERVAL 1 MINUTE '
151 syslog(LOG_INFO, 'ERR: Fail query '.mysqli_error());
156 if($row = mysqli_fetch_row($result)) $count = $row[0];
159 // if recent logs exist, skip is true
160 if($count>0)return TRUE;
165 send mail to the user mail address
167 function sendMailToUser($mailSender, $mailAddress, $device, $gatewayName,
168 $ipAddress, $timestamp){
171 $subject="Your device is detected";
172 $message="Your device ".$device
173 ." is detected as ip=".$ipAddress
174 ." on the subnet under ".$gatewayName
177 ." If it is not your use, please contact to the administrator.";
178 $headers="From: ".$mailSender."\n";
179 $parameters="-f ".$mailSender;
181 if(mb_send_mail($to, $subject, $message, $headers, $parameters)){
182 syslog(LOG_INFO, 'INFO: Success to send mail');
185 syslog(LOG_INFO, 'ERR: Fail to send mail');