Threat Intelligence

work in progress / Analyse der gesammelten Firewall Drops / Datenbank Design usw..

Zielsetzung

Alle Drops mit bestimmten Eigenschaften die auf der Firewall extern einschlagen protokollieren um daraus Hinweise auf gehackte Systeme zu gewinnen
Interessant wird dann die andere Richtung auf anderen Systemen - welche Endgeräte versuchen auf IP Adressen von Intern nach Extern zuzugreifen auf IPs die aus den historischen Daten gewonnen wurden

Datenbank

Ich möchte in der Lage sein SQL Queries auf die Daten abzusetzen

MariaDB [inteldb]> desc raw;
+---------------+--------------+------+-----+---------------------+-------------------------------+
| Field         | Type         | Null | Key | Default             | Extra                         |
+---------------+--------------+------+-----+---------------------+-------------------------------+
| hash          | char(64)     | NO   | PRI | NULL                |                               |
| original      | varchar(450) | NO   |     | NULL                |                               |
| src           | varchar(100) | NO   | MUL | NULL                |                               |
| dst           | char(40)     | NO   | MUL | NULL                |                               |
| dpt           | int(11)      | NO   | MUL | NULL                |                               |
| spt           | int(11)      | NO   | MUL | NULL                |                               |
| proto         | varchar(15)  | NO   | MUL | NULL                |                               |
| country       | char(2)      | NO   |     | NULL                |                               |
| originalDate  | datetime     | NO   | MUL | NULL                |                               |
| insertionDate | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+---------------+--------------+------+-----+---------------------+-------------------------------+

Storage Engine default-storage-engine = Aria - als Nachfolger von myisam - ich benötige Crash resistenten Table

Schnittstelle f. Datenbank / Input der Daten

syslog-ng loggt direkt auf eine pipe / für das Skript macht es keinen Unterschied ob ein Logfile gelesen wird oder die Pipe geöffnet

<?php
// Shouldn't be used anymore
//declare(ticks = 1);
pcntl_async_signals(true);
pcntl_signal(SIGINT,'exit_handler');


$db_servername="";
$db_username="";
$db_name="";
$db_password="";


require_once("geoip2.phar");
use GeoIp2\Database\Reader;

if(!isset($argc) || $argc !== 2 )
{
	echo "\nParamters count wrong \n";
	exit(2);
}

$input_file=$argv[1];

if(!is_readable($input_file))
{
	echo "\nCannot read: ".$input_file."\n";
	exit(2);
}

$fp = fopen($input_file,"r");

global $entriesOk;
global $entriesFail;


$entriesOk=0;
$entriesFail=0;


$db_connection = new mysqli($db_servername,$db_username,$db_password,$db_name);

if($db_connection->connect_error)
{
	echo "\nCannot connect to database: $db_servername : $db_username : $db_name \n";
		exit(2);
}

$insert_statement=$db_connection->prepare("INSERT into raw (hash,original,src,dst,dpt,spt,proto,country,originalDate) values (?,?,?,?,?,?,?,?,? )");

