Indoor temperature monitoring

This is not particularly technical but might interest someone.  I live in interior Alaska and during the winter it's not that unusual for the outside temperatures to drop to -40, or lower.  Needless to say, if the indoor heating fails some really terrible things could happen to the plumbing and other household systems,  so using the software that pulls data out of my weather station and uploads it to Weather Underground I was able to throw together a trivial script to send email and an SMS message if the indoor temperature drops below 60F.

I have an Ambient Weather WS-1080, which was the predecessor to this and substantially similar.  It's pretty unsophisticated and not that accurate but it's (mostly) sufficient for my purposes and is absolutely an excellent value.  I use Tee-Boy's WeatherSnoop to pull data off the weather station console (there's a USB port) and upload it to wunderground.com.

WeatherSnoop has an option to write the data out to a sqlite database, and being curious, of course, I turned it on.  You can look at the contents of a database from the sqlite command line, and this is what I saw:

sqlite> .tables
barometricPressure  extraHumidity8      extraTemperature9   rainRate          
barometricTrend     extraHumidity9      forecast            solarRadiation    
dayRain             extraTemperature1   indoorDewPoint      uvIndex           
extraHumidity1      extraTemperature10  indoorHeatIndex     windChill         
extraHumidity10     extraTemperature2   indoorHumidity      windDirection     
extraHumidity2      extraTemperature3   indoorTemperature   windGust          
extraHumidity3      extraTemperature4   monthRain           windSpeed         
extraHumidity4      extraTemperature5   outdoorDewPoint     yearRain          
extraHumidity5      extraTemperature6   outdoorHeatIndex  
extraHumidity6      extraTemperature7   outdoorHumidity   
extraHumidity7      extraTemperature8   outdoorTemperature
sqlite>

The table "indoorTemperature" popped right out as something I could use to monitor the house when I was away from home, so I took a look at the schema and found that there are two values: time and value:

sqlite> .schema indoorTemperature
CREATE TABLE indoorTemperature ( 'time' INTEGER, 'value' FLOAT );
sqlite>

So, I just wrote a script to dump the indoor Temperature table

#!/bin/ksh

WEATHERFILE=/Users/melinda/Documents/weather.db

sqlite3 $WEATHERFILE <<EOF 
.separator " "
select strftime('%Y:%m:%d:%H:%M', time, 'unixepoch', 'localtime'),value from indoorTemperature;
EOF

What this does is use a Unix "here document" to provide scripted commands to the sqlite command line.  .separator " " changes the character used to separate fields on output to a space (by default it's a vertical bar, or pipe character).

The select statement is actually pretty straightforward and kind of inefficient and brainless, to be honest.  Basically I'm selecting every record and formatting the time for printing.

This little routine is invoked by the real script, which looks like this:

#!/bin/ksh

CURTMP=`/Users/melinda/lib/curindoortemp | tail -1 | cut -d" " -f2`
ADDR=0000000000@msg.acsalaska.com,xxxxxxx@gmail.com
THRESHHOLD=60

if [ $CURTMP -lt $THRESHHOLD ]
then
        Mail $ADDR <<EOF
        Temperature is $CURTMP
EOF
fi

The curindoortemp script is the one described above.  I grab the last record and print the second field, which is the temperature.  If it's less than the thresshold value (in this case, 60 degrees Fahrenheit) I send off email to the addresses listed in the ADDR field.  Obviously it would be far better programming practice to have the curindoortemp script just return the temperature from the last record.