I wanted a script which would read all the live dump1090 aircraft data and log it to a mysql database.
This base script does it, it will dump all the data to Mysql every minute.
It will start the dump1090 server and kick off a process which reads/sorts and inserts.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
#!/bin/bash dbserver="localhost" dbuser="flighttrax" dbpw="flighttrax" dbtable="flighttrax" clientid="1000000" core="/root/file.txt" dump="/tmp/dump.txt" selectdate=`date -d "1 min ago" '+%Y/%m/%d,%H:%M'` listener="localhost" listenerport="30003" ncpid=`ps waux | grep "nc $listener $listenerport"| grep -v grep | awk '{print $2}'` dumppid=`ps waux | grep "dump1090"| grep -v grep | awk '{print $2}'` log="/var/log/flighttrax.log" echo "`date`" >> $log echo "`date` - Starting script" >> $log echo "`date` - Netcat pid is $ncpid" >> $log echo "`date` - Dump1090 pid is $dumppid" >> $log if [ -z $dumppid ] then echo "`date` - Dump1090 not started yet" >> $log /root/dump1090/dump1090 --net --quiet & echo "`date` - Started Dump1090" >> $log exit fi if [ -z $ncpid ] then echo "`date` - Netcat not started yet" >> $log nc $listener $listenerport >> $core echo "`date` - Started NC" >> $log exit fi echo "`date` - Copying last minute" >> $log cat $core | grep $selectdate > $dump echo "`date` - Calculating Entries" >> $log entries=`wc -l $dump` echo "`date` - Total of $entries to import" >> $log echo "`date` - Starting FOR statement" >> $log for i in `cat $dump | awk -F ',' '{print $5}' | sort | uniq | sed '/^$/d'` do reg=$i flightnum=`cat $dump | grep $i | awk -F ',' '{print $11}'| sort| uniq | sed '/^$/d'|awk '{print $1}'|sed 's/\r//'` for i in `cat $dump | grep $reg | awk -F ',' '{print $7","$8","$15","$16","$12}'| sort| uniq | sed '/,,/d'` do date=`echo $i| awk -F ',' '{print $1}' | sed 's#/#-#g'` time=`echo $i| awk -F ',' '{print $2}'` lat=`echo $i| awk -F ',' '{print $3}'` long=`echo $i| awk -F ',' '{print $4}'` alt=`echo $i| awk -F ',' '{print $5}'` echo "$reg,$flightnum,$date,$time,$lat,$long,$alt" mysql -uflighttrax -pflighttrax flighttrax -e "INSERT INTO flighttrax VALUES ('','$clientid','$reg','$flightnum','$date $time','$lat','$long','$alt');" done done echo "`date` - For completed" >> $log echo "`date` - Deleting temp files" >> $log rm $dump echo "`date` - Done." >> $log |
