Database Connection Log

Use this forum if you have problems with a hMailServer script, such as hMailServer WebAdmin or code in an event handler.
Post Reply
palinka
Senior user
Senior user
Posts: 1294
Joined: 2017-09-12 17:57

Database Connection Log

Post by palinka » 2019-07-22 21:57

For a while I've been formatting entries in my connection log to make it easy to parse in a spreadsheet using space delimiters. But its kind of a pain to retrieve, convert, sort, etc to find what I wanted, which usually pertained to a connection I wanted to investigate. While going through this hassle, I thought about doing it from a database and using a web page to search, so I recycled some of my firewall ban php.

For one thing, I wanted it to be as simple as possible, so its a single page (index.php) with everything included including searching by keyword to match most columns in the table structure, by date, and by accept/reject status.

Demo is here: http://hmsfirewallbandemo.ddns.net/accrej/ <<<<------EDIT - Demo removed temporarily because I added OnClientLogon to list of log events. When I get around to it, I'll create a sanitized second db and put the demo back up.

Prerequisites:
* MySQL
* Apache/IIS with PHP
* RvdH's OnHELO custom build (to obtain sender EHLO)


Create table:

Code: Select all

CREATE TABLE `hm_accrej` (
  `id` int(11) NOT NULL,
  `timestamp` datetime NOT NULL,
  `stringport` varchar(4) NOT NULL,
  `port` int(3) NOT NULL,
  `event` varchar(20) NOT NULL,
  `accrej` varchar(20) NOT NULL,
  `reason` varchar(20) NOT NULL,
  `ipaddress` varchar(15) NOT NULL,
  `country` varchar(30) DEFAULT NULL,
  `helo` varchar(192) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Modify EventHandlers.vbs:

Code: Select all

Private Const ADMIN = "Administrator"
Private Const PASSWORD = "supersecretpassword"

'	Credit to SorenR
Function Include(sInstFile)
   Dim f, s, oFSO
   Set oFSO = CreateObject("Scripting.FileSystemObject")
   On Error Resume Next
   If oFSO.FileExists(sInstFile) Then
      Set f = oFSO.OpenTextFile(sInstFile)
      s = f.ReadAll
      f.Close
      ExecuteGlobal s
   End If
   On Error Goto 0
End Function

'	Credit to SorenR
Function GetDatabaseObject()
   Dim oApp : Set oApp = CreateObject("hMailServer.Application")
   Call oApp.Authenticate(ADMIN, PASSWORD)
   Set GetDatabaseObject = oApp.Database
End Function

'	Credit 99.9% to SorenR
Function AccRejDB(xStringport, xPort, xEvent, xAccRej, xReason, xIPAddress, xHELO)
   Include("C:\Program Files (x86)\hMailServer\Events\VbsJson.vbs")
   Dim ReturnCode, Json, oGeoip, oXML
   Set Json = New VbsJson
   On Error Resume Next
   Set oXML = CreateObject ("Msxml2.XMLHTTP.3.0")
   oXML.Open "GET", "http://ip-api.com/json/" & xIPAddress, False
   oXML.Send
   Set oGeoip = Json.Decode(oXML.responseText)
   ReturnCode = oXML.Status
   On Error Goto 0

   Dim strSQL, oDB : Set oDB = GetDatabaseObject
   strSQL = "INSERT INTO hm_accrej (timestamp, stringport, port, event, accrej, reason, ipaddress, country, helo) VALUES (NOW(),'" & xStringport & "','" & xPort & "','" & xEvent & "','" & xAccRej & "','" & xReason & "','" & xIPAddress & "','" & oGeoip("country") & "','" & xHELO & "');"
   Call oDB.ExecuteSQL(strSQL)
End Function

'******************************************************************************************************************************
'********** hMailServer Triggers                                                                                     **********
'******************************************************************************************************************************

Sub OnHELO(oClient)

	'	Exclude local LAN & Backup from test
	If (Left(oClient.IPAddress, 8) = "192.168.") Then Exit Sub
	If (Left(oClient.IPAddress, 9) = "127.0.0.1") Then Exit Sub
	If (Left(oClient.IPAddress, 12) = "184.105.182.") Then Exit Sub

	Dim strPort
	strPort = Trim(Mid("SMTP POP  IMAP SMTPSSUBM IMAPSPOPS ", InStr("25   110  143  465  587  993  995  ", oClient.Port), 5))

	If YourFilter Then
		'
		' filter code to accept connection (my example below taken from "GeoIP" filter)
		'
		Call AccRejDB(strPort, oClient.Port, "OnHELO", "Accepted", "GeoIP", oClient.IPAddress, oClient.HELO)
	Else
		'
		' filter code to reject connection (my example below taken from "GeoIP" filter)
		'
		Call AccRejDB(strPort, oClient.Port, "OnHELO", "REJECTED", "GeoIP", oClient.IPAddress, oClient.HELO)
	End If

End Sub
The 3 hardcoded variables in the example above ("OnHELO", "REJECTED", "GeoIP") are:

1. hmailserver event - in this case, sub OnHELO
2. Accepted/REJECTED or put whatever might be appropriate for you (pass/fail, etc)
3. The filter within the event - in this case "GeoIP" but I have one for spamhaus and several others.

If you don't plan to use RvdH's custom build for OnHELO, then replace oClientHELO with double quotations: "" in order to get a null value. Or "NULL" may work (I'm not positive).

The country information will self populate in the function by geoip request.


Lastly - index.php:

Code: Select all

<?php
// Fill in variables
$m_host="localhost";
$m_dbuser="root";
$m_dbpass="supersecretpassword";
$m_db="hmailserver";

	$con=mysqli_connect($m_host,$m_dbuser,$m_dbpass,$m_db);
	if (mysqli_connect_errno()){
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
		die();
	}
?>

<!DOCTYPE html> 
<html>
<head>
<title>hMailServer Connection Log</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta http-equiv="Content-Style-Type" content="text/css">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://fonts.googleapis.com/css?family=Roboto" rel="stylesheet">
<link href="https://fonts.googleapis.com/css?family=Oswald" rel="stylesheet"> 
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="/resources/demos/style.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script type="text/javascript">
$(function () {
    $("#date").datepicker({
        dateFormat: "yy-mm-dd",
		minDate: <?php
	$query = "SELECT DATE(timestamp) Date FROM hm_accrej ORDER BY DATE(timestamp) ASC LIMIT 1";
	$exec = mysqli_query($con,$query);
	while($row = mysqli_fetch_array($exec)){
		echo "'".$row['Date']."',";
	}
?>
		maxDate: new Date,
        onSelect: function (selected) {
        }
    });
});
</script>

<style>
body {
	background: #fefefe;
	font-family: "Roboto";
	font-size: 12pt;
	}

a:link, a:active, a:visited {
	color: #FF0000;
	text-transform: underline;
	}

a:hover {
	color: #FF0000;
	text-transform: none;
	}

.header {
    position: fixed;
    top: 0;
    left: 0;
    width: 100%;
    color: #000;
	background: #fefefe;
    z-index: 1;
    overflow: hidden;
    text-align:center;
	}

.header h1 {
	font-size:25px;
    font-weight:normal;
	margin:0 auto;
	}

.header h2 {
	font-size:15px;
    font-weight:normal;
	margin:0 auto;
	}

.wrapper {
	max-width: 920px;
	position: relative;
	margin: 30px auto 30px auto;
	padding-top: 20px;
	}

.clear {
	clear: both;
	}

.banner {
	width: 100%;
	}

.headlinks {
	max-width: 720px;
	position:relative;
	margin: 0px auto;
	}

.headlinkwidth {
	width: 100%;
	min-width: 300px;
	position:relative;
	margin: 0 auto;
	}

.headlinks a:link, a:active, a:visited {
	color: #FF0000;
	text-transform: underline;
	}

.headlinks a:hover {
	color: #FF0000;
	text-transform: none;
	}

.section {
	padding: 5px 0 15px 0;
	margin: 0;
	}

.section h2 {
	font-size:16px;
    font-weight:bold;
	text-align:left;
	}

.section h3 {
	font-size:16px;
    font-weight:bold;
	}

.secleft {
	float: left;
	width: 49%;
	padding-right: 3px;
	}

.secright {
	float: right;
	width: 49%;
	padding-left: 3px;
	}

table.section {
	border-collapse: collapse;
	border: 1px solid black;
	width: 100%;
	font-size: 10pt;
	}
	
table.section th, table.section td {
	border: 1px solid black;
	}

.footer {
	width: 100%;
	text-align: center;
	}
	
ul {
	list-style-type: none;
	padding: 0;
	}

li {
	padding: 0;
	display: inline;
	}
	
@media only screen and (max-width: 629px) {
	.secleft {
		float: none ;
		width: 100% ;
		padding: 0 0 10px 0;
		text-align: left;
	}
	.secright {
		float: none ;
		width: 100% ;
	}

}	
</style>
</head>
<body>

<div class="header">
	<div class="banner"><h1><a href="./">hMailServer Connection Log</a></h1></div>
</div>

<div class="wrapper">

<?php

	if (isset($_GET['page'])) {
		$page = $_GET['page'];
		$display_pagination = 1;
	} else {
		$page = 1;
		$total_pages = 1;
		$display_pagination = 0;
	}
	if (isset($_GET['submit'])) {$button = $_GET ['submit'];} else {$button = "";}
	if (isset($_GET['search'])) {$search = mysqli_real_escape_string($con, preg_replace('/\s+/', ' ',trim($_GET['search'])));} else {$search = "";}
	if (isset($_GET['AR'])) {$AR = mysqli_real_escape_string($con, preg_replace('/\s+/', ' ',trim($_GET['AR'])));} else {$AR = "";}
	if (isset($_GET['date'])) {
		if(preg_match("/^(20[1-9][0-9]\-(0[0-9]|1[0-2])\-(0[0-9]|[1-2][0-9]|3[0-1]))$/", ($_GET['date']))) {
			$date = (mysqli_real_escape_string($con, preg_replace('/\s+/', ' ',trim($_GET['date']))));
		} else {
			$date = "";
		}
	} else {
		$date = "";
	}
	echo "<div class='section'>";
	echo "<form autocomplete='off' action='index.php' method='GET'> ";
	echo    "<input type='text' id='date' name='date' placeholder='Date...' value='".$date."' />";
	echo	" ";
	echo	"<input type='text' size='20' name='search' placeholder='Search Term...' value='".$search."'>";
	echo	" ";
	echo	"<select name='AR'>";
	echo		"<option value=''>AR</option>";
	echo		"<option value='REJ'>REJ</option>";
	echo		"<option value='ACC'>ACC</option>";
	echo	"</select>";
	echo	" ";
	echo	"<input type='submit' name='submit' value='Search' >";
	echo "</form>";
	echo "</div>";
	echo "<div class='section'>";
  
	$no_of_records_per_page = 20;
	$offset = ($page-1) * $no_of_records_per_page;
	
	if ($AR=="REJ"){$AR_SQL = " AND accrej='REJECTED'";}
	elseif ($AR=="ACC"){$AR_SQL = " AND accrej='Accepted'";}
	else {$AR_SQL = "";}
	
	if ($date==""){$date_SQL = "";}
	else {$date_SQL = " AND DATE(timestamp)='".$date."'";}
	
	$total_pages_sql = "SELECT Count( * ) AS count FROM hm_accrej WHERE (timestamp LIKE '%{$search}%' OR stringport LIKE '%{$search}%' OR port LIKE '%{$search}%' OR event LIKE '%{$search}%' OR reason LIKE '%{$search}%' OR ipaddress LIKE '%{$search}%' OR country LIKE '%{$search}%' OR helo LIKE '%{$search}%')".$AR_SQL."".$date_SQL."";
	$result = mysqli_query($con,$total_pages_sql);
	$total_rows = mysqli_fetch_array($result)[0];
	$total_pages = ceil($total_rows / $no_of_records_per_page);

	$sql = "SELECT DATE_FORMAT(timestamp, '%y/%m/%d %H:%i.%s') as TimeStamp, stringport, port, event, accrej, reason, country, ipaddress, helo FROM hm_accrej WHERE (timestamp LIKE '%{$search}%' OR stringport LIKE '%{$search}%' OR port LIKE '%{$search}%' OR event LIKE '%{$search}%' OR reason LIKE '%{$search}%' OR ipaddress LIKE '%{$search}%' OR country LIKE '%{$search}%' OR helo LIKE '%{$search}%')".$AR_SQL."".$date_SQL." ORDER BY TimeStamp DESC LIMIT $offset, $no_of_records_per_page";
	$res_data = mysqli_query($con,$sql);
	
	if ($AR=="REJ"){$ARres=" with accept status \"<b>REJECTED</b>\"";} 
	elseif ($AR=="ACC"){$ARres=" with release status \"<b>Accepted</b>\"";} 
	else {$ARres = "";} 

	if ($date==""){$dateres="";} 
	else {$dateres=" on \"<b>".$date."</b>\"";} 

	if ($search==""){$searchres="";}
	else {$searchres=" for search term \"<b>".$search."</b>\"";}

	if ($total_rows == 1){$singular = '';} else {$singular= 's';}
	if ($total_rows == 0){
		if ($search == "" && $date == ""){
			echo "Please enter a search term";
		} else {
			echo "Error: No results ".$searchres."".$ARres."".$dateres;
		}	
	} else {
		if ($search == "" && $date == ""){
			echo "Please enter a search term. <br /><br />";
			echo "All results".$ARres.": ".number_format($total_rows)." hit".$singular." (Page: ".number_format($page)." of ".number_format($total_pages).")<br />";
		} else {
			echo "Results ".$searchres."".$ARres."".$dateres.": ".number_format($total_rows)." Hit".$singular." (Page: ".number_format($page)." of ".number_format($total_pages).")<br />";
		}
		echo "<table class='section'>
			<tr>
				<th>Timestamp</th>
				<th>IP Address</th>
				<th>Protocol</th>
				<th>Port</th>
				<th>Event</th>
				<th>Acceptance</th>
				<th>Reason</th>
				<th>Country</th>
				<th>HELO</th>
			</tr>";
		while($row = mysqli_fetch_array($res_data)){
			echo "<tr>";
			echo "<td>".$row['TimeStamp']."</td>";
			echo "<td><a href=\"index.php?submit=Search&search=".$row['ipaddress']."\">".$row['ipaddress']."</a></td>";
			echo "<td>".$row['stringport']."</td>";
			echo "<td>".$row['port']."</td>";
			echo "<td>".$row['event']."</td>";
			echo "<td>".$row['accrej']."</td>";
			echo "<td>".$row['reason']."</td>";
			echo "<td><a href=\"https://ipinfo.io/".$row['ipaddress']."\"  target=\"_blank\">".$row['country']."</a></td>";
			echo "<td>".$row['helo']."</td>";
			echo "</tr>";
		}
		echo "</table>";
		if ($total_pages == 1){echo "";}
		else {
			echo "<ul>";
			if($page <= 1){echo "<li>First </li>";} else {echo "<li><a href=\"?submit=Search&search=".$search."&AR=".$AR."&date=".$date."&page=1\">First </a><li>";}
			if($page <= 1){echo "<li>Prev </li>";} else {echo "<li><a href=\"?submit=Search&search=".$search."&AR=".$AR."&date=".$date."&page=".($page - 1)."\">Prev </a></li>";}
			if($page >= $total_pages){echo "<li>Next </li>";} else {echo "<li><a href=\"?submit=Search&search=".$search."&AR=".$AR."&date=".$date."&page=".($page + 1)."\">Next </a></li>";}
			if($page >= $total_pages){echo "<li>Last</li>";} else {echo "<li><a href=\"?submit=Search&search=".$search."&AR=".$AR."&date=".$date."&page=".$total_pages."\">Last</a></li>";}
			echo "</ul>";
		}
		if ($total_pages > 0){
			echo "<br />";
		}
	mysqli_close($con);
	}
	echo "<br />";
	echo "</div>";
?>

<br /><br />

<div class="footer">

</div>

</div> <!-- end WRAPPER -->
</body>
</html>
Be sure to fill in the database user/pass credentials at the top.

palinka
Senior user
Senior user
Posts: 1294
Joined: 2017-09-12 17:57

Re: Database Connection Log

Post by palinka » 2019-07-23 01:17

New releases will be here. https://github.com/palinkas-jo-reggelt/ ... ection-Log

Basically the only thing I plan to change is adding a method of deleting entries after an expiration period.

palinka
Senior user
Senior user
Posts: 1294
Joined: 2017-09-12 17:57

Re: Database Connection Log

Post by palinka » 2019-07-23 12:51

V.0.02 on GitHub.

Changes:

* Added auto-populate drop down boxes to search "reason", "port" and "event"
* Removed "stringport" as superfluous
* Added hmsCLExpire.ps1 to auto expire entries via scheduled task
* Added OnClientLogon event to EventHandlers.vbs

I'll try to get the demo working today. Since I have to sanitize the data, I'm only doing that once, so the data will be static (vs the firewall ban demo which is updated with fresh data in realtime).

palinka
Senior user
Senior user
Posts: 1294
Joined: 2017-09-12 17:57

Re: Database Connection Log

Post by palinka » 2019-07-24 16:35

The demo is up and running: http://hmsfirewallbandemo.ddns.net/accrej/

It contains sanitized email logons. I will not be updating the demo db in real time the way I do the firewall ban demo database. There are about 4 days worth of data, which is plenty to get the idea and see how it works.

palinka
Senior user
Senior user
Posts: 1294
Joined: 2017-09-12 17:57

Re: Database Connection Log

Post by palinka » 2019-08-08 23:13

I just committed an update to GitHub. Now you can search HELO by regex. I think this can be very useful. Have a try with a sample query (put in code quotes to prevent truncating)

Code: Select all

http://hmsfirewallbandemo.ddns.net/accrej/index.php?submit=Search&search=REGEX:^\\[(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\\.){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])\\]$

palinka
Senior user
Senior user
Posts: 1294
Joined: 2017-09-12 17:57

Re: Database Connection Log

Post by palinka » 2019-08-12 16:58

Pushed a bug fix for REGEX searches that failed due to "+" in URL.

Files: https://github.com/palinkas-jo-reggelt/ ... ection-Log

Working example including plus sign here: http://hmsfirewallbandemo.ddns.net/accr ... C%5C.it%24

For some reason I get a LOT of hits where the EHLO is a ".it" domain starting with the letter "L". Anyway, that's the search in the link.

Post Reply