Saturday, April 7, 2018

So, I Went to a Couple of Garage Sales ...

Driving down the road this fine Saturday in the desert I saw a sign for a garage sale. I don't normally go to these because I haven't had much luck finding anything of interest, but a couple of weeks ago I stumbled on a really cool clock (more below) and thought, "what the heck?"

Sitting on a table being ignored by the other customers looking for the perfect piece of glassware, child's clothing, big crescent wrench or first printing Gutenberg Bible was a 16 port gigabit TP-Link Ethernet switch. Trying to hide my excitement I asked the owner, "How much?"  "How about five bucks?"

Here it is:

Brand new, in the box, ready to go on the shelf where all the little computers are.

I've been thinking about getting one to support all the little computers that seem to be breeding over there. Right now I'm down to the last port on the switch that handles them and the next one would require this or something like it. Five Bucks?! This particular one is $76 on Amazon right now. Score!

I like to have anything I can connected hardwire into the house ethernet because I don't have to worry about how far away the nearest wireless access point is. This addition to the 'little computer shelf' will help out a lot.

No, I didn't ask them where they got it ...

The clock I mentioned above was an impulse purchase I made a couple of weeks ago at an estate sale. Estate sales are sad (at least to me) because someone passed away and a company is liquidating their belongings. I always get a little stuffed up when I wander through them thinking about the pleasure the items gave their owner, and I usually pick something out that probably had some meaning to them so I can help a little bit in keeping a tiny piece of history around. OK, it doesn't fit my image, and I'll never say that out loud.

I saw this mantle clock that had had a hard time of it, the poor thing was stuffed on a shelf and was brush painted white. Sad little fellow kind of whimpered, "Take me home." So I did:

The white paint had brush marks in it and the brass was tarnished. There were a couple of chips here and there, but they had the key and it ticked. I had a new old clock.

Got the thing home and started tracing the history of that model and it's roughly a hundred years old. Since it still ticked, it hasn't seen much actual use as a clock because the bearings would have long since worn out. I wound it up and rolled the minute hand to the half hour and it chimed. So, I moved again to the hour and it announced the hour nicely. So far, so good. I had $20 invested in this thing it better pan out. I went out and got some paint remover I had left over from another project and removed some of the nasty white paint:

The grain on the wood looked OK and it appeared to be a mahogany laminate on the top of the tambour (curved part) with solid mahogany for the trim. So, I took the guts out and dug in.

Like any 'normal' person in the 21st century, I watched a dozen youtube videos on furniture refinishing and followed the ones that weren't blatant advertisements for some furniture product. I got all the paint off and it was looking pretty good:

And a huge collection of little tiny parts. These are the parts from only the door on the back where you can service the thing:

I didn't want to post the parts and pieces of the clock and chimes because that would have meant taking them out of the Ziploc bags I put them in the instant I got them loose. Losing a single little part could have been a disaster and would have certainly dropped the value of the restoration. You don't use new parts on something you're restoring if at all possible to keep it original.

Anyway, I spent several afternoons refinishing the cabinetry, another one putting the parts back in and this is what came from the effort:

I realize that tambour mantle clocks are not of great value and I wouldn't get much profit if I were to sell it on ebay, especially since shipping would cost as much as I paid for it. But that wasn't the point. Now my family holds a little piece of another families history that we can care for.

At least until the estate sale.

Thursday, March 29, 2018


A while back I posted about the front loader valve on my big tractor, then forgot to post an update <link>. Well, after several screw ups and two separate orders for new hoses and such, I got it installed on the tractor.

Looks good doesn't it?  I had to cut off the old mounting arrangement entirely since there wasn't enough room for the new valve in the existing one. Then I had to get some 1/4 inch plate steel and bend it to wrap around as shown above. This gave me a place to mount the valve itself. Here's the old mounting arrangement:

Compared with the new:

That was an interesting project in itself; mainly because I'm not a welder, I just make stabs at it from time to time. That mounting plate was cut with a plasma cutter and then I bent it by cutting some lines about half way through the plate and bending it over in a vise. I stitch welded the cuts to make them hold. The entire plate was welded on the side of the front loader pillar so it would be in approximately the same place as the old one.