while( ($line=fgets($fp,1024)) !== false)
{
	//echo $line."\n";
        $matches=array();

	$matches_count=preg_match_all('/[A-Z]+=[A-Z,a-z,0-9,\.,:]+/i',$line,$matches);

	if($matches_count > 1 )
	{

		$inputMaster=array();

		$matches=$matches[0];

		$wrongCommit=false;

		for($i=0; $i<count($matches) && $wrongCommit==false ; $i++)
		{
			$cut_array=explode("=",$matches[$i]);
			if(count($cut_array)== 2)
			{
				if(!isset($inputMaster[$cut_array[0]]))
				{
					$inputMaster[$cut_array[0]]=$cut_array[1];
					
					if(isset($inputMaster["SRC"]) && filter_var($inputMaster["SRC"], FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE)===false)
					{
						$wrongCommit=true;
					}
					
					
				}
				else
				{
					#2024-02-16 cc: no multiple SRC definitions !
					if(isset($inputMaster["SRC"]) && $cut_array[0]=="SRC")
					{
						$wrongCommit=true;
					}
				}
			}

		}


		if($wrongCommit===false)
		{

			//Jan 26 01:46:54
			if(preg_match('/^[A-Z,a-z]{3}[ ]{1}[0-9]{1,2}[ ]{1}([0-9]{1,2}:){2}[0-9]{1,2}/i',$line,$matches_time)===0)
			{
				$inputMaster["timestamp"]=time();
				$inputMaster["datetime"]= date("Y-m-d H:i:s",$inputMaster["timestamp"]);
				$inputMaster["original_time"]=false;
			}
			else
			{
				$inputMaster["timestamp"]=strtotime($matches_time[0]);
				$inputMaster["datetime"]= date("Y-m-d H:i:s",$inputMaster["timestamp"]);
				$inputMaster["original_time"]=$matches_time[0];

			}


			$inputMaster["original_line"]=$line;
			$inputMaster["hash"]=hash('sha256',$line);
			if(isset($inputMaster["SRC"]))
			{
                       		$inputMaster["country"]=getCountry($inputMaster["SRC"]);

				//$reader = new Reader('/usr/local/etc/dbip-country.mmdb');
				//$inputMaster["country"]=($reader->country($inputMaster["SRC"]))->country->isoCode;
		
			}
			else
			{
				$inputMaster["SRC"]="00";
				$inputMaster["country"]="00";
			}


			if(isset($inputMaster["DST"]))
			{
				$inputMaster["DST"]=hash('sha1',$inputMaster["DST"]);

			}
			else
			{
				$inputMaster["DST"]=hash('sha1',"00");
			}


			if(!isset($inputMaster["DPT"]) || strlen($inputMaster["DPT"])===0 )
			{
				$inputMaster["DPT"]=0;
			}

			if(!isset($inputMaster["SPT"]) || strlen($inputMaster["SPT"])===0 )
			{
				$inputMaster["SPT"]=0;
			}


			$entriesOk++;

			$insert_statement->bind_param("ssssiisss",$inputMaster["hash"],$inputMaster["original_line"],$inputMaster["SRC"],$inputMaster["DST"],$inputMaster["DPT"],$inputMaster["SPT"],$inputMaster["PROTO"],$inputMaster["country"],$inputMaster["datetime"]);

		//print_r($inputMaster);
		
			if($insert_statement->execute())
			{
				$entriesOk++;
			}
			else
			{
				$entriesFail++;
				echo "MYSQL Failure: ".$insert_statement->error."\n";
				print_r($inputMaster);
			}
		}
		else
		{
			echo "\nInvalid Commit: $line \n";

			//print_r($inputMaster);
			//print_r($matches);

			$entriesFail++;
		}

		if( !( $entriesOk % 1000) || !( $entriesFail %1000) )
		{
			echo "\nInsertion OK: $entriesOk \nInsertion FAIL: $entriesFail ";
			echo "\nStats Entries handeled total: ".($entriesOk+$entriesFail);
		}


	}
	else
	{
		$entriesFail++;

	}


}


echo "\n Program quit !";
echo "\nInsertion OK: $entriesOk \nInsertion FAIL: $entriesFail ";
echo "\nStats Entries handeled total: ".($entriesOk+$entriesFail);


exit(0);


function getCountry($ip)
{
try
{
   $reader = new Reader('/usr/local/etc/dbip-country.mmdb');
   return $reader->country($ip)->country->isoCode;

}
catch(Exception $e)
{
	return "00";

}



}


function exit_handler($signal)
{
	echo "It's over'";
	exit(2);
}

?>

Logging

Zentraler Server Beispiel syslog server auf 192.168.1.1 linux syslog.conf

..
destination d_intel { network("192.168.1.1" port(514) transport(udp)); };
filter f_firewall_intel { message("fw") and message("EXT-FW") and message("DROP"); };
log { source(s_src); filter(f_firewall_intel); destination(d_intel);};
..

openwrt /etc/config/system

..
        option log_ip '192.168.1.1'
        option log_port '514'
        option log_proto 'udp'
..

API

API unter: https://threatintel.pannoniait.at/ip.php
Parameter: requestIP → IPv4 oder IPv6 IP Adresse
Parameter: apiKey → API Key von Pannonia IT Results ohne Delay
Paramter: format → csv - Results im CSV Format / optional per Default im json Format
Beispiel: https://threatintel.pannoniait.at/ip.php?requestIP=59.63.148.204
Ergebnisse als JSON Results:

[{"src":"59.63.148.204","dpt":445,"proto":"TCP","originalDate":"2023-06-27 13:42:24","hits":2,"dst_hits":1}]