Replace Text Recursively using grep and sed on MacOS (osX).

As with many MacOS’isms, you have to change up your old school LINUX to work with the slightly different command syntax of the MacOS tools. Specifically in the this case xargs and sed.

xargs can be an amazingly powerful ally when automating commands line functionality, wrapping the reference command with auto-substitutions using the ‘@’ meta-character.

I’ll get to the meat of this. While trying to automated a grep -> sed pipeline, I encountered this error:

egrep -rl 'version 0.0.1' src/* | xargs -i@ sed -i 's/version 0.0.1/version 0.0.1c/g' @
xargs: illegal option -- i

It turns out that MacOS xargs like -I instead of -i.. (a quick trip to man sorted that out).

     -I replstr
             Execute utility for each input line, replacing one or more occurrences of replstr in up to replacements (or 5 if no -R flag is specified) arguments to utility
             with the entire line of input.  The resulting arguments, after replacement is done, will not be allowed to grow beyond 255 bytes; this is implemented by con-
             catenating as much of the argument containing replstr as possible, to the constructed arguments to utility, up to 255 bytes.  The 255 byte limit does not apply
             to arguments to utility which do not contain replstr, and furthermore, no replacement will be done on utility itself.  Implies -x.

Next I ran into an errors with sed.. when using the ‘edit in place’ flag -i. (yes, that makes for a confusing debug when you have the “offending” switch in two places).

egrep -rl 'version 0.0.1' src/* | xargs -I@ sed -i 's/version 0.0.1/version 0.0.1c/g' @
sed: 1: "src/com/ingeniigroup/st ...": bad flag in substitute command: 'a'
sed: 1: "src/com/ingeniigroup/st ...": bad flag in substitute command: 'a'
sed: 1: "src/com/ingeniigroup/st ...": bad flag in substitute command: 'a'
[...]

With a little testing of the individual command, and another trip to man, it took a little noodly to deduce that the error message was being generated because it was trying the sed commmand as the output file suffix and the filename as the sed command! Adding an empty string designator after sed’s -i solved this.

     -i extension
             Edit files in-place, saving backups with the specified extension.  If a zero-length extension is given, no backup will be saved.  It is not recommended to give
             a zero-length extension when in-place editing files, as you risk corruption or partial content in situations where disk space is exhausted, etc.

Final Command Query
The objective was to simply hammer through the source code and update the version tag from 0.0.1 to 0.0.1c. Running egrep in recursive filename list mode ( egrep -rl ) for the string I wanted ( ‘version 0.0.1’ ) gave me a file list, which was then piped into xargs, which expanded that list into the sed command ( sed -i ” ‘s/version 0.0.1/version 0.0.1c/g’ ). And viola.. 18 files changed with just a little big of effort:

egrep -rl 'version 0.0.1' src/* | xargs -I@ sed -i '' 's/version 0.0.1/version 0.0.1c/g' @

Since this took me more than 5 minutes to figure out, I decided I’d take 5 more and hopefully help someone else down the line.

AvMet Beta Release – STRATUX Database Reporter Project (open source!)

AvMet Beta Available on GitHub


For that last 6 months I have been capturing Aviation Mode-S and ADS-b data transmissions using my own customizd STRATUX Aviation Traffic Receiver running Raspberry Pi 3b. With my modifications, the device automatically shuts down daily for a few seconds to release the current SQLite3 database, and the start up pointing at a fresh database. Once released, the database is compressed to save space and for later download (I use rsync to automate that as well).

These database files can be very big, and contain hundreds of thousands of contact events each day. When these databases are generated every day, it doesn’t take long to get a backlog of them to examine for interesting things, such as NASA aircraft and some mysterious high-flyers using spoofed identities.

Being a professional programmer and data guru, this seemed like a perfect project to open-source and use a marketing tool for my consulting. After a week of design, development and preliminary tests.. it’s ready for comment.

What it does:


The tool is purpose built for my needs, so it might not (yet) be easily used on your own Stratux database files, but I feel I’ve made a reasonable effort in the interests of ease.

Each database file is opened and checked for a number of conditions and data:

  • Verify database contains expected STRATUX data tables.
  • Select first timestamp record and report it out. Since the name of the file might not be self descriptive, this is really helpful to know the dataset’s start time:
    Using Database File: './sqlite-stratux-temp'
    	Start: 2017-10-10 08:11:09.967 +0000 UTC
    	-----------------------------------------
    
  • Check dataset for obvious duplicates and remove them. It’s not uncommon for the message log to repeat a fixed position up to 5 times. Although this does not affect the current metrics, this bloats the dataset. There is an optional flag to disable this feature
  • Once duplicates are removed, each airframes dataset is checked for gross errors, such as changes in altitude or speed that would be possible only with alien spacecraft; records are also removed. There is an optional flag to disable this feature
      10486213 -- Bad Distance  	   24924 --> 51857   
      10617769 -- Bad Distance  	   82039 --> 206496  
      10707621 -- Bad Speed              318 --> 8303
    
  • Following data repair a few metrics are pulled from the dataset, looking for interesting boundary events such as Fastest and Furthest contacts. Each record reports the Callsign or Tail Registration number (when available), and the Mode-S ICAO24 code as well as altitude, speed and distance for the specific event. Example:
    	  FASTEST:   XAOLE [0D0AAE]   43000 ft.  @   529 kts.      23 mi.
    	  SLOWEST:   NDU45 [A6EBAF]    1150 ft.  @    61 kts.    2.26 mi.
    	  HIGHEST:   XAOLE [0D0AAE]   47025 ft.  @   505 kts.      92 mi.
    	   LOWEST:  N41218 [A4DE2A]     550 ft.  @    77 kts.    1.71 mi.
    	  CLOSEST:  N6464R [A87E79]    2525 ft.  @    84 kts.    0.00 mi.
    	 FURTHEST:  N229NN [A203A9]   36000 ft.  @   472 kts.     128 mi.
    

    Here is an example of an interesting contact captured last year along the coast of California; a high-altitude signal from a NASA 747 test aircraft almost 100 miles off the coast.

    	  HIGHEST: NASA747 [AA0DB8]   43025 ft.  @   473 kts.      32 mi.
    	 FURTHEST: NASA747 [AA0DB8]   43000 ft.  @   466 kts.      92 mi.
    
  • During this phase of the process, special Squawk Code events are trapped that might indicate special civil, science or military operations. Example:
    	**ALERT:  N7253N [A9B8ED] 4403     2500 ft.  @   118 kts.  SR-71, YF-12, U-2 and B-57, pressure suit flights
    	**ALERT:  N7272N [A9C038] 4402     2075 ft.  @   116 kts.  SR-71, YF-12, U-2 and B-57, pressure suit flights
    	**ALERT:  N7274N [A9C07E] 4404     1250 ft.  @   100 kts.  SR-71, YF-12, U-2 and B-57, pressure suit flights
    	**ALERT:  N907CH [AC8967] 4442     1800 ft.  @   140 kts.  SR-71, YF-12, U-2 and B-57, pressure suit flights
    

    In the above case, N7253N, N7272N and N7274N are Department of Homeland Security Border Patrol helicopter contacts.

Here is a recent report example:

        Start: 2017-10-10 08:11:09.967 +0000 UTC
        -----------------------------------------
          FASTEST: AFR6721 [3951C1]   32975 ft.  @   531 kts.      14 mi.
          SLOWEST:  N42894 [A51D07]     950 ft.  @    51 kts.    3.21 mi.
          HIGHEST:    JCB1 [43E9D5]   47025 ft.  @   469 kts.      34 mi.
           LOWEST:  N724DP [A9B2C1]     400 ft.  @    72 kts.    1.65 mi.
          CLOSEST:  N605CH [A7DA0C]   16200 ft.  @   400 kts.    0.02 mi.
         FURTHEST:  N9023N [AC7968]   33050 ft.  @   433 kts.     143 mi.

Where can you get it?

You can fork or pull the current source code from GitHub here: IngeniiCode AvMet

Simple AWS Lambda Java Project – NetBeans and Gradle


I have been working on distributed harvesting projects for the last 5 years. Generally the projects used a raft of AWS EC2 instances and Gearman to manage the workflow. Having recently heard of some similar solutions using AWS Lambda functions, I wanted to explore this as a possible new architecture for these projects. So.. I dug in.

Article Contents:

The learning curve for me has been a little steep. There is a limited number of languages supported (AWS doc linked here), but the only two that came close to meeting my needs were NodeJS and Java.

Most of my project code is written in CasperJS, a NodeJS extension, so I thought that perhaps that was the path of least resistance.. but it was not clear to me how to ‘install’ PhantomJS and CapserJS components into this Lambda Function, so I switched to working with Java.

Designing the Test

This is a VERY simple test blob, but it took me about a day to get to this point, since it was not totally clear to me how the AWS API Gateway might work and how the data is packaged/posted to the <Lambda Function. After a lot of poking around, uploading my first hunk of (totally broken) code; I was presented the a test CLI. The test CLI has only a few options or packaging up data payloads (at least for test), singular scalar string and a JSON string (which turns into an Object.. the source of the frustration I faced figuring this out).

Boiling it down, this is what the test harness input looks like, when you use the “Hello World” test case, after changing the properties to match my desired inputs:

This will work fine for my needs, I prefer JSON payloads over a fixed (brittle) set of parameters.. this lets me muck around with the internals and add more capacity without having to work about external inputs; I’m just that way.

Having decided that JSON was the payload to accept.. next I needed to write a chunk of code to accept it.

Writing the Test Code

Knowing that I would be using a Json string as my input, I’d first coded my function like this:

public String searchAuto(String json){
return String.format(“%s”,json);
}

What I discovered quickly is that the string of JSON in the test payload (which looks like this), is deserialized as a Json Object, and not a string. This required me to step up my simple test to actually deal with the data as JSON. To do this, I selected simple.JsonObject. This is where the “fun” began. Hopefully this helps you avoid spending the time I did to figure out how to get the code cleanly compile locally.. AND to run when uploaded to AWS.

NOTE: I’m not going to say this is the right way to do this.. it’s just the way it worked for me. It’s kludgy and I don’t really like having a two step process but it’s working to get my version of HELLO WORLD working.

Long story short, I needed to get copies of these three .jar files added to my project’s build path, to get things going. Where you place them in your project might vary, but in a basic NetBeans project, these are in the lib/ directory off the project root:

ls -l lib/*.jar

7,267 lib/aws-lambda-java-core-1.1.0.jar
75,355 lib/aws-lambda-java-events-2.0.jar
57,607 lib/json-simple-2.1.2.jar

Locating the jars was not the simplest task either, Amazon’s horrible documentation sent me fishing around on various Github repos, but in the end.. I found them with a good ol search. I’ve linked the libs in the above block to where I sourced mine.

AWS Lambda requires use of specific handler hooks, when initiating the function. I’d like to wax poetic about what it all means but at this time, I just need a proof of concept, so using a code example.. this is the code I compiled:

package lambdaTest;

import com.amazonaws.services.lambda.runtime.Context;
import com.amazonaws.services.lambda.runtime.LambdaLogger;
import org.json.simple.JsonObject;

/**
*
* @author david
*/