The old valve was plumbed with steel tubing and some of them wouldn't fit the new valve, so I got some hydraulic hoses from an on-line vendor and connected it that way.

My return line is the one on the bottom; that leads to a new fitting I put on the fluid tank.

You can just barely see the fitting under the mount on the side of the pillar. On this tractor they used the frame of the loader as the reserve fluid tank, so I couldn't drill any holes for bolts and such. It all had to be welded. Here's a better view of the return line:

That was another new experience, welding with a MIG loaded with flux core wire in the desert winds. Try fitting your head in there with a welding helmet around it. I have a couple of new scars to brag about.

After fixing a couple of leaks and crossing my fingers, it worked first try. I used the tractor to move some stuff around and the new valve was great. I probably have the only 3000 series Ford tractor with a joystick. Other folk have thought about it, but I haven't read about anyone actually doing it.

Aren't tractors fun?

Friday, February 23, 2018

Local Power Politics

I live in Arizona USA just north of Phoenix in the foothills. Power here is supplied by a company that calls itself "Arizona Public Service," that is an owned subsidiary of Pinnacle West Capital Corporation. I'm bringing this up because they've managed to get two rate increases in the last 12 months. This is actually not that unusual in this country, power companies are considered a "public utility" and given a service area in which they are a monopoly. Since they are a monopoly, they are heavily regulated and watched by some political entity; in the case of Arizona, it's the Arizona Corporation Commission. Additionally, the commission is responsible for making sure the utilities they oversee make a reasonable profit.

What an incredibly wonderful position to be in. To have a public commission that will guarantee you a profit no matter how bad a business you run. That MUST be worth a few regulations that may be somewhat annoying. I bet it also accounts for some of the high salaries they report...

Boring right? You probably clicked off the site about the middle of the first paragraph; I don't blame you.

The reason this is important to me is illustrated by the following screen shot of a rate calculator APS (the aforementioned jerk company Arizona Public Service) put on their web site:

Zoom in if you have trouble reading it.

This is a comparison of two of their rate plans compared with the plan I currently have. Notice the "difference" column for the two new rate plans, "premier choice large" and "saver choice max" and how much the difference is between the money I'm paying now and the money they project I will pay in the future. No, that isn't a typo and I didn't take photoshop to the screenshot, I'm projected to see an increase in over $1700 for a years worth of power.

Kinda makes you wonder why such a thing got past the regulating committee AZCC (Arizona Corporate Commission) doesn't it? Of course it couldn't be because all but one of the Commissioner's campaigns was heavily financed by APS could it? Of course not; we have the best commission money can buy.

Like most of this country our power bills are cluttered with a ton of little charges to disguise what is actually being charged. There is a charge for renting the meter we're required to have, a charge for reading the meter, a charge if we don't have a 'smart' meter which is read remotely, a charge for billing us, a charge for preparing the bill, ... you get the idea. As a matter of fact, my well power bill is literally half these charges and the other half is actual power used. (Yes, I can prove it.) I'm still wondering why I pay to rent a smart meter that is read remotely and being charged for reading it. Something there doesn't make much sense.

Leaving the whining aside, what the heck is with an increase of over $1700 a year? When they presented the rate plans they predicted a 4.5% increase that should average out to around $6 a month. This is the kind of thing they managed to push through the AZCC. It took them over a year of hearings and testimony to get the increase, and part of the settlement was the rate calculator they created. The calculator was designed so people could make an informed decision which plan they would go with in the future since APS is removing all the other plans people have had for years and years.

Well, after a few phone calls where I had to convince the APS representative that I was right, I finally figured out what the heck was going on. The calculator is pure crap. The calculator takes into account the actual time usage of the home and applies it to the new rates. Since I control my power and keep my peak usage way down, the calculator goes nuts.

That probably means that my bill will be considerably less than they predict, but it also means the calculator thousands of people use to make an informed decision is wrong. Nice job folks.

It also means that thousands of bills will be calculated by the same computers and the same software written by the same programmers that came up with this mess.

Yes, I filed a complaint with the AZCC and wrote letters to both of my state representatives about this very thing. Never had much luck with either of those, but

