Wednesday, May 20, 2020

Old Phone/Tablet as an Info Board: Update 3 - The elusive sunrises and sunsets

While I was messing up with the infoboard's bus widget, I also decided to touch up on the weather forecast (see the detailed description in the earlier post) and and add a few more visual cues to it.

I wanted to distinguish day and night, so that it becomes immediately clear how the weather behaves at sunset and during the morning commute, kind of like so:



My first attempt was very simple: I added a function eachTime() that did this:

function eachTime(){
 var str = $(this).text();
 var hhmm = str.split(":");
 var sunrise = 6; var sunset = 22;
 if (hhmm[0]>=sunset || hhmm[0] < sunrise) {
  $(this).css("background","rgb(0,0,0)");
  $(this).css("color","rgb(255,255,255)");
}}

along with a single line in forecast():
$("#rowTime").find("td").each(eachTime);

However, this seemed a bit artificial to declare night as "anything from 22:00 till 6:00", so I wanted actual sunrise/sunset times to be calculated. Although, with my degree of precision of 1 hour, this seemed like a relatively simple mathematical task, there was no need to reinvent the wheel since PHP already has date_sunrise() and date_sunset() functions. So I geared up a new PHP script as follows:
<?php
$remote_dtz = new DateTimeZone('US/Eastern');
$remote_dt = new DateTime("now", $remote_dtz);
$offset = ($remote_dtz->getOffset($remote_dt))/3600;
$lat=43.6;$lon=-79.6;
echo '<html><head></head><body>';
echo '<span id="sunrise">';
echo(ceil(date_sunrise(time(),SUNFUNCS_RET_DOUBLE,$lat,$lon,90,$offset)));
echo '</span>';
echo(':'); echo '<span id="sunset">';
echo(ceil(date_sunset(time(),SUNFUNCS_RET_DOUBLE,$lat,$lon,90,$offset)));
echo '</span>';echo '</body></html>';
?> 

The only trick here is to get the correct GMT offset; I am also rounding up the result to the nearest hour. It is then very easy to load this script in another hidden iframe like so:
<iframe id="astronomy" style="visibility: hidden; height: 0px !important;" src="astro.php"> </iframe>

and amend eachTime() as follows:

function eachTime(){
 var str = $(this).text();
 var hhmm = str.split(":");
 var dom=$("iframe#astronomy").contents();
  var sunrise = 6; var sunset = 22; //fallback
  sunrise = parseInt(dom.find("#sunrise").text());
  sunset = parseInt(dom.find("#sunset").text());
 var commute = 8;
 if (hhmm[0]>=sunset || hhmm[0] < sunrise) {
  $(this).css("background","rgb(0,0,0)");
  $(this).css("color","rgb(255,255,255)");
  if (hhmm[0]==commute){ $(this).css("color","rgb(225,255,0)");$(this).css("border","1px solid yellow");}
 }
 else{  if (hhmm[0]==commute) {$(this).css("border","1px solid black"); $(this).css("background","rgb(255, 213, 171)");}}  
}


There is no need to bother about refreshing astro.php because the entire system refreshes at 2 am every day anyway, and even if we happen to be 1 day off we are well within the desired accuracy margin. In addition I have added a cue mark for the morning commute, which is (unfortunately) independent of where the Sun happens to be. (And your mileage may vary here too, distinguishing weekdays from weekends and even accounting for statutory holidays if need be.)


BONUS: As an addition, notably after a strong wind storm in our area, I wanted to colour code wind as well, along with rain and temperature. Here's what I changed about eachWind():

function windgradient(base,gust){ 
var b=(base>=80)?1.0:(base/80.0);
var g=((gust-base)>=25)?1.0:((gust-base)/25.0);
if (g<0) g=0;
b=Math.pow(b,0.75);g=Math.pow(g,1.0);
    return "rgb(" 
  + Math.round(255.0) + "," 
  + Math.round(255.0*(1.0-b*g)) + "," 
  + Math.round(255.0*(1.0-b)) + ")";
}

function eachWind(){
 var rawstr = str = $(this).text().trim(); 
 var str = rawstr.split(String.fromCharCode(160));
 var result = "rgb(255,255,255)"; var speed = 0;
 try { speed = parseInt(str[1]);} catch(ignore){speed=0;}
 var gust=speed;
 if(rawstr.indexOf("gust")!=-1) {try { gust = parseInt(str[2]);} catch(ignore){gust=speed;}}
 result=windgradient(Math.round((speed+gust)/2.0),gust);
 $(this).css("background",result);
 $(this).text(rawstr.replace("gust",">"));
}


So the colour intensity and hue can independently give some information about how strong and how gusty the wind is, more or less like so (yes I did it on Wolfram Cloud for a quick illustration):



Friday, May 15, 2020

Old Phone/Tablet as an Info Board: Update 2 - The snappy client-side bus board updates

Last time I promised to touch upon what I did on the client side of the bus widget to make it look like this:

In simple terms, I needed to solve the following problem: given the timetable loaded X minutes ago, find the number of minutes remaining until the bus departures now, and based on that, highlight the buses that are convenient to catch (i.e. which depart roughly when I reach the bus stop if I leave soon).

We remember that the timetable, as loaded, returns its output nicely compartmentalized into distinct <span>'s:


so the idea is to do the following at regular intervals:

  • query all #bustime's for departure times;
  • determine the time difference between those times and the current time
  • update the number of minutes in #busreal's
  • re-colour all #bus* according to the number of minutes left, taking into account the time it takes to walk to the bus stop.
The ideal point of insertion from the point of view of the flow control would be the clock() function, which is scheduled to run every second to update the clock; every 10 seconds I ask it to do the following: 

function clock(){
var now = new Date(); var h=now.getHours(); var m=now.getMinutes(); var s = now.getSeconds();
$("#nowclock").text(((h>=10)?h:("0"+h)) + ":" + ((m>=10)?m:("0"+m)) + ":" + ((s>=10)?s:("0"+s)));
if (s % 10 ==0 ) {
var mintogo = 6; // walking distance to stop in minutes
var togo = -1; var nextgo = -1; var num=0; var fnum=-1;
var dom = $("iframe#miway").contents();
while(num<6) {
 var token = "#bustime"+num;
 var nextbus = dom.find(token).text().trim();
 nextbus.replace("now","0 min");
 var am = (nextbus.indexOf('am')>0); var pm = (nextbus.indexOf('pm')>0)
 var idx = nextbus.indexOf(' ');
 if (idx>0) nextbus = nextbus.substr(0,idx);
 var ddot = nextbus.indexOf(":"); if (ddot<0) return;
 var hh = parseInt(nextbus.substr(0,ddot)); var mm = parseInt(nextbus.substr(1+ddot,nextbus.length-ddot));
 if (am && hh==12) hh=0; if (pm && hh<12) hh+=12;
 var hcarry=(h<20 && hh>20); var hhcarry=(h>20 && hh<20);
 if (hcarry) h+=24; if (hhcarry) hh+=24; 
 var diffmin = (mm+hh*60) - (m+1+h*60);
 togo = diffmin - mintogo;
 var thiscol = buscolor(togo);
 var token = "#busnum"+num; if (num==0) token="#bustoken";
 dom.find(token).css("background", thiscol);
 var mintxt = ':'+diffmin+'m';
 if (diffmin<0) mintxt = ':-';
 if (diffmin==0) mintxt = ':now';
 if (num==0) {
   mintxt = '('+diffmin+' min)';
   if (diffmin<0) mintxt = '(--)';
   if (diffmin==0) mintxt = '(now)';
   }
 dom.find("#bustime"+num).css("color", thiscol);
 dom.find("#bustime"+num).text('\xa0'+hh+":"+((mm<10)?"0":"")+mm+'\xa0');
 dom.find("#busreal"+num).css("color", thiscol);
 dom.find("#busreal"+num).text(mintxt);
 if (togo>=0 && nextgo<0) { nextgo=diffmin; fnum=num+1; }
 num++;
 }
dom.find("#realtime").text("leave in " + (nextgo-mintogo) + " min");
}} 


