~~ODT~~
====== Threat Intelligence ======
FIXME 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
<code>
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() |
+---------------+--------------+------+-----+---------------------+-------------------------------+
</code>
  * 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

<code>

<?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);
}

?>


</code>

===== Logging =====
  * Zentraler Server Beispiel syslog server auf 192.168.1.1 linux **syslog.conf**
<code>
..
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);};
..
</code>

  * openwrt **/etc/config/system**

<code>
..
        option log_ip '192.168.1.1'
        option log_port '514'
        option log_proto 'udp'
..
</code>

===== 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:
<code>
[{"src":"59.63.148.204","dpt":445,"proto":"TCP","originalDate":"2023-06-27 13:42:24","hits":2,"dst_hits":1}]
</code>