Wish me luck.

Friday, February 9, 2018

Grafana: Getting close to having something nice

I spent a few hours getting a Pi 3B running properly for this Grafana project. I actually think it is going to worth the effort. Grafana seems to work well and I have all the code on my own machine.

What I had to do was first set up a usb drive for the machine so I didn't depend on a silly SD card. I went into this with my teeth clamped down because previously it had been a pain to implement <link>. This time, it was a piece of cake.

What the folk did at Raspberry Pi was take the idea of a usb drive seriously. You enable a bit in the configuration, read the SD card you already have running into a file, write it out to the usb drive, plug it in and boot the Pi. OK, it's a little bit more than that, but not much.

I followed the instructions for setting the bit that allows boot from usb drive in the instructions on the Pi site <link> and found out the bit was already set. Then I remembered doing it when I was experimenting before. Then I took out the SD card, stuck it in my laptop and copied an image to a file. Next, I plugged in the usb ssd I already had from last time and copied the image over to it.

I walked across the room and plugged the usb drive into the PI, plugged in the power and went back to the laptop. Putty connected to the Pi on the first try. Notice I didn't say anything about plugging in an SD card?

Nope, the machine is running just fine without an SD card at all. You do realize that this makes the Pi into a full blown computer that you can actually rely on! Yep, this may justify making an enclosure for the combination so it looks pretty on the shelf. Right now it looks a little forlorn over there:

Looks a little strange hanging by its wires doesn't it? Notice the unopened Echo Dot boxes right by it? That's for another project I hope to get to some day.

I created a special user in the database that can only read data from certain tables to protect against the scary "SQL injection" attacks by hackers so my database is OK. I forwarded a port to the machine from my router so it can be seen from outside when I finally trust it enough to do so; I may be ready to show this thing off in a few days.

Then I spent a little time adding a few new items to the Grafana dashboard so I had more things I could check on just by glancing at the display. It looks like this right now:

I have the appliance power usage and battery level of the room temperature sensors charted so I can follow them over time. There's other things I can chart for fun and some other indicators that would be nice to see from time to time, but I think I'll use this arrangement for a while to see how it feels.

This entire project has been a success. I gues it was time for my readers to nudge me again about using some of the public tools. Remember this all started when I wrote about Xively a little while ago <link>. Maybe I'll look at Home Assistant next.

Or maybe I'll just make cool charts for a week or two.

Thursday, February 8, 2018

My Freezer: Right in the middle of installing Grafana

So, I'm installing Grafana on my laptop and pull up a chart that looks like this for my monitored appliances:

What the heck? the freezer in the house is getting hot! About a minute later, an email comes in that I actually check since I'm setting right there and it tells me that the house freezer is over 50 F.

Yes, my freezer sends me email. I set this up about 4 months ago when the door didn't get closed completely and was open for HOURS before I noticed it. It didn't cost me much to replace the stuff that had thawed out, I cooked it instead, but having it happen got me to thinking about alarming that kind of thing. I have a process called '' that I run as a daemon (all the time) that monitors the other processes and many of the devices that are reporting around the house. Things like the XBee coordinator process, the thermostats and the septic tank float are watched for problems and an email is sent every hour that they don't perform up to snuff. I just added code to this process to watch the temperature in the appliances to make sure they were OK.

I got up, walked over to the freezer, put the frozen french fries back on the shelf correctly, and closed the door. Problem solved and crises averted. All the scoffers out there that poo-poo'd me about putting a temperature sensor in the appliances ... No, none of the other folk that are automating and monitoring their houses had disparaging comments, it was neighbors and friends that just let the house react to things and then complain wondered why I bothered with that much work.

Really ?

What was really unexpected though was the aftermath of letting the freezer warm up like that. Take a look at this chart of the aftermath:

I suppressed the other appliances and expanded this to show a little normal operation on both sides. The big deal is how long it took for the freezer to return to normal. Because I left the door open for about two and a half hours the temperature went all the way from below zero up to 63 degrees. Then it took over 24 hours for the thing to get back to normal.

Wow, I gotta pay more attention to that door. What the heck was happening with the power usage during this time?

