Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter


#21

edit: all below this line is no longer needed - now basic gmail support is built-in - just copy the actual radar.php - see settings there

for gmail just replace line 138 (in actual version above):

file_get_contents($user_set["url_mailer"] . "?" . $alert_message);

with this (modify 3 times your email address):

$email = "YOUR_EMAIL@gmail.com";
$headers = "From: YOUR_EMAIL@gmail.com";
mail("YOUR_EMAIL@gmail.com", urldecode($alert_message_subject), urldecode($alert_message_body), $headers);

#22

adsbhub.net does not work anymore. I found a new one - adsbhub.org, looks quite promising. Supports several exchange protocols Beast, SBS, VRS Compressed.


#23

go here - new thread Dump1090 MySQL Database Script with Alert and Filter

### Final Version - 5 Minute Express Install HowTo ###
writes Dump1090 data to database and e-mail alerts on special events. you can set how often the script writes to database and looks for alert condition. you can specify the area (lat/lon/alt) to be observed and filter for special hex and/or flight numbers.

please do not abuse my script to generate and publish the flightaware-mlat-data anywhere - this data is only meant for your personal use. you find additional information here: FlightAware MLAT Network Announcement

2017_09_12 23:54 UTC: one bug in the flight-filter, one that aircrafts weren’t deleted from already alert sent list, one where aircrafts without altitude but position were written to database and/or email-alert and one that allowed too many aircrafts to pass hex/flight filter - now debugged.

2017_09_21 19:40 UTC: added new wildcard-search for hex- and flight-numbers. you can now use % for each unknown character in addition upper/lower case does not matter anymore. in addition you can set now hex/flight filter to operate only within lat/lon/alt limit or within whole range of site. better database error handling added. script now does not quit even if database is shut down and automatically resumes db writing as soon as db is again up. added pushover.net alert type.

but be aware what you are doing with this filters! e.g. if you have a site with good reception you maybe see 300 aircrafts in parallel, then if you put 500 hex- and 500 flight-numbers in your text-files and set the frequency to load fresh aircraft.json every second - then you end up with 300,000 wildcard-match-tests per second. not a good idea on the raspi cpu wise. php7 that comes with raspbian stretch is about 3-10 times faster than php5 if someone wants to do really crazy things with the script.

summary:
i wrote this script first just to dump some of the aircrafts in my neighborhood and their data to a database. then somebody wanted an alert and later another hex/flight filtering. at my site the script and mysql server runs 24/7 on a mac-mini-server - but for those who want run this direct on a raspberry i made a short howto. but be aware that the raspi runs from a sd-card and does not have unlimited disk-space nor is the sd-card too happy with too extensive data-writing. at my site i have around 175 aircrafts simultaneously over the whole day. so if i write every second a dataset for each i get more than 15,000,000 table-rows a day! that means you should use the scripts filter, set an higher intervall and probably use an external usb-harddrive. e.g. if i filter an area where i have only 15 aircrafts around the day and then just write every 60 seconds to database then i get about 20.000 entries a day instead of more than 15 millions!

p.s. the howto below is dedicated to forum member abcd for his huge efforts to help others in this forum :slight_smile: hopefully he is fine with my first attempt …

git install:

git clone --depth=1 http://github.com/TomMuc1/Dump1090-MySQL-Alert-Filter.git

the script to do your own daily aircraft-count directly on the raspberry pi without database you find here: Daily Log/E-Mail Aircraft Report directly on your RaspberryPi

5 Minute Express Install HowTo

given raspbian jessie install with dump1090 with lighttpd


sudo apt-get update
sudo apt-get install sendmail
sudo apt-get install php5-common php5-cgi php5-mysql php5-sqlite php5-curl php5
sudo lighty-enable-mod fastcgi
sudo lighty-enable-mod fastcgi-php
sudo service lighttpd force-reload
sudo apt-get install mysql-server mysql-client

sudo shutdown -r now

mysql -u root -p