where the auxiliary function buscolor() is simply

function buscolor(togo){
 if (togo<0) return 'rgb(200,180,180)';
 if (togo<=2) return 'rgb(255,200,0)';
 if (togo<=4) return 'rgb(255,0,0)';
 if (togo<=8) return 'rgb(255,64,64)';
 if (togo<=15) return 'rgb(255,100,100)';
 if (togo<60) return 'rgb(255,128,128)';
 if (togo<120) return 'rgb(255,128,255)';
 return 'rgb(255,0,255)' // error
}

That's it. Note the following:

  • The code handles both 12-hour and 24-hour times but the output is forced into 24-hour because it offers smaller footprint.
  • The item number 0 has a different, more verbose output format; the additional #realtime contains a hint when to leave home for the "next suitable" bus.
  • The container that should be called #busnum0 is called #bustoken instead. This is for historical reasons: the system looks for #bustoken as an indicator that the timetable has loaded.

It remains to be seen whether doing this every 10 seconds will prove feasible for the Playbook in terms of "mean time between refreshes / restarts". 

P.S. Turns out I had planned this feature all along, according to my own original post:


Our mileage may vary even further. For example:

  • we may imagine the code to throw out "missed" departures, moving the remaining ones up the queue, and even triggering an extra timetable refresh when there are too few (say <3) left;
  • we may intelligently trigger an extra refresh every time we are nearing the "leave home in 2 minutes situation in order to make sure that the upcoming bus is still on schedule;
  • we may keep track of how much the real-time departure information is changing between refreshes (provided they happen often enough) and detect when delays start to appear or frequently change (this is indicative of unstable traffic, warranting more frequent refreshes)...
...but we aren't building an after-market professional grade departure board here, and "perfect" is a very common enemy of the "good enough". 

Thursday, May 14, 2020

Old Phone/Tablet as an Info Board: Update 1 - The sneaky Mississauga buses

Since I wrote my original series 2 years ago, my custom made info board has proved a (moderately) trusty friend and a time saver, especially on busy mornings. It does need a manual refresh every now and then (roughly once every 1-2 weeks), and the Playbook itself needs a reboot once every couple of months, but this is part of regular maintenance and hey, the thing is still running fine on a year-2011 Playbook and a 2013 DiskStation, which is, in and of itself, a proof that I did a decent job, and an occasion to celebrate - so cheers :)

However, nothing is forever, especially on the Internet. Over time, the board started losing components, which prompted more in-depth maintenance and code revision. So I am writing a series of update posts to list and describe that maintenance.

One of the components that bit the dust lately was the departure board for the buses at our nearby bus stop (see my earlier post on how I did it, I will be referencing it often.). Contrary to my fear that my brute-force string parsing of a JSON in PHP would give me debugging headaches, it actually proved remarkably robust. Something totally different happened: the query for the JSON was simply giving me Error 404. Apparently, the company just scrapped the service and seems to have migrated to a wholly different platform. Well, as I said, nothing on the Internet is forever, so I needed to re-implement the web scraping for the next bus departure times.

I will forgo the description of a 3-hour long troubleshooting spent in Chrome's web inspector feature (mostly because it happened too long ago for me to recover the details of it); suffice it to say that I ended up finding a new request in this format:
https://www.triplinx.ca/en/NextDeparture/NearByNextDeparture?stopId=(6-digit stop ID)
resulting in the following:


Sweet. A simple Inspect shows a clear document structure:


I wasn't inclined to modify my "bake the page directly in PHP" method, but I could now afford to parse the document somewhat more intelligently, like so:

function getElementsByClass(&$parentNode, $tagName, $className) {
    $nodes=array();
    $childNodeList = $parentNode->getElementsByTagName($tagName);
    for ($i = 0; $i < $childNodeList->length; $i++) {
        $temp = $childNodeList->item($i);
        if (stripos($temp->getAttribute('class'), $className) !== false) {
            $nodes[]=$temp;
        }
    }
    return $nodes;
}

$url = "https://www.triplinx.ca/en/NextDeparture/NearByNextDeparture?stopId=$stop";
$code = file_get_contents($url,false,$context); 

$routes=array(); $times=array();$rid=0;
$dom = new DOMDocument();
@$dom->loadHTML($code);
$list_node=$dom->getElementsByTagName("ul")->item(0);
$items=$list_node->getElementsByTagName("li");
for($i=0;$i<$items->length;$i++)
{
  $thisraw = $items->item($i)->nodeValue;
  // set route - we are in the outer LI
  $token1 = "Bus"; $pos1 = strpos($thisraw,$token1)+strlen($token1)+2;
  $token2 = "Show"; $pos2 = strpos($thisraw,$token2)-2;
  $rawroute = substr($thisraw,$pos1,$pos2-$pos1);
  if (strlen($rawroute) <= 5) 
  {$thisroute=$rawroute; }
  else // we are in the inner LI, so get time for the already set route
  {
 $thisattr=0; $thisattr += (strpos($thisraw,"real time")!==FALSE) ? 1 : 0; // real time attr
  $attr[] = $thisattr;    
  $diritem = getElementsByClass($items->item($i),"span","next-departure-label")[0];
  $direction = $diritem->nodeValue;
        $direction = str_replace("towards ","", $direction);
  $routes[] = $thisroute . substr($direction,1,1) . " ";
  $timitem = getElementsByClass($items->item($i),"span","next-departure-duration")[1];
        $thistime = $timitem->nodeValue;
        $thistime = str_replace("<", "",$thistime); // filter < 1 minute:: gone anyways 
        $thistime = str_replace("<", "",$thistime);
        $entity = htmlentities($thistime, null, 'utf-8');
        $thistime = str_replace(" ", " ", $entity); 
        $thistime = html_entity_decode($thistime);
 $times[] = $thistime;
  $rid++;
  }
}