Here's the power chart for the same period, and I see that shortly after I closed the door the freezer went into a defrost cycle. That's a good thing because there had to be a bunch of ice all over the evaporator coil. The compressor was on trying cool a freezer with an open door and humidity from the house was freezing all over the coil lowering its efficiency. Then the ice maker took off to fill the empty ice bucket that I had emptied of sludge. The compressor ran for the entire 24 hour period getting the internal temperature back down where it should be, but the defrost cycles and ice maker ate some of the energy during this time.

So, the power bill will be a little higher this month, but notice that the freezer only uses around 150 watts. It won't cost me much at all. Once I started watching the appliances, it really impressed me how little power they use. A little Honda generator could power the appliances around my house for an extended power failure. That will almost certainly become a project in the future.

There's a couple of other things I want to point out to readers. First, the reason I left the door open was that I was playing with Grafana and got distracted. Second, the graph I was playing with actually told me something useful: the stupid freezer was getting hot. Third, I was immediately able to research the results of my screw up. What went on with the temperature recovery in the freezer and how that affected my power usage.

That is so stinkin' COOL. (pun intended)

So, I really recommend saving data for some period. I've never gone back more than a few months looking for some change or problem, but as shown here, a couple of weeks can really be valuable. I personally carry years of data, but that doesn't mean I have to; it also slows down my database queries. So, balance your own needs to what you want to do. Over time, you'll work it out.

Also, Grafana allowed me to look at different time frames and data really quickly. It took me just a few minutes to prowl through the data to understand what was going on. I could have done the exact same thing with other charting tools I've tried, but it would have taken me way longer. Man I'm glad they implemented MySQL in this tool.

I probably should go clean the freezer now, but at least it's working.

Tuesday, February 6, 2018

Grafana: Now to get it working on a Raspberry Pi

Last post I talked about how I got Grafana working on my desktop talking to my database. It actually worked pretty well. Unfortunately, the laptop is NOT where I want it to run; I want it to be running on one of my Raspberry Pi machines. Since I have one of the Pi 3b's and, it isn't doing much of anything, I put it there.

Like almost everything related to linux, this was a pain. It seems that since I last played with that little machine, there have been two major updates to the OS. They went from Jessie to Pixel to Stretch. WTF?? Are the folks at Raspberry Pi running out of things to do?

Fine, I downloaded the minimal image of Stretch and wrote it onto an SD card, made a couple of changes to the boot init files to run headless and booted it up. That worked first try. They really did make some improvements in that area. No gritting your teeth and closing your eyes before rebooting; it just worked. Then I went looking for a Raspberry Pi version of Grafana ... Guess what? Grafana doesn't make one !  Fortunately, there's a person out there that took this job on for the rest of us. The github user 'fg2it' has created a github repository that holds Grafana for the various versions of Pi OS's and it's relatively easy to use them --- after you figure out what the heck he is doing. The repository he created is here <link>, and in the instructions he does a cool trick of updating where Grafana is installed from, then used apt-get to install it. I stumbled over that for a while before I got it working. Really, really clever way to do it.

Aside: I ran into instruction after instruction for installing Grafana on the Pi, and almost all of them referenced back to fg2it's repository but, tried to take the credit for all the work. The slimeballs only referenced fg2it in the various commands. You'll see what I mean when you go looking around. Thank you fg2it.

I installed the 'Stretch' version of Grafana on the Pi I had just updated and made the simple changes necessary and it worked. I could log into Grafana and create charts that I copied from the laptop version I had used previously. Everything worked pretty well. Next, I added some more charts and explored a bit. Here's the way it looks right this minute when I load the graphs up on my laptop:

This is produced by Grafana running on a Raspberry Pi displaying on a Windows laptop and getting stored data from a MySQL database running on a QNAP network appliance. Cool !

Yes, since it's only a web page, it works on a phone as well:

You do have to scroll down on the screen to see all four of the charts, but that's the way it should work on a small device.

There's a ton of features I haven't played with yet, and I'll get to some of those over the next few days, but first I want to protect my database and figure out some way of offering this up on the web. It may get complicated since this machine can't be seen from out there.