CREATE DATABASE adsb;

USE adsb;

CREATE TABLE `aircrafts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `now` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `hex` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `flight` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `altitude` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `lat` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `lon` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `track` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `speed` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `vert_rate` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `seen_pos` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `seen` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `rssi` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `messages` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `category` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `squawk` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `nucp` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `mlat` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  `tisb` varchar(100) COLLATE latin1_german1_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11750 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

SHOW COLUMNS IN aircrafts;

ctrl + z

php radar.php

run the script for some seconds/minutes until it says xxx inserted then stop script with ctrl + z

mysql -u root -p

USE adsb;

SELECT * FROM aircrafts;

et voila :)

p.s. for raspbian stretch the php-install line is:
sudo apt-get install php7.0-common php7.0-cgi php7.0-mysql php7.0-sqlite php7.0-curl php7.0

p.p.s. the new mariadb (aka mysql) that comes with stretch is somewhat stupid with the root password.
these steps help to get back the old behavior:

sudo mysql -u root -p (leave password empty)
update mysql.user set password=password('YOUR_DB_PASSWORD') where user='root';
update mysql.user set plugin='' where user='root';
flush privileges;

radar.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// below a sample create statement for database table
// CREATE TABLE aircrafts (id INT NOT NULL AUTO_INCREMENT, date VARCHAR(100), now VARCHAR(100), hex VARCHAR(100), flight VARCHAR(100), altitude VARCHAR(100), lat VARCHAR(100), lon VARCHAR(100), track VARCHAR(100), speed VARCHAR(100), vert_rate VARCHAR(100), seen_pos VARCHAR(100), seen VARCHAR(100), rssi VARCHAR(100), messages VARCHAR(100), category VARCHAR(100), squawk VARCHAR(100), nucp VARCHAR(100), mlat VARCHAR(100), tisb VARCHAR(100), PRIMARY KEY (id))

// set the rectangle and altitude to store aircraft-data in database - if your lon is negative be aware to use the right values for max and min
$user_set_array['max_lat'] = 50.000000;    $user_set_array['min_lat'] = 46.000000;    $user_set_array['max_alt'] = 10000;
$user_set_array['max_lon'] = 14.000000;    $user_set_array['min_lon'] = 10.000000;

// set the rectangle and altitude to send alert message - if your lon is negative be aware to use the right values for max and min
$user_set_array['alert_max_lat'] = 49.000000;    $user_set_array['alert_min_lat'] = 47.000000;    $user_set_array['alert_max_alt'] = 5000;
$user_set_array['alert_max_lon'] = 13.000000;    $user_set_array['alert_min_lon'] = 11.000000;

// set lookup-interval default is 1 (must be integer between 1 - 900) this is the frequency the script runs and writes to database or looks for alerts
$user_set_array['sleep'] = 1;

// set your google email-address for alert-messages - if you want to use mailer.php instead gmail set method 'gmail' to 'webmail' or 'pushover' and a key according to mailer.php
$user_set_array['alert_method'] = 'gmail'; $user_set_array['email_address'] = 'YOUR_EMAIL@gmail.com'; $user_set_array['secret_email_key'] = 'YOUR_USER_KEY';

// set parameters for database connection
$user_set_array['db_name'] = 'adsb'; $user_set_array['db_host'] = '127.0.0.1'; $user_set_array['db_user'] = 'USERNAME'; $user_set_array['db_pass'] = 'PASSWORD';

// set path to aircraft.json file
$user_set_array['url_json'] = 'http://127.0.0.1/dump1090/data/aircraft.json';

// set path to your mailer.php file
$user_set_array['url_mailer'] = 'http://YOUR_WEBSPACE.COM/mailer.php';

// set the absolute limit of alert-messages (default is 1000) this script is allowed to send over its whole runtime
$user_set_array['mailer_limit'] = 1000;

// set aircraft suspend time (default is 900) - change only if needed - time in seconds an aicraft is suspended from alert-messages after sending an alert-message for this aircraft
$user_set_array['aircraft_suspend_time'] = 900;

// set this to true if you want alerts and/or database writes from those aircrafts matching your hex_code_array.txt or flight_code_array.txt files within limited area or whole site-range, with or without wildcards
$user_set_array['filter_mode_alert'] = false;    $user_set_array['filter_mode_alert_limited'] = false;
$user_set_array['filter_mode_database'] = false;     $user_set_array['filter_mode_database_limited'] = false;
$user_set_array['filter_mode_wildcard'] = false;

// set path to your hex_code_array.txt and flight_code_array.txt files
$user_set_array['hex_file_path'] = '/home/pi/hex_code_array.txt';
$user_set_array['flight_file_path'] = '/home/pi/flight_code_array.txt';

// set your timezone see http://php.net/manual/en/timezones.php
$user_set_array['time_zone'] = 'America/Chicago';




function func_wildcard_search($code, $user_code_array, $wildcard_mode) {
	$match = false;
	$code = strtoupper($code);
	if ($wildcard_mode) {
		foreach ($user_code_array as $pattern) {
			if (preg_match('/^' . trim($pattern) . '$/', $code)) $match = true;
		}
	} else {
		$user_code_array = array_map('trim', $user_code_array);
		if (in_array($code, $user_code_array)) $match = true;
	}
	return $match;
}

$i = 0;
$alert_message = '';
$sent_alert_messages = 0;
$alert_trigger_array = array();
$start_time = time();
date_default_timezone_set($user_set_array['time_zone']);

while (true) {

	$x = 0;
	$sql = '';
	$start_loop_microtime = microtime(true);
	$json_data_array = json_decode(file_get_contents($user_set_array['url_json']),true);
	if ($user_set_array['filter_mode_alert'] || $user_set_array['filter_mode_database']) {
		$hex_code_array = explode(',', str_replace('%', '.', strtoupper(file_get_contents($user_set_array['hex_file_path']))));
		$flight_code_array = explode(',', str_replace('%', '.', strtoupper(file_get_contents($user_set_array['flight_file_path']))));
	}

	// loop through aircraft.json file
	foreach ($json_data_array['aircraft'] as $row) {
		isset($json_data_array['now']) ? $ac_now = $json_data_array['now'] : $ac_now = '';
		isset($row['hex']) ? $ac_hex = $row['hex'] : $ac_hex = '';
		isset($row['flight']) ? $ac_flight = trim($row['flight']) : $ac_flight = '';
		isset($row['altitude']) ? $ac_altitude = $row['altitude'] : $ac_altitude = '';
		isset($row['lat']) ? $ac_lat = $row['lat'] : $ac_lat = '';
		isset($row['lon']) ? $ac_lon = $row['lon'] : $ac_lon = '';
		isset($row['track']) ? $ac_track = $row['track'] : $ac_track = '';
		isset($row['speed']) ? $ac_speed = $row['speed'] : $ac_speed = '';
		isset($row['vert_rate']) ? $ac_vert_rate = $row['vert_rate'] : $ac_vert_rate = '';
		isset($row['seen_pos']) ? $ac_seen_pos = $row['seen_pos'] : $ac_seen_pos = '';
		isset($row['seen']) ? $ac_seen = $row['seen'] : $ac_seen = '';
		isset($row['rssi']) ? $ac_rssi = $row['rssi'] : $ac_rssi = '';
		isset($row['messages']) ? $ac_messages = $row['messages'] : $ac_messages = '';
		isset($row['category']) ? $ac_category = $row['category'] : $ac_category = '';
		isset($row['squawk']) ? $ac_squawk = $row['squawk'] : $ac_squawk = '';
		isset($row['nucp']) ? $ac_nucp = $row['nucp'] : $ac_nucp = '';
		isset($row['mlat']) ? $ac_mlat = implode(' ', $row['mlat']) : $ac_mlat = '';
		isset($row['tisb']) ? $ac_tisb = implode(' ', $row['tisb']) : $ac_tisb = '';

		// generate sql insert statement per aircraft in range of user set altitude/latitude/longitude and optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
		#var_dump($hex_code_array); var_dump($flight_code_array); // show arrays for debug
		if ($user_set_array['filter_mode_database'] && $user_set_array['filter_mode_database_limited']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else if ($user_set_array['filter_mode_database']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) || (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		} else {
			if ($ac_altitude != '' && $ac_altitude < $user_set_array['max_alt'] && $ac_lat < $user_set_array['max_lat'] && $ac_lat > $user_set_array['min_lat'] && $ac_lon < $user_set_array['max_lon'] && $ac_lon > $user_set_array['min_lon']) {
				$sql .= "INSERT INTO aircrafts VALUES (NULL, '" . date("Y-m-d G:i:s l", $ac_now) . "', '$ac_now', '$ac_hex', '$ac_flight', ";
				$sql .= "'$ac_altitude', '$ac_lat', '$ac_lon', '$ac_track', '$ac_speed', '$ac_vert_rate', '$ac_seen_pos', '$ac_seen', ";
				$sql .= "'$ac_rssi', '$ac_messages', '$ac_category', '$ac_squawk', '$ac_nucp', '$ac_mlat', '$ac_tisb');";
				$sql .= PHP_EOL;
				$x++;
			}
		}

		// set and modify alert-trigger-array and build alert-message optionally according only to hex or flight numbers in hex_code_array.txt and flight_code_array.txt
		if ($user_set_array['filter_mode_alert'] && $user_set_array['filter_mode_alert_limited']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) && (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		} else if ($user_set_array['filter_mode_alert']) {
			if (($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) || (func_wildcard_search($ac_hex, $hex_code_array, $user_set_array['filter_mode_wildcard']) || ($ac_flight != '' && func_wildcard_search($ac_flight, $flight_code_array, $user_set_array['filter_mode_wildcard'])))) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		} else {
			if ($ac_altitude != '' && $ac_altitude < $user_set_array['alert_max_alt'] && $ac_lat < $user_set_array['alert_max_lat'] && $ac_lat > $user_set_array['alert_min_lat'] && $ac_lon < $user_set_array['alert_max_lon'] && $ac_lon > $user_set_array['alert_min_lon']) {
				if (!array_key_exists($ac_hex, $alert_trigger_array)) {
					$alert_message_subject = urlencode('### STRAFER-ALERT ### ' . $ac_flight  . ' ' . $ac_hex . ' : ' . $ac_lat . ' ' . $ac_lon . ' : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message_body = urlencode($ac_flight  . ' ' . $ac_hex . ' : <a href="http://www.google.com/maps/place/' . $ac_lat . ',' . $ac_lon . '/@' . $ac_lat . ',' . $ac_lon . ',12z">' . $ac_lat . ' ' . $ac_lon . '</a> : ' . $ac_altitude . 'ft @ ' . date('Y-m-d G:i:s l', $ac_now));
					$alert_message = 'key=' . $user_set_array['secret_email_key'] . '&subject=' . $alert_message_subject . '&body=' . $alert_message_body;
					if ($ac_hex) {
						$alert_trigger_array[$ac_hex] = time();
						#var_dump($alert_trigger_array); // show array for debug
					}
				}
			}
		}

		// delete aircraft after user set seconds from already-message-sent-trigger
		$outdated_entry = time() - $user_set_array['aircraft_suspend_time'];
		foreach ($alert_trigger_array as $key => $value) {
			if ($value < $outdated_entry) {
				unset($alert_trigger_array[$key]);
			}
		}

		// send alert-message, set absolute limit for maximum number of messages and reset alert-message
		if ($alert_message != '' && $sent_alert_messages < $user_set_array['mailer_limit']) {
			if ($user_set_array['alert_method'] == 'gmail') {
				$email = $user_set_array['email_address'];
				$header  = 'MIME-Version: 1.0' . PHP_EOL;
				$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
				$header .= 'From: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'Reply-To: ' . $user_set_array['email_address'] . PHP_EOL;
				$header .= 'X-Mailer: PHP ' . phpversion();
				mail($user_set_array['email_address'], urldecode($alert_message_subject), urldecode($alert_message_body), $header);
			} else if ($user_set_array['alert_method'] == 'pushover') {
				file_get_contents($user_set_array['url_mailer'] . '?mode=pushover&' . $alert_message);
			} else if ($user_set_array['alert_method'] == 'webmail') {
				file_get_contents($user_set_array['url_mailer'] . '?mode=webmail&' . $alert_message);
			}
			$sent_alert_messages++;
			$alert_message = '';
		}

	}

// write selected aircraft data to database
try {
	$db = new PDO('mysql:host=' . $user_set_array['db_host'] . ';dbname=' . $user_set_array['db_name'] . '', $user_set_array['db_user'], $user_set_array['db_pass']); $db_insert = '';
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	if ($sql) { $db->exec($sql); $db_insert = 'inserted'; }
	$db = null;
} catch (PDOException $db_error) {
	$db_insert = 'db-error' . PHP_EOL . $db_error->getMessage();
}

// generate terminal output and set sleep timer to get minimum a full second until next aircraft.json is ready to get fetched
$runtime = (time() - $start_time);
$runtime_formatted = sprintf('%d days %02d:%02d:%02d', $runtime/60/60/24,($runtime/60/60)%24,($runtime/60)%60,$runtime%60);
($runtime > 0) ? $loop_clock = number_format(round(($i / $runtime),12),12) : $loop_clock = number_format(1, 12);
$process_microtime = (round(1000000 * (microtime(true) - $start_loop_microtime)));
print('process-time(us): ' . sprintf('%07d', $process_microtime) . ' - ' . $loop_clock . ' loops/s avg - since ' . $runtime_formatted . ' - run(s) ' . $i . ' -> ' . sprintf('%03d', $x) . ' dataset(s) => ' . $db_insert . PHP_EOL);
sleep($user_set_array['sleep']);
$i++;

}

?>

sample hex_code_array.txt

hex1, hex2, hex3

sample flight_code_array.txt

flight1, flight2, flight3

mailer.php

<?php

#phpinfo();
#var_dump(ini_get_all());
#ini_set('error_reporting', E_ALL);

// set key (letters/numbers only) according to the key you set in radar.php
$user_key = 'YOUR_USER_KEY';

// set your email address if you want to use email alert
$user_email = 'YOUR_EMAIL@EMAIL.COM';

// set your pushover token and key if you want to use pushover alert
$pushover_api_token = 'YOUR_API_TOKEN'; $pushover_user_key = 'YOUR_USER_KEY';

isset($_GET['key']) ? $key = $_GET['key'] : $key = '';
isset($_GET['mode']) ? $mode = $_GET['mode'] : $mode = '';
isset($_GET['subject']) ? $subject = urldecode($_GET['subject']) : $subject = '';
isset($_GET['body']) ? $body = urldecode($_GET['body']) : $body = '';

if ($key == $user_key && $mode == 'webmail') {
	$header  = 'MIME-Version: 1.0' . PHP_EOL;
	$header .= 'Content-type: text/html; charset=iso-8859-1' . PHP_EOL;
	$header .= 'From: ADS-B RADAR' . PHP_EOL;
	$header .= 'Reply-To: ' . $user_email . PHP_EOL;
	$header .= 'X-Mailer: PHP '. phpversion();
	mail($user_email, $subject, $body, $header);
} else if ($key == $user_key && $mode == 'pushover') {
	curl_setopt_array($ch = curl_init(), array(
		CURLOPT_URL => 'https://api.pushover.net/1/messages.json',
		CURLOPT_POSTFIELDS => array('token' => $pushover_api_token, 'user' => $pushover_user_key, 'message' => $body, 'html' => '1'),
		CURLOPT_SAFE_UPLOAD => true,
		CURLOPT_RETURNTRANSFER => true,
	));
	curl_exec($ch);
	curl_close($ch);
}

?>

Permanently Logging a specific Tail(s)?
One hell of a ride - FlightAirMaps the way better VirtualRadarServer
#24

GREAT!
A “How to” with easy to follow and clear step-by-step instructions. Well done Tom.


#25

haha - thanx :slight_smile: and now i have a little idea of how exhausting it is to make all these howtos like you do!


#26

some short examples what you can do with your own harvested data

count the distinct number of aircrafts you got messages from:

select count(distinct hex) from aircrafts;

count how many messages you received per aircrafts:

SELECT hex, COUNT(hex) number FROM aircrafts GROUP BY hex

combine your data with e.g. basestation.sqb - see output below sql sample:

select * from aircrafts inner join basestation on aircrafts.hex = basestation.ModeS

sample output:

id	    date	                    now	            hex	    flight	altitude	lat	        lon	        track	speed	vert_rate	seen_pos	seen	rssi	messages	category	squawk	nucp	mlat	tisb	AircraftID	ModeS	ModeSCountry	Country	Registration	Status	Manufacturer	        ICAOTypeCode	Type	SerialNo	RegisteredOwners	        OperatorFlagCode
252186	2017-09-08 21:01:56 Friday	1504897316.1	a6bd32	EJM533	31000	    47.801234	9.799171	144	    510	    -64	         0.2	    0.1	    -3.8	13107	    A2	        0356	7			            42480	    A6BD32	United States	N	    N533SR	        A	    Gulfstream Aerospace	G450	        G450	4253	    Executive Jet Management	EJM

Daily Log/E-Mail Aircraft Report directly on your RaspberryPi
#28

I assume adding and removing entries from the hex and flight text files will require a restart of the radar.php?

Also, can the script handle wildcards? For example ANZ12%, C87F% etc.


#29

I have set emailed alerts up for my GMail account and set the variables in the script accordingly. Subsequently an aircraft of interest that is in my hex text file has not been emailed to me. Having rescanned the radar.php file I see no variable for my GMail password or other GMail account settings, does there need to be?


#30

Setting my mail for internal and setting the URL for the mailer to be http://192.168.1.2:8082/mailer.php gives me the following error when starting radar.php:

PHP Warning: file_get_contents(http://192.168.1.2:8082/mailer.php?key=xxxxxx&subject=%23%23%23+STRAFER-ALERT+%23%23%23+JST231+7c6b13+%3A+-42.212592+172.864188+%3A+7818.12m+%40+2017-09-11+12%3A33%3A56+Monday&body=JST231+7c6b13+%3A+<a+href%3D"http%3A%2F%2Fwww.google.com%2Fmaps%2Fplace%2F-42.212592%2C172.864188%2F%40-42.212592%2C172.864188%2C12z">-42.212592+172.864188<%2Fa>+%3A+7818.12m+%40+2017-09-11+12%3A33%3A56+Monday): failed to open stream: HTTP request failed! HTTP/1.0 500 Internal Server Error
in /var/www/html/aircraft.php on line 157

Key has been hidden for this post and port 8082 is my web forward port for the machine hosting radar.php and mailer.php


#31

OK, I found them all in my Junk folder! GMail works just fine!


#32

OK I’m sure I had this running OK this morning but now it seems after a total refresh of both radar.php and mailer.php that I am not getting any emailed alerts.

If I set Gmail option to true and enter my GMail address radar.php runs without error but also without any alerts, but database writes are OK.

Switching to the mailer it seems that I get a:

HTTP request failed! HTTP/1.0 500 Internal Server Error message when radar.php tries to locate the mailer.php

Any help appreciated.

Edit: This may be related to the fact I am trying to run the radar.php and mailer.php (when enabled) on a Ubuntu platform. I’ve just moved the scripts back to my RPi where the radar.php used to be until I started ‘messing’ with alerts! Having installed sendmail on the RPi I think the GMail option is now working… sort of.


#33

@invergordon

if you use the script with google-mail configuration it should run without any problems on any raspian jessie install out of the box and mailer.php is not needed.

don’t know which version you are using? and if you messed too much around - just copy radar.php again - do the few needed settings again …

final version: Dump1090 MySQL Database Script - E-Mail-Alert Lat/Lon/Alt/Hex/Flight-Filter


#34

@invergordon

did a short test - downloaded the radar.php from fa-website - setup googlemail to true, my google-address, path to aircraft.json, lat/lon/alt for db, lat/lon/alt for alert, timezone and password/user/db/path-to-db. then copied to a fresh jessy setup.

runs instantly without any issue … sends alerts and writes to database.


#35

in my fresh raspian-jessie installs there is sendmail installed - but when i download jessie from raspbians website today and did an install ther was no sendmail at all …

i add to install howto the sendmail install - no settings needed - just:

sudo apt-get update
apt-get install sendmail

but again if you simply use and upload mailer.php to your webspace all this isn’t needed


#36

restart isn’t needed - the script every loop checks both .txt files

you have to input the full flight or hex


#37

Thanks To, I’ve done this a few times now in case I’ve missed anything but that’s the process I’ve been through.

While the database writing goes well and I have no issues here the alerts are not really working.

Can you explain what the ‘upload’ mailer to my ‘webspace’ means and does? I’m a little confused with what the mailer/php should be doing.

Thanks.


#38

what does ‘not really working’ mean?
webspace - where you have to put the mailer.php: https://www.ntchosting.com/encyclopedia/internet/web-space/


#39

If I use GMail, I get no email alerts now. Yesterday morning on my Ubuntu laptop through some inexplicable reason I got some alerts via GMail with the $user_set_array[‘filter_mode_alert’] = false;, initially all the aircraft that were airborne around my receiver. I decided that was not a good option so set some hex codes in the text file and set $user_set_array[‘filter_mode_alert’] = false;. As aircraft that flew by that were in the hex text file I got no alerts from them.

Last night I downloaded and reset everything, installing sendmail on the RPi and got two arbitrary alerts with $user_set_array[‘filter_mode_alert’] = true; but then nothing else.

Using the mailer, I just get errors about trying to find the mailer.php, hence my earlier question about this.


#40

please remove the code mess above :slight_smile:

to be honest i have no idea what you are doing and where all this back and forth with the ubuntu notebook should be good for.

just use your pi, install the software described in howto. then make your settings in radar.php. if you have no webspace at an internet-service-provider use the google-mail option. if something then isn’t working correctly give a reasonable description.

$user_set_array[‘filter_mode_alert’] = false;. As aircraft that flew by that were in the hex text file I got no alerts from them

if you switch off the hex/flight-alert option then why do you wonder that you did not get mails?

p.s. the settings in your radar.php are: write every 10 second each aircraft my site detects to database and sent for every one an email via google until 1000 emails are sent out …

and in addition read hex/flight files in home-directory of my pi - but you say you run it on an ubuntu notebook - what should this setting be good for?


#41

what i found was a wrong bracketing in the online version of the script that allowed too many aircrafts to pass filter. but this could not be the reason for not sending out alert-emails at all.

two additional ideas what could cause problems you have:

  1. if you start the script manual in terminal then stop and later restart - always close terminal-window completely before a new run to avoid multiple instances running in parallel.

  2. even if you did not receive emails because of wrong configuration the sent-message counter in the script of course counts up and then could stop at the set maximum number.

and again - you cannot setup relative path to hex/flight files of your pi while running the script on your notebook.

i’d start from scratch - copy the actual version above, install the software needed in howto, run script from home/pi on raspberry (where both .txt filter files are placed - and then step by step altering the position/hex/flight filter settings - one step after the other. at the moment anything runs fine you could start testing what happens on the ubuntu notebook …