where I borrowed the function getElementsByClass() from StackOverflow; the snippet aimed at getting rid of all &nbsp;'s was likewise borrowed from there.

As a result I was now getting an array of bus routes and bus times in string arrays $routes[] and $times[] respectively. However, I still needed to sort my buses by departure time rather than by route as in the screenshot above; the trick is that some of the times are given as "5 min" while some others are like "2:35 pm".

I got around the problem by determining the "number of minutes till all departures" and storing it in $nummin[], like so

date_default_timezone_set('US/Eastern');
$rawtime = new DateTime();
$rid = (($rid>6)?6:$rid);
for ($j=0; $j<$rid; $j++)
{
  if (strpos($times[$j],"min")!==false)
  {
   $nummin[$j] = intval(str_replace("min","",$times[$j]));
  }
  else
  {
   $temptime = new DateTime($times[$j]);
   $fulltime = getdate($temptime->getTimestamp());
   $temptime->setDate($fulltime['year'],$fulltime['mon'],$fulltime['mday']);
   $nummin[$j] = intval(($temptime->getTimestamp() - $rawtime->getTimestamp())/60);
   if($nummin[$j]<0)$nummin[$j]+=(24*60); // add a day if needed
  } 
}

and then using it to bubble sort all three arrays (6 elements aren't worth doing anything more sophisticated):

for ($i=0; $i<$rid; $i++)
 for ($j=0; $j<$i; $j++)
  if($nummin[$i]<$nummin[$j])
  {
   $temproute = $routes[$i]; $routes[$i] = $routes[$j]; $routes[$j] = $temproute;
   $temptime = $times[$i]; $times[$i] = $times[$j]; $times[$j] = $temptime;
   $tempmin = $nummin[$i]; $nummin[$i] = $nummin[$j]; $nummin[$j] = $tempmin;
  }

For output, we would need the reverse operation, i.e. converting "in 5 minutes" to a valid departure time. The reason is that we can't afford to pull the timetable every single minute, so labels like "in 5 min" would very soon mean "in 5 minutes, as of 3 minutes ago" which isn't very convenient to use. To get around this confusion, I have employed the following trick:

for ($j=0; $j<$rid; $j++)
 {
  if (strpos($times[$j],"min")!==false)
  {
   $live = $times[$j];
   $mins = intval(substr($live,0,strpos($live," min")));
   $newtime = (clone $rawtime);
   $newtime->modify("+{$mins} minutes");
   $bustime = date("H:i",$newtime->getTimeStamp());
   $times[$j] = $bustime . " (" . $live . ")";
  } 
 }
if (strlen($times[0])<4) $times[0]= $timestamp." (now)";

Note the line with $newtime = clone $rawtime. It is very important that $newtime is cloned, otherwise $newtime->modify(...) will modify $rawtime and our code will work, but will produce a wrong timetable! Also note that the last line is a patch to ensure that "<1 min" is captured as "now" regardless of possible parsing errors upstream (spoiler: there are errors upstream).


As an afterthought, as I have both the number of minutes and time for all departures, I decided to output them both, breaking up the elements and giving them distinct IDs for future access. Here's how:
$css= ' style="font-weight: bold; color:rgb(255,128,128);"';
for ($j=0; $j<$rid; $j++)
{
 if (strpos($times[$j],"(")!==false)
 {$times[$j] = str_replace('(','</span><span id="busreal'.$j.'" '.$css.'>(',$times[$j]); }
 else { $times[$j] =    $times[$j].'</span> <span id="busreal'.$j.'" '.$css.'>('.$nummin[$j].' min)';}
 if ($j==0) $css=str_replace(';"','; font-size: 14px;"',$css);
}
. . .
echo '<span id="bustoken" style="background: ',$color,'; color:white;">', " ".$routes[0], '</span>',  '<span id="bustime0" style="font-weight: bold; color:',$color,'">', " ", $times[0], '</span>', '  <span id="realtime" style="color: ',$color,'; ">[...]</span>';
if ($rid > 1)
{echo "<br/>";
 echo '<span style="color:rgb(255,235,235);">', $timestamp, ' </span>';
 for ($j=1; $j<$rid; $j++)
  {echo '<span id="busnum'.$j.'" style="background: rgb(255,128,128); color:white; font-size: 14px;">', " ".$routes[$j], '</span>',  '<span 
   id="bustime',$j,'"  style="font-weight: bold; color: rgb(255,128,128);font-size: 14px;">', " ", $times[$j], '</span>  ';
  };
} 

You can surely notice that the "breaking up" thing was really an afterthought and is not neat code at all. I will probably yell at myself for doing it this ugly when I decide to refactor this in another 2 years. :)


So, here is the final result:


And this is how it looks like in an embedded form:



What's with all the different colours, you ask, what does "leave in..." mean, and why is formatting so different? I bet you guessed it: the 16:31 bus is already too soon to catch, and the 16:33 can be caught just barely, if you leave now and make haste. In my next post, I am going to describe how I achieved this on the client side.

Sunday, May 10, 2020

Computer surgery, DVD player laparoscopy, and even more debugging without a debugger

Pre-prologue: Computer surgery

Long story short: the battery on my old Mac gave up the ghost in a relatively ugly way and I had to put a bit of effort for that ghost not to become a fire spirit. In one picture worth a thousand words, this is what happened:


Which meant that the only computer with a DVD drive was severely crippled, and I was getting fed up with hooking a computer to the TV whenever there was a "please Daddy may we watch..." So I decided to buy a simple standalone DVD/Blu-ray player.

Prologue: DVD players and world travellers

The trouble was, I have lived on 2 continents in 5 different countries (at one time, simultaneously), and while doing so, a hodge-podge mini collection of DVDs from at least 3 different regions materialized on my living room shelf. While I fully understand the region mechanism so that a new blockbuster may hit the box offices at different times in different parts of the world, this protection kind of makes zero sense for titles (or worse, indies) released around the world long ago. Of course there's VLC and dvdlibcss but hey, this whole thing was done to spare the hassle of having to connect a computer to the TV to play a disc.

In other words, I needed a region freeing or region switching mechanism for my player. I researched some forums before buying, and it seemed that at least *some* players of this kind could play discs from around the world; also there were already "multi-region" players of this kind freely available for sale on Amazon and third-party websites (presumably with modified firmware), so I thought that it might be worth to try my luck as well.

Expectedly but disappointingly, the unit was not multi-region out of the box. None of the methods "off the internet" worked either. None of the "magic" remote control codes. Some more in-depth sites suggested burning a special CD-R to enter the "advanced settings mode", but this did not work for me at all (and people were writing in the forum that this CD-R method was good for any player except North American, which was my case). The forums even mentioned that the only way was to physically get into the player by soldering some wires to its serial interface (yikes: I am not that big a fan of soldering  and my few circuits even tend to work better on a breadboard than in soldered form), so it seemed that I had to use the computer route for anything outside North America...

