802.1x supplicants can be monitored and logged with mySQL and a simple php code. “Allow network connection” permission is required for mySQL connection. mySQL root password also can be asssinged at this moment. from mySQL CLI interface an additional configuration is required as written below:
servername root#mysql –uroot –pabc123
mysql>CREATE DATABASE radius;
servername root# mysql -uroot -pabc123 radius < schema.sql
mqsl>GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ identified by ‘abc123’ with grant option;
/private/etc/raddb/sites-enabled/inner-tunnel
authorize {
sql}
session {
sql}
/private/etc/raddb/sql.conf
sql {
database = “mysql”
server = “localhost”
login = “root”
password = “abc123”
}
/Library/Webserver/Documents/Report.php
<html>
<title>HTML with PHP</title>
<body> <h1>Dot1x Active Users</h1>
<?php
mysql_connect(“10.10.10.10”, “root”, “abc123”) or die(mysql_error());
mysql_select_db(“radius”) or die(mysql_error());
$data = mysql_query(“SELECT * FROM radacct where acctstoptime is null”) or die(mysql_error());
Print “<table border cellpadding=3>”;
print “<tr>”;
print “<th>User Name</th>”;
print “<th>NAS IP address</th>”;
print “<th>Start Time</th>”;
print “<th>Session Time</th>”;
print “</tr>”;
while($info = mysql_fetch_array( $data )) {
print “<tr>”;
print “<td>”.$info[‘username’].”</td>”;
print “<td>”.$info[‘nasipaddress’].”</td>”;
print “<td>”.$info[‘acctstarttime’].”</td>”;
print “<td>”.timesince($info[‘acctstarttime’]).”</td>”;
print “</tr>”;
}
print “</table>”;
?>
</body>
</html>
<?php function timesince( $tsmp ) {
$diffu = array( ‘seconds’=>2, ‘minutes’ => 120, ‘hours’ => 7200, ‘days’ => 172800, ‘months’ => 5259487, ‘years’ => 63113851 );
$diff = time() – strtotime($tsmp);
$dt = ‘0 seconds ago’;
foreach($diffu as $u => $n){ if($diff>$n) {$dt = floor($diff/(.5*$n)).’ ‘.$u.’ ago’;} }
return $dt;
}?>