public class LambdaTest {

public String myHandler(int myCount, Context context) {
LambdaLogger logger = context.getLogger();
logger.log(“received : ” + myCount);
return String.format(“Received value of %d”,myCount);
//String.valueOf(myCount);
}

/* search options */
public String searchAuto(JsonObject json){

return String.format(“Search Region [%s] for ‘%s'”,json.getString(“region”),json.getString(“query”));
}
}

Beyond the Build – Packaging your Java

Again, the think the AWS documentation is pretty crappy, and this extends to every AWS service I’ve had to use. Once you figure out what I really means, they work very well.. but decoding Seattle AWS-speak (for me) is often not easy. You might be smarter than me and able to see the path right off the bat.. well.. good for you. For me and the rest of the inelastic brain crowd.. I’ll try to give you the Cliff Notes version…

When I simply wanted to test my JAR, and I didn’t need the JSON libary, it was a simple matter of uploading the Jar file in the Lambda dialog; it looks like this:

However, uploading more than 1 jar, things start to get a little wonky. The instructions say this:

Example Gradle Config
The page also provides a rather extensive rundown on using Gradle to run your build and make the package. Since creating my own .ZIP with the files in the ‘root’ as designated didn’t work, I went Gradle. If you decide to go Gradle, here is the build file I ended up using. When I made the mistake of including the Mavin paths for AWS into the Gradle build, I ended up with ALL if the AWS .jar files being downloaded and zipped into the library, creating a 7.8 MB upload!! For a Hello World program.. INSANE! Anyhow.. this is my gradle config that changes the default Gradle configs (build.gradle text file) to work with NetBeans paths. You might need to tweak this further to your own needs:

apply plugin: ‘java’

//repositories {
// mavenCentral()
//}

sourceSets {
main {
java {
srcDir ‘src’
}
}
test {
java {
srcDir ‘test’
}
}
}

dependencies {
compile (
fileTree(dir: ‘lib’, include: ‘*.jar’)
)
}

task buildZip(type: Zip) {
from compileJava
from processResources
into(‘lib’) {
from configurations.runtime
}
}

build.dependsOn buildZip

Using Gradle to run the build looks like this, run the command gradle build and you are on your way… as long as your code has a clean compile:

gradle build

BUILD SUCCESSFUL in 1s
3 actionable tasks: 3 executed

This allowed me to build a ZIP file that uploaded and worked. What I found out, was that the structure of the file is NOT really the way it was described in the docs. The contents of the zip file look like this for my project; note that when uploading this way, you are *not* packaging the .jar file for your class, but the compiled class file. Also not that it is *not* in the root of the path. With this knowledge, I can probably dispense with dual-building with Gradle and just package up the contents in this way. Hack the process!

Lambda/lambdaTest:
1612 Sep 29 15:18 LambdaTest.class

Lambda/lib:
7267 Sep 26 10:40 aws-lambda-java-core-1.1.0.jar
75355 Sep 26 10:13 aws-lambda-java-events-2.0.jar
57607 Sep 26 18:30 json-simple-2.1.2.jar

Now, you should be able to upload your zip file. Not that a file of 10MB or more, they’d like you to spend extra money storing it on S3… (niiiiice).

Testing your Function

Once uploaded, you can configure your test…

… and run it!

Conclusion

After a day of putting around and getting frustrated with the AWS docs that always leave (for me) gaping knowlege gaps.. I have a proof of concept test coded, compiled, uploaded and run. Now.. what needs to be sorted out is how to setup the AWS API Gateway to trigger this function… and look at how much each of those iterations will cost. With a planned execution batch that will range from 40-60 events per query.. this could add up fast.

So….. I’m not yet convinced this will function economically for my latest project; but I’m going to be testing out the concept pretty thoroughly over the coming week.


Pima Air & Space Visit – June 2017

Our time in California is quickly coming to a close. As part of this process I took on a 3000 mile road trip from Santa Cruz CA to Canyon Lake TX.

Along the way I decided to spend a day in Tuscon AZ and visit the Pima Air & Space Museum. And it was WELL worth the stop! Along with that visit I took a trip to the US Military “Aircraft Boneyard” (actually known as Davis-Monthan AFB).

The Air Museum itself is absolutely amazing! With 300+ aircraft and endless artifacts, it is really a 2 day visit if you want to even read all the ID plates, much less spend the time to learn about the amazing history some of these very unique aircraft, including the Boeing 787 Dreamliner Prototype 002 which is currently on display in this collection.

I could attempt to wax poetic about this visit, but instead I’ll simply post about 90 snapshots I took while trying to take do it all in one day.

STRATUX – Hacking together a WiFi connected Ground Station

Following my initial vanilla setup of STRATUX, I decided to make some networking and file system modifications to turn it into a fix (ground) monitoring station.

The initial effort was quite successful; that is until I ran out of disk space and during my cleanup effort accidentally wrecked the source tree, rendering it mostly inoperable. So, I’m taking that opportunity to re-document the setup process I used to accomplish the following:

Imaging Stratux

The first step was performing a default STRATUX install to a micro SD card. I selected a 16GB card for this project, but Stratux will run on anything down to 4 GB, based on what I observed with the default file system (default size is under 2GB).

I won’t repeat the installation instruction here, since they might have changed since this article was written. The instructions I used, and link to the latest Pi Image are located here: [ STRATUX.me ]

Preliminary Work

Once the card was imaged, I plugged in an HDMI cable, keyboard, mouse and Ethernet cable to start work.

Stratux Pi preliminary setup

Once powered up and the boot sequence completes, your are challenged with a simple login prompt. The initial login is: pi and the password is raspberry.

Once you login, you’ll see this welcome screen (sorry for the crappy photo.. if I can figure out how to PiP the HDMI output to my Mac to snap a good screen shot, I’ll re-do this!

Stratux first login

You will WANT to change that default password first!

Changing Default Password

Switch user to root and change the pi user’s password. You do not NEED to be root to change your own password, but we’ll need to be root from here on out.. and why not just do it that way?


pi@raspberrypi: sudo su -

root@raspberrypi: passwd pi
Enter new UNIX password: enter your password here
Retype new UNIX password: re-enter your password here

Enable eth0 for wired LAN

Next step involves enabling eth0 and turning on it’s DHCP setting. Once this is complete, it should be possible to SSH into the pi and continue the modifications from your ‘master’ computer.


root@raspberrypi:~# cd /etc/network

root@raspberrypi:~# vi interfaces

At the top of the file you will see the line auto lo, below that add the following ‘auto eth0’. Then below the line iface lo inet loopback, add the line ‘iface eth0 inet dhcp’


auto lo
auto eth0

iface lo inet loopback
iface eth0 inet dhcp
...

At this point I do not plan to try enable the WiFi, first want to verify that eth0 will come up with a LAN address.

Close the edited file, and then restart networking, then run ifconfig to verify that eth0 is up with and assigned IP address (my local DHCP range is 10.10.1.10 – 1.50).


root@raspberrypi:~# service networking restart

root@raspberrypi:~# ifconfig

eth0      Link encap:Ethernet  HWaddr b8:c3:eb:2e:72:a5  
          inet addr:10.10.1.11  
          Bcast:10.10.1.255  
          Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:236 errors:0 dropped:0 overruns:0 frame:0
          TX packets:96 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:36403 (35.5 KiB)  TX bytes:17092 (16.6 KiB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:54364 errors:0 dropped:0 overruns:0 frame:0
          TX packets:54364 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:2718200 (2.5 MiB)  TX bytes:2718200 (2.5 MiB)

At this point you should be able to ssh into the machine directly and continue the work. From your main computer (hopefully a *NIX variant), open up a shell and ssh to the Pi. You will receive a password challenge:


IngeniiGroup:STRATUX$ ssh pi@10.10.0.11

pi@10.10.0.11's password: _enter your password_

If the login was successful, you will see this welcome banner:

STRATUX welcome banner

Next, will be the modifications to disable the adhoc network and enable local WiFi connectivity.

Disable adhoc ‘stratux’ WiFi

Now that you are logged into the Stratux via ssh, assume root user and then cd to the networking directory again:


pi@raspberrypi:~ $ sudo su -

root@raspberrypi:~# cd /etc/network

root@raspberrypi:~# vi interfaces

Now, this time the interfaces file will be heavily modified to configure the eth0 and wlan0, along with some rational routing, using the ‘metric’ setting to prioritize route assignments.

Once again, at the top of the file another new line will be added:


auto lo
auto wlan0
auto eth0

iface lo inet loopback

Then replacing the entire eth0 and wlan0 entries with the following block of text. NOTE: The line `wireless-power off` will disable the power management for WiFi. If this is not done, I have found that the chip will shut down after a couple of hours and the device will become unreachable via wifi, until it’s rebooted. This discovery took no short amount of time to discovery and remedy.

iface lo inet loopback

iface lo inet loopback

iface wlan0 inet static
   metric 0
   wireless-power off
   hostname Stratux-wlan0
   wpa-conf /etc/wpa_supplicant/wpa_supplicant.conf
   address 10.100.0.21
   netmask 255.255.255.0
   gateway 10.100.0.1
   network 10.100.0.0
   broadcast  10.100.0.255
   dns-nameservers 8.8.8.8 8.8.4.4

iface eth0 inet static
   metric 3
   hostname Stratux-eth0
   address 10.100.0.210
   netmask 255.255.255.0
   gateway 10.100.0.1
   network 10.100.0.0
   broadcast  10.100.0.255
   dns-nameservers 8.8.8.8 8.8.4.4 

Restart networking and verify everything is working by logging back in.


root@raspberrypi:~# service networking restart

IngeniiGroup:STRATUX$ ssh pi@10.10.0.11
pi@10.10.0.11's password: _enter your password_
pi@raspberrypi:~ $ sudo su -

If you were able to log in again, using the fixed IP address, then the first part of the static IP configurations are completed.

Configure the wlan0 WiFi

NOTE!: As of 14-MAY-2017 and Raspberry Pi 3b; 5.0GHz Wifi IS NOT SUPPORTED.

Now.. the really fun part.. connecting the Pi to your WiFi network. Obviously I will not be displaying my own real WiFi access credentials, so you will need to find the SSID you want to connect to and the password for that network before starting.

The networking configuration that you setup in the previous step contains this setting: ‘wpa-conf /etc/wpa_supplicant/wpa_supplicant.conf‘. The next step is to create/configure that file.

The file is fairly simple, and with the SSID and Password you already obtained for your network (you did that already.. right?). You’ll simple fill that information into the blanks named _SSID_ and _NETWORK_PASS_.

Open the file and edit:


root@raspberrypi:~# vi /etc/wpa_supplicant/wpa_supplicant.conf

ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

Add this block below the original lines, using your settings:

network={
        ssid="_SSID_"
        psk="_NETWORK_PASS_"
}

Save this file, restart networking, signal a daemon reload, and then, reboot of the device. While it is rebooting, unplug the Ethernet cable (from eth0). Once the networking is back up, check to make sure you are able to contact the machine via it’s WiFi connection.

root@raspberrypi:~# service networking restart
root@raspberrypi:~# ystemctl daemon-reload
root@raspberrypi:~# reboot

After 2-3 Min. the Pi should have completed restarting and you should be able to verify a successful WiFi login.


IngeniiGroup:STRATUX$ ssh pi@10.100.0.21

Increasing Root Filesystem Space

By default, the size of the disk partitions on the image is VERY small; less than 2GB. Most of that space is used by by the base Stratux install, leaving about 400MB of space to save logs (and your replay database if you turn it on). You can see this with the ‘df’ command:

root@raspberrypi:~# df -h

Filesystem      Size  Used Avail Use% Mounted on
/dev/root       1.8G  1.4G  324M  81% /
devtmpfs        459M     0  459M   0% /dev
tmpfs           463M     0  463M   0% /dev/shm
tmpfs           463M   12M  451M   3% /run
tmpfs           5.0M  4.0K  5.0M   1% /run/lock
tmpfs           463M     0  463M   0% /sys/fs/cgroup
/dev/mmcblk0p1   60M   20M   41M  34% /boot

This just isn’t enough space, especially for the ‘/var/log’ directory where a lot of transient/logging data is written. My solution is to create a disk partition and mount it to `/var/log`.

Locate the disk device

Instructions on the web are not exactly correct, some suggest /dev/sda as the main device, however my testing shows it’s actually this named ‘/dev/mmcblk0’.


root@raspberrypi:~# fdisk -l | grep Disk
[...]
Disk /dev/mmcblk0: 14.5 GiB, 15523119104 bytes, 30318592 sectors

… with the following partitions:

Device Boot Start End Sectors Size Id Type
/dev/mmcblk0p1 8192 131071 122880 60M c W95 FAT32 (LBA)
/dev/mmcblk0p2 131072 3887103 3756032 1.8G 83 Linux
Running fdisk

With the physical partition located.. start fdisk:


fdisk -u /dev/mmcblk0

Welcome to fdisk (util-linux 2.25.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

First, order of business is to increase the size of the main partition, to give it a big more room than just 1.8GB. I like to bump it up to around 4GB to leave room for installing more system updates and tools. To do this you will need to know the starting and ending blocks of the partition. That is available with the ‘print’ command:


Command (m for help): p

Disk /dev/mmcblk0: 14.5 GiB, 15523119104 bytes, 30318592 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0xe6a544c8

Device Boot Start End Sectors Size Id Type
/dev/mmcblk0p1 8192 131071 122880 60M c W95 FAT32 (LBA)
/dev/mmcblk0p2 131072 3887103 3756032 1.8G 83 Linux

Now delete the partition. Yes.. feels VERY dangerous.. and it is.. but as long as the starting block is maintained, and the end block number is increased.. this will end up being a safe operation. Verify that you targeted the correct partition by using ‘p’ again:


Command (m for help): d
Partition number (1,2, default 2): 2

Partition 2 has been deleted.

Command (m for help): p
Disk /dev/mmcblk0: 14.5 GiB, 15523119104 bytes, 30318592 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disklabel type: dos
Disk identifier: 0xe6a544c8

Device Boot Start End Sectors Size Id Type
/dev/mmcblk0p1 8192 131071 122880 60M c W95 FAT32 (LBA)

If this is correct, now recreate it with same number (2), start and type but with a bigger end (taking care not to overlap with other partitions). Try to align things on a megabyte boundary that is for end, make it a multiple of 2048 minus 1. Change the type if needed with t (for partitions holding an extX or btrfs filesystem, the default of 83 is fine). Then `w` to write and `q` to quit.


Command (m for help): n
Partition type
p primary (1 primary, 0 extended, 3 free)
e extended (container for logical partitions)
Select (default p): p
Partition number (2-4, default 2): 2
First sector (2048-30318591, default 2048): 131072
Last sector, +sectors or +size{K,M,G,T,P} (131072-30318591, default 30318591): 8451072

Created a new partition 2 of type 'Linux' and of size 4 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Re-reading the partition table failed.: Device or resource busy

The kernel still uses the old table. The new table will be used at the next reboot or after you run partprobe(8) or kpartx(8).

root@raspberrypi:~#

The partition table will have been modified but the kernel will not be able to take that into account as some partitions are mounted.

However, if in-use partitions were only enlarged, you should be able to force the kernel to take the new layout with:


root@raspberrypi:~# partx /dev/mmcblk0
NR START END SECTORS SIZE NAME UUID
1 8192 131071 122880 60M e6a544c8-01
2 131072 8451072 8320001 4G e6a544c8-02

If the command works the next step is to expand the filesystem. In my case I needed to reboot before the kernel picked up the new partition size, despite running partx to fill up this new space.


root@raspberrypi:~# init 6

Following the restart, execute `resize2fs` and run an on-line expansion of the filesystem, and finally verify it again with ‘df -h’


root@raspberrypi:~# resize2fs /dev/mmcblk0p2
resize2fs 1.42.12 (29-Aug-2014)
Filesystem at /dev/mmcblk0p2 is mounted on /; on-line resizing required
old_desc_blocks = 1, new_desc_blocks = 1
The filesystem on /dev/mmcblk0p2 is now 1040000 (4k) blocks long.

root@raspberrypi:~# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 3.9G 1.4G 2.4G 36% /
devtmpfs 459M 0 459M 0% /dev
tmpfs 463M 0 463M 0% /dev/shm
tmpfs 463M 6.2M 457M 2% /run
tmpfs 5.0M 4.0K 5.0M 1% /run/lock
tmpfs 463M 0 463M 0% /sys/fs/cgroup
/dev/mmcblk0p1 60M 20M 41M 34% /boot

The next step is to add a 3rd partition which will then be mounted to `/var/log`

Creating a dedicated filesystem for logging / database

I ended up creating 3 primary partitions, the largest of which will be mounted to `/var/log`.


fdisk /dev/mmcblk0

Command (m for help): n
Partition type
p primary (2 primary, 0 extended, 2 free)
e extended (container for logical partitions)
Select (default p): p
Partition number (3,4, default 3): 3
First sector (2048-30318591, default 2048): 8451073
Last sector, +sectors or +size{K,M,G,T,P} (8451073-30318591, default 30318591): 30318591

Created a new partition 3 of type 'Linux' and of size 10.4 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Re-reading the partition table failed.: Device or resource busy

The kernel still uses the old table. The new table will be used at the next reboot or after you run partprobe(8) or kpartx(8).

root@raspberrypi:~# partprobe

Checked to make sure the device was crated by checking the `/dev` directory:


root@raspberrypi:~# ls -l /dev/mmcblk0*
brw-rw---- 1 root disk 179, 0 May 15 15:11 /dev/mmcblk0
brw-rw---- 1 root disk 179, 1 May 15 15:11 /dev/mmcblk0p1
brw-rw---- 1 root disk 179, 2 May 15 15:11 /dev/mmcblk0p2
brw-rw---- 1 root disk 179, 3 May 15 15:11 /dev/mmcblk0p3

Next, put a filesystem on this new partition. Using df to determine the type of filesystem currently in use; I recommend that you stick with it for this most basic of operations:


root@raspberrypi:~# df -T

Filesystem Type 1K-blocks Used Available Use% Mounted on
/dev/root ext4 4063680 1392604 2512268 36% /
devtmpfs devtmpfs 469688 0 469688 0% /dev
tmpfs tmpfs 474004 0 474004 0% /dev/shm
tmpfs tmpfs 474004 6340 467664 2% /run
tmpfs tmpfs 5120 4 5116 1% /run/lock
tmpfs tmpfs 474004 0 474004 0% /sys/fs/cgroup
/dev/mmcblk0p1 vfat 61384 20416 40968 34% /boot

Run mkfs to initialize the filesystem.


/sbin/mkfs -t ext4 /dev/mmcblk0p3

Creating filesystem with 2733439 4k blocks and 684096 inodes
Filesystem UUID: 94f004af-7008-4dbe-8805-3eb2d739436b
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information:... this might go on for a bit..

Once completed.. mount this where the logs and databases live. To do this the first thing that needs to happen is to check your current fstab:


cat /etc/fstab
proc /proc proc defaults 0 0
/dev/mmcblk0p1 /boot vfat defaults 0 2
/dev/mmcblk0p2 / ext4 defaults,noatime 0 1
# a swapfile is not a swap partition, no line here
# use dphys-swapfile swap[on|off] for that

My first order of business was to copy the current `/var/log` to a new location, create a new clean mount point for `/var/log` and then mount the new filesystem, and then verify it’s mounted using `df -h`


root@raspberrypi:~# mv /var/log /var/log2
root@raspberrypi:~# mkdir /var/log
root@raspberrypi:~# mount -t ext4 /dev/mmcblk0p3 /var/log
root@raspberrypi:~# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 3.9G 1.4G 2.4G 36% /
devtmpfs 459M 0 459M 0% /dev
tmpfs 463M 0 463M 0% /dev/shm
tmpfs 463M 6.2M 457M 2% /run
tmpfs 5.0M 4.0K 5.0M 1% /run/lock
tmpfs 463M 0 463M 0% /sys/fs/cgroup
/dev/mmcblk0p1 60M 20M 41M 34% /boot
/dev/mmcblk0p3 11G 27M 9.6G 1% /var/log

Edit the fstab file to create a mount point for the new partition where the logs used to be written (added the orange line), and ran mount to verify that it will automount on a restart.


root@raspberrypi:~# vi /etc/fstab

proc /proc proc defaults 0 0
/dev/mmcblk0p1 /boot vfat defaults 0 2
/dev/mmcblk0p2 / ext4 defaults,noatime 0 1
/dev/mmcblk0p3 /var/log ext4 defaults,noatime 0 0
# a swapfile is not a swap partition, no line here
# use dphys-swapfile swap[on|off] for that

root@raspberrypi:~# mount -a

Restart and verify

Restart the little box and verify that the mount was preserved.


init 6

Log back in, and run df to check the filesystem health. It should now has the the main filesystem has some breathing room again:


pi@raspberrypi:~ $ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/root 3.9G 1.4G 2.4G 36% /
devtmpfs 459M 0 459M 0% /dev
tmpfs 463M 0 463M 0% /dev/shm
tmpfs 463M 6.2M 457M 2% /run
tmpfs 5.0M 4.0K 5.0M 1% /run/lock
tmpfs 463M 0 463M 0% /sys/fs/cgroup
/dev/mmcblk0p1 60M 20M 41M 34% /boot
/dev/mmcblk0p3 11G 27M 9.6G 1% /var/log

Setting Time with NTPD

If you do not have a GPS receiver attached to your Stratux, then it might not be able to determine proper system time. When this is the case, enabling ntpd will be your solution. Using national time sync services, it will keep your system clock correct. If you have your GPS plugged in, it’s getting really good timing signals already.. or the location function would not work at all! For the non GPS users:

Install ndptdate:


root@raspberrypi:~# apt-get install ntpdate
Reading package lists... Done
Building dependency tree
Reading state information... Done
[...]
Do you want to continue? [Y/n] Y
Get:1 http://mirrordirector.raspbian.org/raspbian/ jessie/main liblockfile-bin armhf 1.09-6 [18.2 kB]
Get:2 http://mirrordirector.raspbian.org/raspbian/ jessie/main liblockfile1 armhf 1.09-6 [14.7 kB]
Get:3 http://mirrordirector.raspbian.org/raspbian/ jessie/main ntpdate armhf 1:4.2.6.p5+dfsg-7+deb8u2 [69.0 kB]
[...]
Setting up ntpdate (1:4.2.6.p5+dfsg-7+deb8u2) ...
Processing triggers for libc-bin (2.19-18+deb8u3) ...

It should now start at bootup and resolve any timing issues you might have.

Update your Pi with the latest updates and security patches

Some might remember “Black Friday” when a worm created some serious disruption in the tech world.. including at hospitals in the UK. And it happened primarily because people are not applying their security patches! Although the risk of your Pi being botified and ransomed my not be high.. you should be updating it regardless! So, let’s do that now.


root@raspberrypi:~# apt-get install ntpdate
root@raspberrypi:~# apt-get dist-upgrade

Finish up with this command to clean up some of the used disk space. Since you’ve already bumped the numbers on your partitions in the previous steps.. this is not nearly as necessary, but why leave unused stuff lying around? Your mother taught you to clean up afteryourself, right?


root@raspberrypi:~# sudo apt-get clean

AND THAT DOES IT FOR THIS EPISODE!

Stratux Webserver – what’s behind the scenes?

Stratux has a rich community of forums, and a lot of information about debugging Strtux, but so far good hacking information is really hard to find. One of the things of most interest to me was “What is severing up this webpage?”

Every search was a dead end, so I went back to my *NIX system administration roots and thought.. “Well, if someone wont admit what’s serving up the stream.. I’ll find out for myself.

Who’s Your Server? — gen_gdl90

A couple of quick commands told me which PID was hanging onto Port 80 and from there which process was associated with the PID:


root@raspberrypi:~# fuser 80/tcp
80/tcp: 3110
root@raspberrypi:~# ps aux | grep 3110
root 3110 51.9 2.9 973132 27600 ? Ssl 15:19 47:03 /usr/bin/gen_gdl90
root 6606 0.0 0.2 4276 1904 pts/0 S+ 16:50 0:00 grep 3110
What files does it have open?

Once I had an idea of who’m I was looking for, running lsof with the port number gave me 60 entries… and bingo.. there was the nugget of gold I was looking for:


root@raspberrypi:~# lsof -p 3110
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
gen_gdl90 3110 root cwd DIR 179,2 4096 2 /
[...]
gen_gdl90 3110 root 35u IPv6 1849628 0t0 TCP StratuxWiFi.io:http->10.100.0.188:49157 (ESTABLISHED)
gen_gdl90 3110 root 36u IPv6 210216 0t0 TCP StratuxWiFi.io:http->10.100.0.188:64155 (ESTABLISHED)
gen_gdl90 3110 root 37u IPv6 1859641 0t0 TCP StratuxWiFi.io:http->10.100.0.188:49165 (ESTABLISHED)
gen_gdl90 3110 root 38u IPv6 1759876 0t0 TCP StratuxWiFi.io:http->10.100.0.188:65420 (ESTABLISHED)
gen_gdl90 3110 root 41u IPv6 1778784 0t0 TCP StratuxWiFi.io:http->10.100.0.188:65442 (CLOSE_WAIT)
gen_gdl90 3110 root 48r REG 179,2 146998 51146 /var/www/maui/js/angular.min.js
The Web Path

Once onto the trail of the web path, I see that this is an Angular based application (ugh.. I really despise Angular.. I just do.), and all based on some JS stuffs. I get it. for an app like this the two-way data binding of Angular is probably the right too; but I still do not (no do I have to) like it.


root@raspberrypi:~# cd /var/www/maui/
root@raspberrypi:/var/www/maui# ls -l
total 12
drwxr-xr-x 2 root root 4096 Mar 15 2016 css
drwxr-xr-x 2 root root 4096 Mar 15 2016 fonts
drwxr-xr-x 2 root root 4096 Mar 15 2016 js
root@raspberrypi:/var/www/maui#

What I was hoping to find was the location of the that status page.. but.. I believe that what I’m looking for now is the .JS file that manages that label. Initially this looked like a dead end…


root@raspberrypi:/var/www/maui# egrep -r Distance *
root@raspberrypi:/var/www/maui#

Realiazing this was some templating sub-directory, and the root was likely at /var/www, I ran another search that found the location of the desired string, and likely the location of the parts I’m looking for:


root@raspberrypi:~# cd ..
root@raspberrypi:/var/www# egrep -lnr 'Distance' *

plates/js/traffic.js
plates/traffic-help.html
plates/traffic.html

STRATUX – Filesystem Full; Managing disk space redux

Checking in with my Starux project this morning, I found it unresponsive. A physical check shows a flashing red light on the Pi… something has gone haywire, and I couldn’t SSH into the little thing, so a really hard cold restart was in order.

Following the restart I quickly shut Stratux back down to start performing diagnostics:


pi@raspberrypi:~ $ sudo su -

root@raspberrypi:~# service stratux stop

Diagnostics 101

Check Filesystem Health

The last time the little Stratux suddenly had problems it was a filesystem space issue ( previous article ). It turns out the boot drive space was OK but the new Logging partition was again, completely consumed:

Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/root        1815440 1391284    331164  81% /
devtmpfs          469688       0    469688   0% /dev
tmpfs             474004       0    474004   0% /dev/shm
tmpfs             474004    6340    467664   2% /run
tmpfs               5120       4      5116   1% /run/lock
tmpfs             474004       0    474004   0% /sys/fs/cgroup
/dev/mmcblk0p1     61384   20400     40984  34% /boot
/dev/mmcblk0p4   8125880 8109496         0 100% /var/log

Locate the Culprit

There are a number of ways to locate large file on a *NIX system. My favorite tool is find. First thing I want to do is locate any file that is larger than 1 Gigabyte, and sure enough it located a massive sqlite database file. The same one that ate up all the space on the boot drive. So.. this is going to require some more extrodinary measures to maintain 100% 24×7 operational status.


root@raspberrypi:~# cd /var/log
root@raspberrypi:/var/log# find . -size +1G -exec ls -l {} \;
-rw-r--r-- 1 root root 7463211008 Apr 29 21:48 ./stratux.sqlite
Start Solving

First order of business is to move aside the massive database, but try to preserve the data for examination. Since the filesystem is full, I can’t zip this thing in place, so first some space needs to cleared on the device. The things I’m least interested in go first.. like the zipped syslogs and any other ‘archived’ file (those with a .# suffix).

-rw-r----- 1 root adm      728756 Apr 25 06:25 syslog.5.gz
-rw-r----- 1 root adm     2552845 Apr 26 06:25 syslog.4.gz
-rw-r----- 1 root adm     2447263 Apr 27 06:25 syslog.3.gz
-rw-r----- 1 root adm     2498089 Apr 28 06:25 syslog.2.gz
-rw-r----- 1 root adm    33735478 Apr 29 06:25 syslog.1
-rw-r----- 1 root adm       54307 Apr 30 05:19 debug.1
-rw-r----- 1 root adm      380928 Apr 30 05:19 kern.log.1
-rw-r----- 1 root adm   332365824 Apr 30 06:18 daemon.log.1
-rw-r----- 1 root adm       48723 Apr 30 06:25 auth.log.1
-rw-r----- 1 root adm           0 Apr 30 06:25 syslog.1.gz
-rw-r----- 1 root adm     1032192 Apr 30 06:25 messages.1

root@raspberrypi:/var/log# rm -f *.gz *\.[0-9]

But.. that’s not going to doe the complete trick, especially if after deleting files df still shows 100% utilization. You need to figure out what is holding which deleted file(s).

Normallyh, the best way to do that on *NIX is with lsof. Much to my chagrin, it was not available on the OS… so I had to go grab it. Thankfully I had the main filesystem on a different partition that still had enough space to install more tools! Once lsof was installed, grep through the list of open file handles and find those marked for delete.. and those are the processes that have handles pointing to those files. However, this didn’t help me with Rasperian Jessie. I know that a reboot will recover the space so.. that was the next step.. a brutal warm-boot.


root@raspberrypi:/var/log# lsof
-su: lsof: command not found

root@raspberrypi:/# apt-get install lsof
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
libperl4-corelibs-perl
The following NEW packages will be installed:
libperl4-corelibs-perl lsof
...

root@raspberrypi:/var/log# lsof | grep deleted
root@raspberrypi:/var/log#

root@raspberrypi:/var/log# init 6

Archiving Massive SQLite Database

The first thing was to move aside the current database, and then restart stratux to verify it can created a new empty database for it’s purposes.. then shut it right back down again.


root@raspberrypi:/var/log# mv stratux.sqlite stratux.sqlite.1
root@raspberrypi:/var/log# service stratux start
root@raspberrypi:/var/log# ls -l
total 7291480
[...]
-rw-r--r-- 1 root root 4096 Apr 30 14:27 stratux.sqlite
-rw-r--r-- 1 root root 7466160128 Apr 30 14:24 stratux.sqlite.1
[...]
root@raspberrypi:/var/log# service stratux stop

A new empty database file has been created [ 4096 Apr 30 14:27 stratux.sqlite ]. This tells me that moving aside the current database file on a periodic basis, compressing and then archiving it should be sufficient to maintain operational status.

Logging Insanity

Before restarting Stratux, I zerod out these log files. Running for first a few moments these files were already reading up a lot of space. Tailing one of them I see that Stratux, with my current settings is logging A LOT of data to these log files. I feel this was my first error.. enabling too much logging. My settings look like this:

With those settings enabled, there are a lot of GPS and other events that I don’t really have a use for, being dumped into the Statux log.


-rw-r--r-- 1 root root 1361778 Apr 30 14:39 stratux.log

Turning OFF ‘Verbose Message Log’ made that insanity stop.

Replay logging is what is writing to the SQLite database. So the question is. how much of that data do I want to keep, and how much will I lose if I turn off the replay logs. I think that will be research for another day… right now the goal is to recover disk space by compressing the massive database file that was moved aside, and get Stratux stabilized again. Once compressed, check filesystem and file size!


root@raspberrypi:/var/log# gzip stratux.sqlite.1

root@raspberrypi:/var/log# df

Filesystem     1K-blocks    Used Available Use% Mounted on
/dev/root        1815440 1392384    330064  81% /
devtmpfs          469688       0    469688   0% /dev
tmpfs             474004       0    474004   0% /dev/shm
tmpfs             474004   12272    461732   3% /run
tmpfs               5120       4      5116   1% /run/lock
tmpfs             474004       0    474004   0% /sys/fs/cgroup
/dev/mmcblk0p4   8125880  681792   7008276   9% /var/log
/dev/mmcblk0p1     61384   20400     40984  34% /boot

root@raspberrypi:/var/log# ls -lktr --color --block-size=M stratux.sqlite*

-rw-r--r-- 1 root root 643M Apr 30 14:24 stratux.sqlite.1.gz
-rw-r--r-- 1 root root   8M Apr 30 15:21 stratux.sqlite
-rw-r--r-- 1 root root   1M Apr 30 15:22 stratux.sqlite-shm
-rw-r--r-- 1 root root   5M Apr 30 15:22 stratux.sqlite-wal

With the file compressed, it could be copied elsewhere for analysis.

SQLite – get table schema

SQLite is a functional little SQL database that is often found used in embedded systems projects (such as one of my favorites, Stratux).

As far as I know, there are three ways to look at a table’s schema (the 3rd is just and extension of the 2nd).

.schema table_name

Running .schema will show you a ‘CREATE’ statement that can be used to build the database table. The issues I have with using this are that the command only shows the original CREATE, not any subsequent changes such as indexes, are worse yet, new fields! Regardless, here is an example:

.schema traffic
CREATE TABLE traffic (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Icao_addr INTEGER, Reg TEXT, Tail TEXT, Emitter_category INTEGER, OnGround INTEGER, Addr_type INTEGER, TargetType INTEGER, SignalLevel REAL, Squawk INTEGER, Position_valid INTEGER, Lat REAL, Lng REAL, Alt INTEGER, GnssDiffFromBaroAlt INTEGER, AltIsGNSS INTEGER, NIC INTEGER, NACp INTEGER, Track INTEGER, Speed INTEGER, Speed_valid INTEGER, Vvel INTEGER, Timestamp STRING, PriorityStatus INTEGER, Age REAL, AgeLastAlt REAL, Last_seen STRING, Last_alt STRING, Last_GnssDiff STRING, Last_GnssDiffAlt INTEGER, Last_speed STRING, Last_source INTEGER, ExtrapolatedPosition INTEGER, Bearing REAL, Distance REAL, timestamp_id INTEGER);

PRAGMA table_info

This is my favored by far. Format can actually be taken and dropped int a Wiki page, or JIRA case or any other similar document system.. but it’s still not the most ideal, as you can see:


PRAGMA table_info(traffic);

0|id|INTEGER|1||1
1|Icao_addr|INTEGER|0||0
2|Reg|TEXT|0||0
3|Tail|TEXT|0||0
4|Emitter_category|INTEGER|0||0
5|OnGround|INTEGER|0||0
6|Addr_type|INTEGER|0||0
7|TargetType|INTEGER|0||0
8|SignalLevel|REAL|0||0
9|Squawk|INTEGER|0||0
10|Position_valid|INTEGER|0||0
11|Lat|REAL|0||0
12|Lng|REAL|0||0
13|Alt|INTEGER|0||0
14|GnssDiffFromBaroAlt|INTEGER|0||0
15|AltIsGNSS|INTEGER|0||0
16|NIC|INTEGER|0||0
17|NACp|INTEGER|0||0
18|Track|INTEGER|0||0
19|Speed|INTEGER|0||0
20|Speed_valid|INTEGER|0||0
21|Vvel|INTEGER|0||0
22|Timestamp|STRING|0||0
23|PriorityStatus|INTEGER|0||0
24|Age|REAL|0||0
25|AgeLastAlt|REAL|0||0
26|Last_seen|STRING|0||0
27|Last_alt|STRING|0||0
28|Last_GnssDiff|STRING|0||0
29|Last_GnssDiffAlt|INTEGER|0||0
30|Last_speed|STRING|0||0
31|Last_source|INTEGER|0||0
32|ExtrapolatedPosition|INTEGER|0||0
33|Bearing|REAL|0||0
34|Distance|REAL|0||0
35|timestamp_id|INTEGER|0||0

Now.. for my favorite modifier:

PRAGMA table_info with headers!

By turning headers ‘on’ you will now get a tabular list of what each of these fields mean. Note that the first operator command does not use a terminating semi-colon.

.headers on
PRAGMA table_info(traffic);

cid|name|type|notnull|dflt_value|pk
0|id|INTEGER|1||1
1|Icao_addr|INTEGER|0||0
2|Reg|TEXT|0||0
3|Tail|TEXT|0||0
[... you get the idea ...]

35|timestamp_id|INTEGER|0||0

There you go.. three (OK two) ways to list a table schema in SQLite.

Stratux – what’s in that database?

If you’re a hacker, and you’re a data geek.. you probably want to know what’s in that Stratus SQLite database!

Get a SQLite client

I wasn’t able to find a SQLite client on the Straux image, so I installed one with apt-get:


sudo apt-get install sqlite3

Next, run a basic function test to see if it installed and will run:


sqlite3

SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

Cracking open the data file

The sqlist logging / database file is in /var/log, and is named stratux.sqlite.

Cracking open the database and checking the schema I found:


sqlite3 stratux.sqlite

SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite>
sqlite> .tables

dump1090_terminal messages startup traffic
es_messages mySituation status
gps_attitude settings timestamp

Stratux SQLite Tables

Some of these tables contain alot of data:

Table Name Record Count
dump1090_terminal 313290
messages 307
startup 29
traffic 47038
mySituation 0
status 5350
timestamp 252097
settings 0
es_messages 463973
gps_attitude 0
es_messages table

This contains some interesting things. Here are the last 5 records from the database:


select * from es_messages order by timestamp_id desc limit 5;

469502|0001-01-01 00:39:09.91 +0000 UTC|{"Icao_addr":11265096,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.002207,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":22625,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.804Z"}|296790
469503|0001-01-01 00:39:09.97 +0000 UTC|{"Icao_addr":10846969,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.039045,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":12075,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.901Z"}|296790
469498|0001-01-01 00:39:09.53 +0000 UTC|{"Icao_addr":11265096,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.003431,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":22600,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.426Z"}|296789
469499|0001-01-01 00:39:09.53 +0000 UTC|{"Icao_addr":10687795,"DF":11,"CA":1,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":8,"SignalLevel":0.001740,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":null,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":null,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.441Z"}|296789
469500|0001-01-01 00:39:09.69 +0000 UTC|{"Icao_addr":10687795,"DF":0,"CA":0,"TypeCode":0,"SubtypeCode":0,"SBS_MsgType":7,"SignalLevel":0.000488,"Tail":null,"Squawk":null,"Emitter_category":null,"OnGround":false,"Lat":null,"Lng":null,"Position_valid":false,"NACp":null,"Alt":28025,"AltIsGNSS":false,"GnssDiffFromBaroAlt":null,"Vvel":null,"Speed_valid":false,"Speed":null,"Track":null,"Timestamp":"2017-04-24T19:04:54.626Z"}|296789

Selected one of the records and decoded the JSON message:

{
	"Icao_addr": 11265096,
	"DF": 0,
	"CA": 0,
	"TypeCode": 0,
	"SubtypeCode": 0,
	"SBS_MsgType": 7,
	"SignalLevel": 0.002207,
	"Tail": null,
	"Squawk": null,
	"Emitter_category": null,
	"OnGround": false,
	"Lat": null,
	"Lng": null,
	"Position_valid": false,
	"NACp": null,
	"Alt": 22625,
	"AltIsGNSS": false,
	"GnssDiffFromBaroAlt": null,
	"Vvel": null,
	"Speed_valid": false,
	"Speed": null,
	"Track": null,
	"Timestamp": "2017-04-24T19:04:54.804Z"
}

These seems to be the 1090 ‘es’ messages, however (at least this example) is missing tail number and squawk information. It’s interesting.., but not very actionable.

The next table I looked at was a completely different story!

traffic table

This looks like a good dataset to mine. With about 47,000 contacts int it.. this might be the basic dataset I’m looking for to use for some visualization.


select * from traffic limit 5;

1|11017168|N621VA|eaVRD947|0|0|0|1|-18.1550731147|6742|1|36.9193725586|-122.0635986328|13075|650|0|7|8|310|287|1|-1536|2016-02-26 01:22:53.426 +0000 UTC|0|0.27|0.16|0001-01-01 00:04:45.29 +0000 UTC|0001-01-01 00:04:45.4 +0000 UTC|0001-01-01 00:04:45.08 +0000 UTC|13075|0001-01-01 00:04:45.08 +0000 UTC|1|0|0.0|0.0|4
2|11017168|N621VA|eaVRD947|0|0|0|1|-19.4002028306|6742|1|36.9202423096|-122.0649414062|13050|650|0|7|8|310|287|1|-1472|2016-02-26 01:22:54.57 +0000 UTC|0|0.22|0.0|0001-01-01 00:04:46.33 +0000 UTC|0001-01-01 00:04:46.55 +0000 UTC|0001-01-01 00:04:46.55 +0000 UTC|13050|0001-01-01 00:04:46.55 +0000 UTC|1|0|0.0|0.0|7
3|11017168|N621VA|eaVRD947|0|0|0|1|-21.540961611|6742|1|36.9209747314|-122.0660552979|13025|650|0|7|8|310|287|1|-1472|2016-02-26 01:22:55.503 +0000 UTC|0|0.23|0.23|0001-01-01 00:04:47.32 +0000 UTC|0001-01-01 00:04:47.32 +0000 UTC|0001-01-01 00:04:47.48 +0000 UTC|13025|0001-01-01 00:04:47.48 +0000 UTC|1|0|0.0|0.0|10
4|11017168|N621VA|eaVRD947|0|0|0|1|-24.8545224734|6742|1|36.9220275879|-122.0676879883|13000|650|0|7|8|310|287|1|-1472|2016-02-26 01:22:56.453 +0000 UTC|0|0.14|0.14|0001-01-01 00:04:48.41 +0000 UTC|0001-01-01 00:04:48.41 +0000 UTC|0001-01-01 00:04:47.48 +0000 UTC|13025|0001-01-01 00:04:47.48 +0000 UTC|1|0|0.0|0.0|12
5|11017168|N621VA|eaVRD947|0|0|0|1|-22.2004294875|6742|1|36.9220275879|-122.0676879883|12975|650|0|7|8|310|287|1|-1408|2016-02-26 01:22:57.573 +0000 UTC|0|1.14|0.0|0001-01-01 00:04:48.41 +0000 UTC|0001-01-01 00:04:49.55 +0000 UTC|0001-01-01 00:04:49.44 +0000 UTC|12975|0001-01-01 00:04:49.44 +0000 UTC|1|0|0.0|0.0|14

Question.. what are the top 10 traffic events by Tail number?
Let’s find out! The simplest way I know of to do this.. and it offers the advantage that the select query against the large dataset with aggregation only runs ONCE.. is to get the parts you want, and the summary count and jam them into a temporary table. With SQLite, you can do it like this:


CREATE TABLE traffic_summary AS select Reg,Tail,count(*) as hits from traffic group by Tail;

That produced a summary table with 423 records in it:


select count(*) from traffic_summary;

423

Now.. who’s the nosiest one of them all?


select * from traffic_summary WHERE Tail>'' order by hits DESC limit 10;

N713FR|ea1435|660
N214NN|CPZ6073|624
N887NN|eaAL2508|564
|eaAL8215|553
N76503|UAL1010|539
N141SY|SKW5982|511
N363VA|VRD1935|501
N204NN|CPZ6074|494
N630VA|VRD941|491
|eaAAR284|478

Using a little sleuthing.. I found that ‘N713FR’ is a Frontier Airlines Airbus 321

Upcoming Article: Creating some tools to automate acquisition of this information and find out just who’s flying over and how often.

Stratux – handling a full filesystem

Running a Stratux in test-bench mode (not in an aircraft, and for days at a time), you’ll likely run into an issue with disk space. The ISO image I acquired from Stratux only provided a 1.8 BG partition for things to live in, and it’s quickly exhausted.

Here is the status of my system after running for about 36 hours… it’s full.

Filesystem Size Used Avail Use% Mounted on
/dev/root 1.8G 1.8G 0 100% /
devtmpfs 459M 0 459M 0% /dev
tmpfs 463M 0 463M 0% /dev/shm
tmpfs 463M 30M 434M 7% /run
tmpfs 5.0M 4.0K 5.0M 1% /run/lock
tmpfs 463M 0 463M 0% /sys/fs/cgroup
/dev/mmcblk0p1 60M 20M 41M 34% /boot

Now to get about the business of increasing the partition and filesystem size without destroying it. First

Locate the disk device

Instructions on the web are not exactly correct, some suggest /dev/sda as the main device, however my testing shows it’s actually this named ‘/dev/mmcblk0’.


root@raspberrypi:~# fdisk -l | grep Disk
[...]
Disk /dev/mmcblk0: 14.5 GiB, 15523119104 bytes, 30318592 sectors

… with the following partitions:

Device Boot Start End Sectors Size Id Type
/dev/mmcblk0p1 8192 131071 122880 60M c W95 FAT32 (LBA)
/dev/mmcblk0p2 131072 3887103 3756032 1.8G 83 Linux
Running fdisk

With the physical partition located.. start fdisk:


fdisk /dev/mmcblk0

Welcome to fdisk (util-linux 2.25.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

I ended up creating 3 primary partitions. The plan is to delete partition 3 and then re-size the main partition to use up remaining space:


Device Boot Start End Sectors Size Id Type
/dev/mmcblk0p1 8192 131071 122880 60M c W95 FAT32 (LBA)
/dev/mmcblk0p2 131072 3887103 3756032 1.8G 83 Linux
/dev/mmcblk0p3 2048 8191 6144 3M 5 Extended
/dev/mmcblk0p4 3887104 20664319 16777216 8G 83 Linux

Command (m for help): d
Partition number (1-5, default 5): 3

Partition 3 has been deleted.

Device Boot Start End Sectors Size Id Type
/dev/mmcblk0p1 8192 131071 122880 60M c W95 FAT32 (LBA)
/dev/mmcblk0p2 131072 3887103 3756032 1.8G 83 Linux
/dev/mmcblk0p4 3887104 20664319 16777216 8G 83 Linux

Write out the partition and… then run to enable it:


Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.

partprobe

Next, put a filesystem on this new partition. Using df to determine the type of filesystem currently in use; I recommend that you stick with it for this most basic of operations:


df -T

Filesystem Type 1K-blocks Used Available Use% Mounted on
/dev/root ext4 1815440 1799056 0 100% /

Run mkfs


/sbin/mkfs -t ext4 /dev/mmcblk0p4

Creating filesystem with 2097152 4k blocks and 524288 inodes
Filesystem UUID: e36a8f6c-a457-4531-b67d-bea4885a9583
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632

Allocating group tables: done
Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information:... this might go on for a bit..

Once completed.. mount this where the logs and databases live. To do this the first thing that needs to happen is to check your current fstab:


cat /etc/fstab
proc /proc proc defaults 0 0
/dev/mmcblk0p1 /boot vfat defaults 0 2
/dev/mmcblk0p2 / ext4 defaults,noatime 0 1
# a swapfile is not a swap partition, no line here
# use dphys-swapfile swap[on|off] for that

My first order of business was to mount the new filesystem to a temporary location (/var/log2) and then copy the contents of /var/log to that location, then delete everything in the log directory, and then unmount log2.


/var/log2

mount -t ext4 /dev/mmcblk0p4 /var/logs

cp -R log/* log2/.

cd log
rm -rf *

umount /dev/mmcblk0p4

Edit the fstab file to create a mount point for the new partition where the logs used to be written (added the orange line), and ran mount to verify that it will automount on a restart.


vi /etc/fstab

proc /proc proc defaults 0 0
/dev/mmcblk0p1 /boot vfat defaults 0 2
/dev/mmcblk0p2 / ext4 defaults,noatime 0 1
/dev/mmcblk0p4 /var/log ext4 defaults,noatime 0 0

mount -a

df

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/root 1815440 1768992 0 100% /
devtmpfs 469688 0 469688 0% /dev
tmpfs 474004 0 474004 0% /dev/shm
tmpfs 474004 35972 438032 8% /run
tmpfs 5120 4 5116 1% /run/lock
tmpfs 474004 0 474004 0% /sys/fs/cgroup
/dev/mmcblk0p1 61384 20400 40984 34% /boot
/dev/mmcblk0p4 8125880 333800 7356268 5% /var/log

Restart and verify

Restart the little box and verify that the mount was preserved.


init 6

Log back in, and run df to check the filesystem health. It should now has the the main filesystem has some breathing room again:

                                                                                              
 ad88888ba  888888888888  88888888ba          db    888888888888  88        88  8b        d8  
d8"     "8b      88       88      "8b        d88b        88       88        88   Y8,    ,8P   
Y8,              88       88      ,8P       d8'`8b       88       88        88    `8b  d8'    
`Y8aaaaa,        88       88aaaaaa8P'      d8'  `8b      88       88        88      Y88P      
  `"""""8b,      88       88""""88'       d8YaaaaY8b     88       88        88      d88b      
        `8b      88       88    `8b      d8""""""""8b    88       88        88    ,8P  Y8,    
Y8a     a8P      88       88     `8b    d8'        `8b   88       Y8a.    .a8P   d8'    `8b   
 "Y88888P"       88       88      `8b  d8'          `8b  88        `"Y8888Y"'   8P        Y8  

NOTE TO DEVELOPERS: Make sure that your system has an acceptable clock source, i.e., a GPS
with sufficient signal or enable ntpd (internet connection required).

Everything here comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.

Type 'stratux-help' (as root) for a few debugging commands.
pi@raspberrypi:~ $ df

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/root 1815440 1389856 332592 81% /
devtmpfs 469688 0 469688 0% /dev
tmpfs 474004 0 474004 0% /dev/shm
tmpfs 474004 6336 467668 2% /run
tmpfs 5120 16 5104 1% /run/lock
tmpfs 474004 0 474004 0% /sys/fs/cgroup
/dev/mmcblk0p4 8125880 329820 7360248 5% /var/log
/dev/mmcblk0p1 61384 20400 40984 34% /boot

At a later date I’ll work on expanding the main partition, but for now this should stabilize the machine and resolve the main disk consumption issue.

Racing, Photography, Software and Politics.