Entry route and initial troubleshooting

...or not? Further search got me to a page mentioning what is called "the Pandora exploit". Here's the essence of it:

$ cat /mnt/rootfs_normal/usr/local/bin/pandora/pandora.sh

#!/bin/sh
#

(...)

if [ -e /mnt/sda1/PandoraApp ]; then
    (...)  
    /mnt/sda1/PandoraApp -qws -display directfb
elif [ -e /mnt/sdb1/PandoraApp ]; then
    (...)
    /mnt/sdb1/PandoraApp -qws -display directfb
else
    (...)
    /usr/local/bin/pandora/PandoraApp -qws -display directfb 
fi
What this means is that when the player is asked to launch the Pandora app (I had no idea what this was, at the time), the player looks for PandoraApp executable in the following directories, in that order: /mnt/sda1/, /mnt/sdb1/, and finally /usr/local/bin/. Which is to say that if you put a shell script called PandoraApp in the root folder of a USB stick, plug it into your player, and launch Pandora, it will execute that script instead of the app.

This was worth trying out. Pandora isn't available in Canada so there was no menu item to call it, but as soon as I changed the player's country to US, that menu item happily appeared in the Premium menu of the player (which holds all the "streaming app" capabilities). Provided the player was connected to the Internet (i.e. had wi-fi set up and running), I was able to get to a nice splash screen where I was happily informed that, well, "Pandora isn't available in your country".

The exploit page mentioned setting up a reverse shell, but this seemed to me too complicated in a home network situation full of dynamic IPs and firewalls. Instead, what I put on the SD card was

ls > /mnt/sda1/check_a.txt; ls > /mnt/sdb1/check_b.txt;

This time, running Pandora resulted in the USB stick LED blinking once and then the player froze with a black screen, forcing me to do a hard reboot. The USB stick nicely contained the file check_a.txt, holding root directory listing, and confirming that the drive was indeed mounting under /mnt/sda1.

Next time, what I put on the drive was:

ls -alR > /mnt/sda1/dirlist ;
/usr/local/bin/pandora/PandoraApp -qws -display directfb;

The last line ensured that the Pandora app was launched after what I instructed the player to do, allowing me to return to the main menu and avoiding the need for a hard reset. After a few unsuccessful tries, I got a nice full listing of the player's file system.

The reason for "a few tries" turned out to be that I was accidentally using a defective USB drive which, due to its old age or/and compatibility issues, had a corrupted file system that took the player forever to list. So, kind of "bad luck". However, I had a compensating "good luck" that the first stick I tried ended up working - the newer ones did not, even the first script wasn't being executed at all, presumably because newer drives were mounted under some different path. Had I started with a non-working stick, I might well have thought that the exploit didn't work anymore, most likely got patched in the newer firmware, and would have given up any further tries... the lesson of this was not to give up and "throw it until it sticks" a few more times.


Analysis and solution

Okay, I have the full listing of the player's system, now what? 

I spent some time carefully studying the listing, and after a few red herrings stumbled onto this:

./mnt/ubi_boot/var/local/acfg:
total 108
drwxr-xr-x 2 root root    232 Jun  1  2014 .
drwxr-xr-x 6 root root    424 Jan  1  1970 ..
-rw-r----- 1 root root 107677 Jun  1  2014 config_file.txt

So copying the file to the USB stick for investigation by doing

cp -f -v /mnt/ubi_boot/var/local/acfg/config_file.txt /mnt/sda1/ ;
/usr/local/bin/pandora/PandoraApp -qws -display directfb;

and opening the copied file in a hex viewer such as hiew immediately gave me this;

 Promising. Let us zoom in for a closer look:

Looking at the byte signature after each option, it seemed like the first non-zero byte marked something like the beginning of the data section (always 01); the second marked the length of the data (01 for one byte, 02 for two bytes etc.), and finally the last byte stored the relevant information (e.g. FF at 0x50C stands for 255 years Blu-ray age restriction).

At this point many would say "Got it!" and rush to change the bytes at 0x3E3 and maybe 0x408 from 01 to 00... well, not so fast. Remember the exploit only works when you have a bootable player! The file is not encrypted, that much we can see, but what if there is a checksum somewhere that needs to match with any edit of the file? A mismatch may, at best, trigger a factory reset, and at worst, brick the player. So I changed an option from the Settings screen, for example the above mentioned age restriction to, like, 128 years. Re-dump the file, and...

>fc /b config_file.txt config_file1.txt

Comparing files config_file1.txt and config_file.txt  0000050C: 80 FF

OK, we are safe at least on this front. 
For editing, you could either buy the full version of Hiew, or use Recordman which is from the same author. Here goes, for example, 


The modification of ..._BDAGE is needed for us to be able to visually confirm that the new file has taken effect; the modification of ..._REGIONFREE is just for good measure, I have no idea what it does. Store it as config_file_fix.txt, and do the following...


cp -f -v /mnt/ubi_boot/var/local/acfg/config_file.txt /mnt/sda1/config_file_pre.txt >/mnt/sda1/report.txt ;
cp -f -v /mnt/sda1/config_file_fix.txt /mnt/ubi_boot/var/local/acfg/config_file.txt >>/mnt/sda1/report.txt ;
cp -f -v /mnt/ubi_boot/var/local/acfg/config_file.txt /mnt/sda1/config_file_post.txt >>/mnt/sda1/report.txt ;
/usr/local/bin/pandora/PandoraApp -qws -display directfb;

... to find that it does not work, and the edits revert back upon player restart.

Well, after some more probing, reading, dumping, and more reading up on UBI, MTD and NAND flash, it occurred to me that write-behind cache might have been playing tricks on me; after all, the system totally doesn't expect a file modification at this point. So I tried adding /bin/sync after copying the file, hard-reset the player some time after the script executed, and it worked; looking at settings confirmed that the BD age restriction went down from 255 to 254 years.

I have to give you a "proceed at your own risk" warning here, for two unrelated reasons. First off, I have no idea how robust the player software is against false moves such as accidentally erasing yor config file entirely or messing up in any other way; having actually done sudo chmod -r / on my first Mac (followed by sudo chmod +r / ... what does it mean "sudo not found"?!?) I can say that it is entirely possible to brick your player this way, and there will be no way back unless you really want to (and can) do the soldering thing. The other reason is that media industry does not pat us on the back for reverse engineering their code because 9 times out of 10 people try this with some form of piracy in mind. Well that wasn't my motive whatsoever (since I already had quite a few ways to play all of my entirely legal discs), and it should not be your motive either. So, don't try this at home unless you really know what you are doing, and if you do, proceed at your own risk and treat it like I treated it -- as a troubleshooting challenge (which I love) along with a comfort improving thing (which I value).

Monday, January 27, 2020

3D printer: First impressions and a DIY fume extractor

Backstory: 3D printer first impressions (pun intended)