Also, this machine only has an SD card on it. I had to leave the usb solid state drive out of the installation to get this much running. That will have to change since I've never had good luck with the SD cards. Three - four months and they crap out leaving you with a days work getting it running again.

Note that I have no dependency on any cloud service at all. It's my database, and all the software runs right here in the house.

Take that Xively !

Monday, February 5, 2018

Grafana: So, how well does this thing work?

As it turns out, pretty darn well. For those of you just getting into house control and monitoring, Grafana is a general purpose set of graphing tools. However, its documentation is daunting. They start right off using terms I never heard of and I couldn't tell what I needed to do. So, in typical Dave form, I downloaded it and started playing around.

I actually followed the steps they suggested and had my first graph running in about an hour. Never mind that the data was mixed up and in the wrong order, I actually had a graph I could look at.

Unlike most folk that delve into this tool, I'm using MySQL as a database manager to store my data. Most folk use a time-value series that is faster and smaller than a full blown database manager. But, I started with sqlite and moved to MySQL because I wanted the flexibility that was available. Of course that meant I had to create queries to the data base for gathering data, but they gave me examples to modify and it was somewhat easier.

Once I got the hang of the basics, I added my room temperature sensors as a 'panel' inside a 'dashboard'. This was cool, I could isolate sections of the graph for examination and change the span of time displayed just by clicking. Then I added the chart I look at a lot: outside temperature vs power usage. This is the key chart I use to keep power usage down to a reasonable amount.

I had about five hours into this by now, so I just let it run, updating every minute overnight. It worked fine. First thing when I looked at it, it showed the temperature drop for the night and when the heater kicked on. Nice.

Now, the problem is that I have to make it work for real. By that I mean that I want to be able to get to it from any device I happen to be using. I use a tablet a lot for this kind of thing and occasionally my phone. Haven't made that stuff work yet.

My plan is to install the software on a Raspberry Pi and allow it to be seen outside the house. That means I have to worry about security since there are SQL queries in there that can be messed with. I'm not much worried about someone watching my data to see if I'm home, I live in the middle of nowhere down a dirt road, so getting here is beyond the effort most thieves are willing to invest. Plus, if they make a mistake and I'm home ... shotgun practice!

Since I installed it on my laptop for convenience, I get to start all over again, so I don't want to develop it too much further before getting it set up for real use.

Oh, I also remembered why I haven't looked deeply into this earlier. They only enabled it for use with MySQL last year. Every time I looked before, it wouldn't do the job for me. MySQL seems to work fine now.

Here's a screenshot of the very first panel I made:

Like I said, this took a about five hours to get going, but that included installing it and figuring out how to connect it to my data base. Quite a bit of time was spent playing with colors and text as well, so your mileage may vary.

Nice tool if I can make it work outside the house on a phone. We'll see.

Monday, January 29, 2018

Xively: See I told you so...

Over and over again I've ranted about how I hate cloud storage of data, or reliance on cloud services for control around the house, and again I was justified. Earlier this month Xively (formerly Cosm (formally Pachube)) turned off free access. Never mind that years ago Pachube promised access forever for its early users and promised again when they became Cosm. Xively didn't honor the promise.

So much for promises. Remember the wonderful free service DynDNS that had its software installed on new routers? That went away a while ago and I had to change away from that service, now I get to stop the update process for Xively and pull the data from my local database instead.

At least I'm ready to do the switch, and I wish I had bothered to do it a year ago, but I was lazy. Teach me...

They were pretty nasty about it too. They sent emails to some fraction of their free users, and ignored the rest. Even the users that got the mail only got about two weeks notice and were unable to suck the data off the cloud service in time. The others (like me) found out from friends or just noticed one day that the service (and all their accumulated data) had disappeared from the site.

They couldn't even log in, and mails seemed to disappear into the same black hole as their data.

I don't recommend any product from LogMeIn. For software to avoid, see the Wiki page on the company and avoid all their offerings. The nasty way they did this should be a nice hint for folk.

I'm not going to recommend a replacement because this situation is only going to get worse over time. Hold your own data at someplace that belongs to you, not some profit driven jerks.

Now, where was that charting software I like.