I need to do quite a lot of round-the-house repairs, and have always fancied a 3D printer to be able to churn out custom plastic parts for replacements and custom projects. Well, as a recent birthday gift from my wife, I got one - and it proved to be one of the most versatile and good-value-for-money model on the market, thanks to a very wide community support. The learning curve was a bit steep at times but it turned out I got extremely lucky and managed to get my printer going in relatively no time without causing it major damage -- although I came quite dangerously close to it more than once, I even managed to master bed leveling without major scratches on said bed :).

An accidental find: it is more reliable to not disable steppers for bed leveling following auto homing (moving X/Y using the control panel instead). It's more cumbersome but maintaining holding current on the Z-motor ensures that the nozzle remains exactly at the same height at the beginning of the print as it was during leveling. An even better option would be to only disable X/Y steppers while holding the Z-stepper. I may end up programming some kind of manual bed leveling wizard into the firmware at some later point.

Another accidental find: it is better to not bundle the Bowden tube together with the hotend cables. It came bundled and I undid it by pure accident, then connected it, quite absent-mindedly, to the extruder (barely realizing that this is actually where the filament is going through), and only then realized that I cannot easily undo the tube connection to re-route it through the cable ties. So I just left it like that, and discovered that it makes a more natural filament path and puts less strain on the print head.


First impressions (pun intended) came out quite acceptably good, at least for my purposes (I wasn't going to set any world records in print quality or detail level or display beauty or anything line that - I simply want my parts to come out functional). Since I wanted to keep the printer busy for some time, I was mainly printing some more common accessories for the printer itself. (Yes, this kind of a 3D printer is very modular, like Lego for adults -- you can swap out and upgrade pretty much any part of it, and many upgrades come in the form of "download a model and print it yourself", so I outfitted mine with a filament guide and cleanerextruder knob, fan cover, tool holder (a LONG almost-overnight project), etc.)

Then came the first "real-life" application: basically a custom-sized brick of plastic with a screw hole inside to serve as a spacer for mounting a hanging file frame inside a bedroom cabinet (as part of document organization project). This was an extremely simple model that came out as desired on the second try.

Then came the second "real-life" application: a holder for a Tile Sticker that would fit on the strap of a Babble Belt baby monitor. Thanks to Pavel from Dots and Brackets who introduced me to the genius idea of OpenSCAD (basically a programming language for 3D modeling), the total time from concept to printout was less that 3 hours and fit nearly perfectly.

I was ready to start conquering the 3D printing world but...


Fumes issue and extractor hood idea

...but the layout of our house left no place for the 3D printer installation other than in the basement room that doubled as gym / playroom, as well as contained one of the main HVAC returns. This basically meant that I couldn't print with anything "smelly" like ABS or, well, most materials other than PLA. Even with PLA (biodegradable / biocompatible as they claim it is) I would be a bit worried about the amount of fumes and fine particles that my prints might be generating. Especially with kids playing in the same room and the HVAC system happily blowing the basement air throughout the whole house.

So in the long run, I would totally need some kind of exhaust to vent all those pesky fumes the hell out of my house. I noticed that I had an existing 4-inch dryer exhaust in the workshop room (a small 1x2 meter closet in the far corner of the basement). Immediately I thought that I could gear up something like this (additionally outfitting the workshop itself with a functional exhaust which isn't a bad idea either). After googling around for what components I can buy cheaply I came up with the following:


From right to left: L, louver; G, grille (3D printed); ED, existing duct; BD, backdraft damper; R, 6-to-4 inch reducer; Y, 6x4x4 inch Y-connector; BG, blast gate; FD, flexible duct; V, adjustable round vent

Buyer's list

So here was my buyer's list (all prices are in Canadian dollars). Note that I got all items off Amazon with Prime delivery, which may have increased the prices compared to Ebay or Aliexpress, but I wanted to get the work started ASAP, not in a few months:
  • The hood: a mini dust hood ($17), as well as any suitable sheet to extend the hood to dimensions - I used an ABS sheet I happened to have lying around ($13) but you can use literally anything you have on hand, including cardboard from an Amazon shipping box. (Well, not quite, as it turns out. The sheet needs to be strong enough to support the curtain weight, yer lightweight enough to not bring down your mount. My ABS sheet did flex a bit so may need to replace it or to print some reinforcement beams to support it.)
  • The fan: I used a 6-inch inline 250 CFM lying around ($35 a few years ago) but you could get a 4-inch 100 CFM inline fan for about $24. 
  • The ductwork: blast gate ($9), backdraft damper ($13), some 4-in flexible duct ($5 for 5ft), clamps ($2 for 2), and connectors ($3.5 each). Since I used a 6-in fan, I also needed a 6-to-4 in reducer ($14), and because I designed the exhaust to also serve my workshop, I added on a 6x4x4 Y-connector ($9) and an adjustable 4-in round vent register ($13). If you decide to merge your exhaust with your existing dryer ductwork, you will instead something like a 4x4x4 T-connector ($8.5) and another blast gate. You will also need to make sure your backdraft damper is between your fan and the insertion point to your dryer ductwork, and would need to have at least some kind of lint trap or screen, compensated for by a stronger fan, so that your dryer will not rain lint onto your 3D printer. Ideally, you'd want some sensor lock-out preventing your dryer from running when the blast gate to your printer is opened, otherwise your filament won't like the warm moist air coming from your dryer. 
  • The mount. I wanted it to be flexible so that I could raise my exhaust hood when not in use, therefore this part proved the most problematic. A gooseneck phone holder didn't work (way too limp); an adjustable screen arm wasn't adequate either (too heavy, not enough articulation in the needed directions, hot tight enough in other directions). But microphone mounting equipment proved quite fine just barely adequate (I ended up getting  a boom arm, a flange, a short gooseneck and a set of nuts for a total of $41, roughly the price of a monitor arm.)
  • The curtains. I reused a clear plastic table covering sheet outfitting it with an abundance of Velcro strips ($10 for 5m). More on this later.
Some of the parts for the project (a few of them were returned and upgraded).


Implementation

0. Electrical wiring for the fan

This step was optional but I felt the need to do it because I had no readily available wall outlets near the fan, and did not want to mess with extension cords. So I ended up rummaging my electrical supplies box for a standard 2-outlet receptacle, a Decora switch, two 2x4 electrical boxes, two outlet covers, several oddball clamp connectors, and a bit of 14/3 cable to put in a switch-controlled plug for the fan(s). 

I decided to branch the circuit off the ceiling light as this was the closest point (behind a GFCI as are all my workshop circuits which is a bonus). The inconvenience that at least some lighting needs to be on in the workshop for the fan to run was easily offset by the ease of wiring compared to running the wire all the way to the workshop light switch. If all else fails, and if I need to run the exhaust overnight with someone sleeping in the basement next to the workshop and the 3D printer (odd as it is that the workshop light would bother that individual while the running 3D printer itself would not), I can simply temporarily unscrew the bulb.

1. Mounting the hood above the 3D printer

My idea was to make the mount without drilling any holes in the visible part of the cabinet that doubled as the printer stand. So I used some hobby 2x2 wooden stock and steel brackets from Home Depot to improvise a stand on top of the cabinet. Here goes:

[[[T, top surface of the IKEA cabinet (birch veneered particle board); PL, back of the IKEA cabinet (plywood thin MDF board); P, 2x2 poplar; O, 2x2 oak. Steel brackets and screws are self-explanatory.]]]


The resulting stand wasn't 100% solid in all directions but served my purposes. (You might as well use the nearest wall stud, though.) 

The remaining steps were to attach the microphone mount to the stand, flange then arm then gooseneck. I ended up disassembling the boom arm to be able to screw it onto the flange to avoid moving the cabinet (it is secured to the wall to avoid tipping). I fitted the hood directly to the gooseneck by drilling a carefully measured 5/8 inch hole and tightening the hood wall between two mic nuts. I may be in need of reinforcing the gooseneck in the future if it becomes limp over time.

2. Mounting the fan motor 

This step was rather straightforward. I noticed that the motor fits pretty tightly with the reducer on one side and the Y-connector on the other side (the latter needed some slight Dremel grinding to make a groove to accommodate a rivet head on the fan). The reducer, in turn, fit nicely into the backdraft damper, which could be connected to the inside of the duct with the aid of a 4-inch hose connector.

The outside of the duct needed to be replaced with a louvered vent which I had lying around from some previous projects. However I had no pest-proofing grille, so I ended up printing one:


Oddly enough, some 1/3 of the grille beams had no brim around them, and seemingly started printing in thin air, but the grille ended up acceptably good. Indeed PLA can be very forgiving. Next time I'll be reprinting those in ABS or PETG (and I might need it because PLA may not be the best material to leave exposed to the elements) I'll probably use a raft.

An unpleasant surprise came up when it turned out that the airflow was barely enough to open the damper, limiting the exhaust functionality quite significantly. So I quickly outfitted the damper with a quick-and-very-dirty manual open/close actuator threading a piece of string through three tiny holes and tying two knots, like so:



The beauty of it is that it is simple enough to do in 20 minutes, versatile enough to allow for 3 modes of operation (as seen above), is easily outfitted with visual aids, can be secured in either open or closed position using simple loop-and-nail "mechanisms", and even lends itself to automatic actuation upgrade using some kind of solenoid electromagnet (I may do such an upgrade in the future by rigging a solenoid connected to a current sensing switch from within the switch box, as I do have a few current sensing transformers lying around.)


3. Installing the blast gate and assembling

Probably the most invasive part was installing the blast gate through the drywall. I decided to use a shuttered gate (rather than making do with something like routing a duct directly through the wall) for several reasons:

  1. I wanted to be able to shut off the exhaust when not in use, so that I don't get extra cold air seeping onto my 3D printer (and into my basement). Also, to increase the efficiency of the workshop exhaust, I needed to be able to shut off the 3D printer branch. 
  2. I wanted to protect the drywall from chipping near the hole. Drywall is brittle, so anything that could wiggle inside the hole would gradually cause it to chip and crack. Not only would it damage the wall, but also the resulting dust would not add to the quality of the 3D printing or the surrounding air.
  3. I wanted the connection to look as aesthetically pleasing as I could make it, so that if I decide to discontinue the use of the exhaust or the 3D printer or both, I am left with a nice looking round opening with a door that can be easily closed (rather than an ugly looking protruding piece of aluminum duct, or worse, a gaping 4-inch hole). 

Cutting the hole ended up surprisingly easy with a very dull 4-inch hole saw and a handheld power drill. (Actually, the "very dull" did more good than harm. Had the saw been sharp, it would start catching in the drywall, tearing chunks off it (making a lousy looking hole), or worse, tearing the drill away from my grip and sending it flying all over the workshop. Although I might have avoided it by running the drill backwards.) I took time to carefully tape a few garbage bags with masking tape around the hole to minimize dust generation, so the hole ended up very nice. (My wife didn't welcome the idea regardless.)

Once the hole was there, it just took some silicone caulk to glue the blast gate in place
(I also reinforced the shutter stop of the gate by an M4 machine screw to prevent the door from sliding completely out if pulled too vigorously). What remained was simply attaching some 4-inch flex ducts with worm-gear clamps, sealing some joints with (insulating) duct tape, and the ductwork part was ready.

4. Making and attaching the enclosure walls

To make the enclosure itself, I re-purposed a decommissioned used clear table cloth from the dollar store, and made 4 curtains the following way:

  1. Cut each curtain to size Wx2H, fold to make a double-layered curtain .
  2. Use an impulse sealer to fuse the layers of the curtain at regular intervals. (Yes this was a good excuse to buy an impulse sealer.)
  3. Place a small weight (I used some Home Depot brass rods I had around for 4 years but never used) into the fold; seal sideways so that the weight does not slide out.
  4. Attach curtains to the hood using strips of Velcro tape.
  5. Put some more Velcro tape strips on the sides (to close the enclosure) as well as in the middle (to hold the curtains rolled fully or partially up, if needed). I'll have to figure out whether to make them more continuous (to make the enclosure more airtight on the sides) or more in pieces (to make the curtain easier to roll up).

Final result

Well, a couple of pictures are worth a thousand words (This has been a long read anyways.)

Workshop side (before and after). The yellow string operates the backdraft shutter.

Basement side (also showing the printer in actual operation)

Outlook


  • The gooseneck, as well as the ABS extender sheet, are just barely adequate for the weight of the structure. The hood looks a bit flimsy and will eventually shift under use, especially when (un)rolling the curtains. I will be reinforcing the gooseneck, or maybe even foregoing it entirely, opting for another articulated arm instead.  
  • The curtains design just sucks in terms of fireproofing - any flame and it's curtains for the curtains, if you excuse the pun - as well as for the printer and the basement. I may, at some point in the future, upgrade the enclosure to some flame retardant (and also more easily rollable) version. So far I am investing in a smoke detector directly above the printer. And an air quality monitor to boot. 
  • On the flip side, I like the transparent enclosure and easy access to the filament spool. (Most people would mount the spool outside of the enclosure anyway but I was too lazy to do so.)

Saturday, January 11, 2020

Tamper detection flip-flop circuit and my first soldering experience

Preface

In any modest-sized family home, locks tend to accumulate - door locks, shed locks, mailbox locks, cabinet locks, closet locks, lockbox locks... Locks breed keys, keys breed frustration when they get misplaced - and they always get misplaced when they are used a few times a year. And even though I did decide to learn lock picking (sic!) in the meantime, to have a last-resort method to open those pesky locks, and even bought a training set (double sic!!),  I never had time to practice it to the point of usability.

An obvious solution is a key cabinet. But, similar to the jewellery drawer lock I wrote about earlier, there is an inconvenience of having to lock the cabinet whenever you leave your house to cleaners or contractors and want to feel peace of mind. 

Of course you could lock it and keep its key on your key chain. But this was not fail safe (what if your main keys get misplaced, or worse, stolen?) Instead, inspired by the KGB spy stories, where agents would wrap a hair around their purse clasp to see if it's been opened (presumably by an opposing MI6 agent), I wanted to design an electronic detector that would alert me if the cabinet was opened in my absence.


Idea

The idea that immediately comes to mind is a simple contactor, i.e. en electromagnetic relay with its coil wired in series with its normally open contacts, like so:



The operation is pretty simple: the relay K1 is initially de-energized because its contacts are normally open; as soon as the (discreetly located) Start button is pushed, contacts are bypassed, the relay is energized and maintains the contacts closed. At that point the circuit is "armed" (signaled by the LED illuminating as shown above; R1 may be needed to limit the LED current). This goes on indefinitely until the circuit is broken (say by a reed switch attached to the door of the key cabinet, and I had a few of them lying around). The LED goes out, signalling that the cabinet was opened. The diode D1 is the flyback diode, especially important to protect the reed switch from arcing damage.

The drawbacks of this set-up are too obvious. The relay is an overkill to power a single LED (the relay coil consumes about 10 times more power). In addition, there is no way of telling if the LED went out because someone opened the cabinet or because the battery went out (and powering it from a wall adapter is a non-starter because it would give a false alarm for every tiniest power outage.) 

To solve the first drawback, I remembered I had an optocoupler lying around (the white 6-pin chip from this kit), which can work like a relay but for a fraction of power. To solve the second drawback I remembered that I had also ordered some MOSFETs for an earlier garage door opener mod project and ended up using bipolar transistors instead. So the resulting circuit was something like this (the link opens in a live circuit simulator) :



Again the operating principle is pretty simple. The optocopupler Q1 takes on the role of the relay K1; the resistor R1 in the previous diagram is split between R1 and R2 here, creating a voltage divider is such a way that when current flows through Q1, the gate of the MOSFET Q2 has just the right potential to close Q2. When Q1 is de-energized, Q2 becomes opened and illuminates the alarm LED through the resistor R3. 


Implementation and breadboard

When I needed to spend a few hours at a car dealership waiting for my car to be serviced, I took along a few parts, a multimeter and a breadboard and tried to implement my circuit (funny as it looked in the dealership lounge). After a few trials I ended up with this (again please feel free to play in the simulator):



The only modification, aside from tuning the values of the resistors to suit my actual optocoupler and MOSFET, came from the fact that my optocoupler had a separate base pin for the phototransistor, and leaving it floating meant that it could get energized at random upon power-up (defeating all the purpose of the circuit). So I had to introduce the D1-R4 circuit to pull the base pin down just so that Q1 always started with closed phototransistor (in the simulator, it looks detached because the optocoupler there is a generic part having no base lead).  

In the breadboard, the circuit looked like this (the red LED is actually D1 - I did not think I would need an ordinary diode, so I did not bring one to the shop and had to use a LED instead.):





Soldering and final installation

Some 6 months later (yes, because small kids);I found a bit of more time to attempt to actually solder it together on a protoboard PCB. I had never soldered anything serious before, so I made a few bad mistakes along the way:
  1. I learned that you cannot connect two adjacent pads with just a drop of solder - needed to use a tiny bit of wire, or strip a component lead leaving 1-2 mm slack and bending it to extend to an adjacent pad.
  2. I learned that it is not possible to melt more than one hole simultaneously unless you had a very specialized tip and super stable hands. So pre-filling the holes for the MOSFET (or worse, the optocoupler) was a disaster and I deeded to clean out the holes again using a desoldering pump (good I had a very basic one included with the soldering kit); a few times even this wasn't working and I had to resort to a Dremel with a very tiny drill bit. All in all, a third hand station I bought a few years before proved very useful here (finally).
  3. My decision to place components to both sides of the PCB (LEDs and the button on one side, the rest on the other) was a mistake. It would have been cleaner and more compact with everything on the same side. 
Surprisingly, it was much easier for me to get a device working on the breadboard than in the soldered form. It proved quite hard to ensure consistently good soldering quality. I spent a few oddball hours during two days tracing and correcting bad solder points, going through some moments of despair and almost wanting to get back to the breadboard. 

But patience and perseverance won (and, oddly but fortunately, the components endured my multiple attempts). Here goes (note that D1 is now a proper diode :) :



The remaining step was to install it in some kind of case. I did not have anything fancy like a 3D printer so I used a pre-bought plastic box to put the board inside using good old-fashioned M3 screws. Unfortunately, the batteries did not fit on the inside (they would if I had not made the mistake #3 above). So outside they went, on the back of the box. The final result looks like this:




The Start button is hidden behind the fourth screw (the one beside the LEDs). It is shorter than the remaining three screws that hold the board in place. To arm, the screw needs to be removed, and the button can then be pressed through its hole.

In operation, it looks like this:



Bonus: The current consumption of the circuit is around 2 mA (two milliamps), in either state, and most of it is the LEDs. Which means that four AAA batteries would power it through months on end; if this proves not enough, I'll put four D batteries and will probably only need to replace them once every few years.

Friday, December 13, 2019

Heartbeat/watchdog service in MS Excel

We have already covered automating Excel tasks at work -- and indeed it can be a life saver (or rather, work-life balance saver) not to have to be present at work every single day at time XX:XX no matter what only to have to push a few buttons.

However, there is an inconvenience that no automation is 100% fool-proof, especially in a corporate environment. The automation computer may be restarted (by your colleagues or your IT department who decided to push some security updates). It may lose power (because the cleaning personnel got a bit too vigorous with their brooms). It may crash, or freeze, or have a hardware failure. It is particularly nasty is the automation computer is a shared terminal that only gets used once in a while, so a user log-off due to restart may not be immediately detected. And of course once the user is logged off, no code nothing written by that user can be invoked any more, and needless to say that admin access to all computers in the corporate is strictly verboten.

One way of getting around the inconvenience would be to implement a "heartbeat" or "watchdog" service that would periodically query the availability of your automation machine and "phone home" if it goes offline.

The general idea is as follows:
  • The "server" is an Excel workbook that does nothing and resides in the user's private folder on the shared drive (most corporate environments have that), so that it's accessible from any computer in the office but only by the particular user. This worksheet contains no code, but is configured to open at startup on the automation machine.
  • The "client" is another Excel workbook that runs on the user's regular desktop (which is assumed to be operational). 
  • The client polls the server by trying to open the server workbook (assuming that if it's locked for editing, it's open on the automation machine so the latter is operational). 
  • Should the server workbook become unlocked, this means that the automation machine has logged the user off and automation won't run. The client then informs the user by sending an email. 
To determine whether a file is open on the automation machine, I use the following macro (adapted from this idea):


Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.
    ' Check to see which error occurred. Open for 
    IsFileOpen = (errnum = 70)
End Function

The periodic polling code may look something like this:



Sub Heartbeat()
 Dim monitor As String
 Dim span, tick, fail, restart As Integer
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value: fail = .Range("numfail").Value: restart = .Range("autoreset").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    .Range("retick").Value = 0
    Call LogMessage("INFO", "Heartbeat server detected")
  Else
    .Range("tick").Value = .Range("tick").Value + 1
    Call LogMessage("WARN", "Heartbeat server NOT detected")
  End If
  If .Range("tick").Value < fail Then
     Application.OnTime Now + span / 86400, "Heartbeat"
  Else
     Call LogMessage("FAIL", "Heartbeat server has failed, REPORTING FAILURE")
     Call SendEmail(.Range("ReportMail"))
     Application.OnTime Now + restart / 86400, "RestartHeartbeat"
  End If
 End With
End Sub

The idea is that the macro will restart itself using Application.OnTime if all goes well, until the server is not detected several times in a row (to guard against false positives due to intermittent network failures). In that case, a report is set via Outlook (adapted from here and possibly here) using this function:


Sub SendEmail(params As Range)
    Dim OutApp As Object, OutMail As Object, strbody As String, toaddr As String, ccaddr As String, subj As String
    strbody = "": toaddr = "": ccaddr = "": subj = "":
    Dim here As Range
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set here = params(1, 1)
    While Len(here.Value) > 0
       If UCase(Left(Trim(here.Value), 2)) = "TO" Then toaddr = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "CC" Then ccaddr = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "SU" Then subj = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 2)) = "BO" Then strbody = here.Offset(0, 1).Value
       If UCase(Left(Trim(here.Value), 1)) = ":" Then strbody = strbody & vbNewLine & here.Offset(0, 1).Value
    Set here = here.Offset(1, 0)
    Wend
    On Error Resume Next
    With OutMail
        .to = toaddr
        .CC = ccaddr
        .BCC = ""
        .Subject = subj
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing: Set OutApp = Nothing
End Sub

Note that the parameters for the email are conveniently stored on the spreadsheet for easy editing, so that only the range pointing to the parameter block needs to be passed. The email can then be easily defined using something like this:


After the failure report is sent, a slightly different macro is invoked:


Sub ReStartHeartbeat()
 Dim monitor As String, span As Integer, autoreset As Integer
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value
  autoreset = .Range("autoreset").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    .Range("retick").Value = 0
    Call LogMessage("OK", "Heartbeat server restarted, monitoring resumed")
    Call SendEmail(.Range("ReSetupMail"))
    Application.OnTime Now + span / 86400, "Heartbeat"
  Else
    .Range("retick").Value = .Range("retick").Value + 1
    Call LogMessage("FAIL", "Heartbeat server NOT restarted, keeping on trying")
    Call SendEmail(.Range("ReSetupErrorMail"))
    Application.OnTime Now + autoreset / 86400, "ReStartHeartbeat"
  End If
 End With
End Sub

Here the idea is to keep reminding the user that the server is still down, and getting  back on track once the user has successfully corrected the problem.

The entire process is set into motion by two macros:


Sub StartHeartbeat()
 Dim monitor As String, span As Integer
 Call LogMessage("INFO", "Service started")
 With ThisWorkbook.Worksheets("Control")
  .Calculate
  monitor = .Range("monitor").Value
  span = .Range("span").Value
  If IsFileOpen(monitor) Then
    .Range("tick").Value = 0
    Call LogMessage("OK", "Heartbeat server detected, monitoring started")
    Call SendEmail(.Range("SetupMail"))
    Application.OnTime Now + span / 86400, "Heartbeat"
  Else
    Call LogMessage("WARN", "Service not started, heartbeat server not detected")
    Call SendEmail(.Range("SetupErrorMail"))
  End If
 End With
End Sub

Private Sub Workbook_Open() ' Put this in the Workbook code rather than Module1
 If ThisWorkbook.ReadOnly Then Exit Sub ' DO NOT execute if read only OR
 If Workbooks.Count > 1 Then
  Call LogMessage("WARN", "Started in non-clean session, entering setup mode. Service NOT starting. For production, run in a CLEAN session.")
  ThisWorkbook.Save
  Exit Sub ' ONLY execute in clean, dedicated session
 End If
 ThisWorkbook.Worksheets("Control").Calculate
 Application.OnTime Now + TimeValue("00:00:05"), "StartHeartbeat"
End Sub

The safeguards in place make sure that opening the client for debugging or viewing (or by accident) do not start spurious monitoring processes (.OnTime is nasty, once set it will persist untill that particular Excel session is ended, even after the workbook containing the .OnTime was closed). So the client only starts if the file is only opened in a clean, dedicated Excel session.

Finally, an auxiliary subroutine, purely aesthetic, is used to log the monitoring actions. Here goes:


Sub LogMessage(code As String, msg As String)
 Dim here As Range, c As Integer, level As Integer
 level = 255
 If UCase(Left(Trim(code), 3)) = "INF" Then level = 10
 If UCase(Left(Trim(code), 2)) = "OK" Then level = 5
 If UCase(Left(Trim(code), 3)) = "WAR" Then level = 2
 If UCase(Left(Trim(code), 3)) = "FAI" Then level = 1
 If UCase(Left(Trim(code), 3)) = "ERR" Then level = 0
 
 For c = 1 To 2
  Set here = ThisWorkbook.Worksheets(IIf(c = 1, "Log", "Errors")).Range("A1")
  If c = 2 And level >= 2 Then Exit For
  While Len(here.Value) > 0
   Set here = here.Offset(1, 0)
  Wend
  With ThisWorkbook.Worksheets("Control")
   .Calculate
   here.Value = IIf(Len(code) > 0, code, "___")
   here.Offset(0, 1).Value = Now
   here.Offset(0, 2).Value = .Range("monitor").Value
   here.Offset(0, 3).Value = .Range("span").Value
   here.Offset(0, 4).Value = .Range("numfail").Value
   here.Offset(0, 5).Value = .Range("tick").Value
   here.Offset(0, 6).Value = .Range("autoreset").Value
   here.Offset(0, 7).Value = .Range("retick").Value
   here.Offset(0, 8).Value = msg
   Set here = ThisWorkbook.Worksheets(IIf(c = 1, "Log", "Errors")).Range(here, here.Offset(0, 8))
   Select Case level
   Case 10
    here.Font.Color = RGB(200, 200, 200)
   Case 5
    here.Font.Color = RGB(0, 128, 0)
   Case 2
    here.Font.Color = RGB(255, 128, 0)
   Case 1
    here.Font.Color = RGB(128, 0, 0)
   Case 0
    here.Font.Color = RGB(255, 0, 0)
   Case Else
    here.Font.Color = RGB(255, 0, 255)
   End Select
  End With
  If c = 2 Then ThisWorkbook.Save ' only save on error
 Next c
End Sub