OK, so I’ve made a few changes to the database schema. I’ve simplified it a little by taking the stateHistory table out and simply pushing those values into the perfHistory table. Eventually, I’ll be adding a dimensional model data warehouse to this project and I’ll split these types of measures up there instead (so I can track duration of state better). Also, I took out the circular reference to the channel and tied it only to the measure itself instead of the device. I can no longer reuse the channel definitions but that wouldn’t be a very common occurrence anyway.

Here’s the updated schema:

I also took out the identity field for the history table since it was really unneeded.

For a while now, I’ve been working on the database design for this project. I wanted to keep it simple and limit the number of tables. In this first design there are a few columns that aren’t normalized, but it helped to limit the table count.

The design is somewhat like a CMDB in that it keeps track of configuration items, their properties, as well as the type of item. Such things as computers, refrigerators, furnaces, and rooms in the house would be considered configuration items.

So the major parts (tables) to the database are as follows:

Configuration Item

configItem – Again, stores information on things like computers, freezers, and rooms.

Location

location – defines where items such as sensors, config items, and measure devices are physically located.

Sensor

sensor – These are specific sensors located in the house, they may be as simple as a thermistor or as complex as an entire script that lives on a computer.

Measure

measure – These are specific measures and the values associated with them.

Measure Device

measureDevice – This table stores the various physical devices the measures are taken with. For example, the sensor might be a thermistor, but the measure device would be an analog to digital converter that measures the voltage across a voltage divider.

History

(perfHistory, stateHistory) – There are two tables here, one for performance values such as temperature, humidity, free physical memory and the other for state values like light on, motion detected, and door closed or open.

So far, I’m not including any aggregate tables for data summarization or any user interface tables…I’ll leave those for later.

Here is the database schema:

Since this project will collect data from many different sources such as data acquisition boards, pc computers, network systems, and weather station data, I’ve debated on building a centralized collection system or a distributed system. After prototyping a collection database and a windows service that would capture all of the data, I thought it may be better to build a distributed system instead.

What would a distributed collection system entail?

Here are some pieces of a distributed for a possible home-based data collection system:

• Database for some config information as well as data measurement storage and retrieval
• Web service for receiving all measurement updates
• Windows service for collecting all analog measurement data from the LabJack ADC(s)
• Scripts running on each computer be it the Mac OS, Windows XP, or Windows 7 for performance and capacity measurements.
• Windows service to collect all data logging for weather, power, power and some event data.

Here are some of the pros of both a  centralized system and a distributed system:

Centralized System Pros:

1. Simpler maintenance since all measurements are taken from one spot.
2. Less complexity overall since there are fewer pieces overall.

Distributed System Pros:

1. Easier to collect data from computers since rights are less of an issue when running locally.
2. Each piece is far simpler since it’s only responsible for doing a few basic things.
3. The system can grow as the different types of measurements grow without affecting existing measurements

I’ve continued to think about a centralized system since one giant windows service that gets all of it’s configuration data from the database would be far cooler and ultimately be more useable from install to install if I wanted to use this system elsewhere. This is nice, but as I was writing some prototype code for this service, it quickly got fairly complex and I hadn’t taken a single measurement.

I now believe that a distributed system might be easier for a the reasons listed above. I still plan on building a fairly healthy windows service to take the analog to digital measurements done primarily by the LabJack(s), but now can focus entirely on that process in the windows service. One issue that kept cropping up was how to handle delta-only measurements for state on a schedule. Another issue was how not incur a long timeout wait while the service tried to connect to a computer for WMI measurements every time the service ran since the computers would many times be off.

With a distributed system, the computer security and scheduling issues aren’t as much of a problem since the processes would only take measurements when the  computer was running. I would let the web service determine if the measures needed to be recorded or ignored.

Next Steps

1. First, I will attempt to prototype a web service built in either PHP or ASP.Net, not sure yet. I’m very familiar with ASP.net web services but would like to try PHP.
2. Second, I’ll hook the web service up to the MYSQL database I’ve prototyped. As soon as I’ve done this and proved I can record measurements from some simple scripts, I’ll put my code and schema out for anyone that cares to check it out.

Basically just a follow up here. I wanted to use the script in Part 1, with some backup hardware and Windows Scheduler to automate this task.

I wanted to use something that was easy to swap media out and had a large capacity. With the price of hard drives these days, it seemed reasonable that I use something that used a standard SATA internal hard drive. After some research, I decided upon a

NexStar Hard Drive Dock

. 

I picked one up for under $40 and with a 1TB SATA drive, it seemed like a great addition to my backup solution.

Windows Scheduler

Now we need some way to kick of the script. To set the script from the previous post to run automatically on a schedule, I used a utility found in Windows called “Windows Scheduler” You can find this tool under Control Panel. Here’s a picture of what it looks like with this job already setup:

As you can see the job is named “Network Backup” and setup to run once every day at 7:17 PM. I set it up like this since I was unsure of when or if the computers being backed up would be turned on and available or not. I figured there was a pretty good chance that at least once a week, it would get lucky and backup the files on each computer. If you read the previous post, you’ll remember that I only created new backup folders for each week of the month. This will cause the backup files to be placed in the same folder for one week, and then switch to the next folder the following week. That way, I’ll be have the option to go back at least 4 weeks to get a copy of some file or set of files.

Here’s how Windows Scheduler looks with this job setup:

And here is the same window but viewing the schedule information:

With a standard hard drive in the dock, here’s what it looks like:

With the external SATA (eSATA) cable hooked up, this can write files as fast as my 100mb network can feed it.

One thing I promised myself was at some point (early on) I would create an automated backup solution for my home computers. I wanted an automated and easy to configure system that I was confident of backing up my most important files. The criteria for this operation were the following:

• Must use a well known and free backup command-line tool that I could script out exactly how and what I wanted it to do

• Must be capable of being run from a scripting language (preferably VBScript).

• Should be able to copy both large files and large quantities of files that might crash less robust tools

• Have the ability to copy from Windows’ default hidden shares like “C$\” and UNC paths like “\\Foo”

A friend of mine had mentioned a long-time windows utility called Robocopy (he also provided some of the VBScript that I used). This command line tool was designed by Microsoft for reliable mirroring of directories and directory trees. You can check out the wikipedia article for Robocopy if you are so inclined.

Robocopy meets all of the requirements mentioned above.

After setting up the computers on the workgroup with the required permissions and settings, the next step was to create a script file that would for now, manually backup files (I’ll cover an automated solution as well as a database configuration and logging solution in future articles).

I wanted to have a process that would eventually create multiple copies of all the files required for backup. There would be a separate copy of each file for each week of the month. This means I would only get a fresh backup of files once every week. For the types of stuff I wanted to backup (family pictures, music and movie files, code and design files) I felt this was adequate. When viewing the backup destination, I would see a maximum of 6 folders (since there could be 6 different weeks in a month) with names like Week1, Week2, Week3, etc. The directories and files would all be inside these folders. Here’s a copy of what that backup destination structure could look like:

For a script and Robocopy to do this, it should have the following steps:

• Setup of global parameters including a collection of machine\directory paths for configuration

• A process of adding the path information to the destination area including machine name and share name

• From a script, Robocopy will only be provided one source and one destination at a time so there will be a primary loop in the script that loops through all specified source paths

• A process to capture and write the results from the process so it may be reviewed at a later date.

• Functions to determine the week of the month, attempt to create the week folder, and create a name for the results file

• A method of executing the Robocopy tool with the proper commands and capturing the information it provides while executing. This is the key to the whole process!

Robocopy requires a command set like the following example:

“Robocopy \\foo\c$\documents D:\Backups\Week1 /S /NP /R:2 /W:5”

This will tell Robocopy to attempt to connect to the hidden share “c$”  on machine “foo”, and copy everything under the directory “documents”  to the backup destination “d:\Backups\Week1”. The stuff behind the destination are known as switches. The “/S” copies all subdirectories unless they are empty. The “/NP” tells Robocopy to not provide any progress information while copying files. The “/R” is tells the tool to retry 2 times before giving up when there is an error. Finally, the “/W” provides the time to wait before retrying, in this case 5 seconds. There are many other switches, but I’ll leave it to you to research all the unique ways Robocopy can be manipulated.

I won’t go into all of the code, but here is a chunk of code for specifying what source paths there are:

Dim array_Sources(3) ‘Source Paths Array
Dim array_Destinations(3) ‘Destination Paths Array

array_Sources(1)    = “\\octavious\e$\Sierra”
array_Sources(2)    = “\\spartan\c$\logs\”
array_Sources(3)    = “\\Minimac\Lisa\”

As you can see one of the machines is a Mac Mini, which with SMB and the proper account settings, can be backed up as well.

Here’s the key to the whole VBScript and Robocopy marriage to get this system to work:

Set oWSH = WScript.CreateObject(”WScript.Shell”)
Set oCOMMAND = oWSH.Exec(sCommand)

Where sCommand is basically a string containing the Robocopy command line for that specific source and destination set as shown above.

Here’s a copy of the entire VBScript backup solution file.

You can probably find a copy of Robocopy somewhere on Microsoft’s site if you dig around a little.

There’s also a GUI for Robocopy that someone at Microsoft built along with all the files and documentation.

Next I’ll be looking at providing some hardware to this solution so I can easily switch out my backup media with another. Remember, this is only the first step in the overall solution…Eventually we’ll have a database tied to this so we can dynamically assign backup sources and log backup metrics for reporting and monitoring of the backup process.

So one of the things I would like to include in my Home Automation project is the ability to track performance and capacity metrics for all computers in the household. I would like to monitor PC information like hard drive capacity, free drive space, CPU utilization, current processes, maybe a list of installed software, etc.

Fortunately, all of these PCs run Windows XP so that helps considerably. I do have other devices on the local network, but that will be a topic for a later post. Since I know VB quite well and there are a LOT of examples on the web using it and Windows Management Instrumentation (WMI) to gather information on the Windows devices, I’ll use these two tools to do the bulk of this work. Also, I wanted to remotely gather this information and not have each PC contain scripts scheduled to run.

Here’s some information on my current setup:

• All PCs are in the same Windows Workgroup

• All PCs have the same accounts with similar passwords (1 account for logging in and one account for remote access)

• All devices on the network are setup with DHCP for addressing

• Some of the PCs have Windows Firewall enabled

Although XP is pretty open out of the box, there are a few things we need to do before we can successfully run WIM-based VB Scripts remotely:

Windows Security – We need to setup the windows account we’re going to use similarly across all PCs we intend to remotely access. This account should be in the Administrator’s group.

Windows Firewall – We must be able to remotely connect to a PC running Windows Firewall with WMI and VB Script. This will be done by configuring a secure WMI connection in Windows.

Guest User – In a Windows XP workgroup, all remote connections coming from the “network” will be authenticated as a Guest User. A Guest User has very few rights so we must determine how to configure Windows to recognize the account used by the remote connection.

Here are the last two configuration settings in further detail:

Windows Firewall Settings For A Secure WMI Connection –

On the remote computer, we need to grant DCOM (DCOM is basically a Microsoft technology for communicating among software components) remote launch and activation permissions for the user we setup earlier. There’s a good article about this on the MSDN site. There’s a utility  in Windows called DCOMCnfg.exe that’s found in Administrative Tools in Control Panel (you can run this from Start->Run if you prefer). If you open this utility up, it should look something like this:

Once this is open, right-click on “My Computer” and click on the “COM Security” tab. You should see something like this:

For both the “Access Permissions” and “Launch and Activation Permissions” we need to add the user we setup above to the names list in both and make sure all of the “Allow” settings are checked on both dialog boxes. Once this is done, Windows Firewall will allow remote WMI connections to this computer.

Guest User Settings for Remote WMI Connections –

This one was a little tough to find. I had been attempting to run some WMI commands (more on this later) against several PCs and getting Access Denied messages until I discovered this behavior in Windows – When a remote connection is made to a computer in a Windows Workgroup, the user will be authenticated using the “Guest User” account in Windows. This can be reset to use the connection’s credentials by changing a setting in the registry. If you don’t feel comfortable changing registry settings in Windows, don’t attempt this. The registry key is located at HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa and is called “ForceGuest”. By default, this setting is set to 1 (Windows will try to log on using the “Guest” account). If this setting is changed to 0 (disabled), the connection will be logged on as the specified user.  Once this setting is set to 0, you should be able make remote WMI connections using you current account. Their is an article on Microsoft’s support site that walks you through setting this up.

I’ll be working through some VB Script WMI calls in later posts but here is a simple script to find all of the drives on the specified computer “Spartan”:

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")

strComputer = "Spartan"

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * From Win32_LogicalDisk Where DriveType = 3")

For Each objItem in colItems

    strDriveLetter = objItem.DeviceID

    Wscript.Echo("Found Drive " & strDriveLetter & " On Computer " & strComputer)

Next

After putting this stuff together and working with various scripts across a Windows Workgroup, I’m quite excited about what can be done with WMI and VBScript, but getting the security and configuration setup correctly was much more of a job that I originally thought it would be. A Windows Domain would make this far easier, but I really wanted to stay away from that kind of a setup for my household. I really do hope this helps anyone attempting else using WMI, VBScript and a Windows Workgroup.

I just received my LabJack U3, so I’ll be checking that out in an upcoming post along with more on current and temperature sensors.

While waiting for my current and temperature sensors to arrive, I thought it might be a good time to look at a few devices for interfacing a computer to the real world. Basically we need something to convert the signals from our various sensors and transducers to the computer.

This is usually handled by a device called a analog to digital converter or ADC. There are MANY different types of ADCs used for converting all kinds of signals to the digital world. The differences and choices could fill a whole set of books, but for our discussion, I’ll try to keep it short.

There are a lot of choices in the home automation arena that provide this function as well, but I really wanted a more flexible solution that I could build on and that didn’t tie me into a specific solution.

A few things we need to concern ourselves with when choosing the correct ADC are resolution, input levels, and sample frequency, and input voltage range.

Resolution

– Basically the number of levels representing the input signal range (in volts). The number of levels are measured in bits. Since it’s a binary scale, the different input resolutions are 8, 10, 12, 14, 16, 24, etc. An 8 bit ADC, would provide 256 different levels and a 24 bit ADC would provide 2^24 or 16,777,216 levels! Generally the higher the resolution, the more expensive the ADC, so we need to find a ADC that will provide enough discrete levels for our needs.

Number of Inputs

– For each sensor we want to use, we will need one input (for now I’m not going to cover single-ended or differential inputs). Again, prices go up with more inputs, so for now, I’m looking for a cheap low-count input ADC.

Input voltage range

– This is simply the maximum voltage the ADC will accept. A higher number here doesn’t mean a better ADC, you just need to determine what input voltage range suits your purposes and find a ADC that fits that range.

There are a ton of other properties when discussing ADCs, but not here, not now. If your interested in more details about ADC, check this Wikipedia article out.

Okay, so after fully reading the Wikipedia article you probably know more about ADCs than me. But suffice to say, I still need to determine what type of resolution I need. Let’s start with an 8 bit ADC.

For 8 bits and lets say a voltage range of 0-5 volts, that would be 5V divided by 256 levels or  .02 volts per level. Also, let’s say we need to measure a temperature sensor that provides a voltage range between 0 and 5 volts for a temperature range of 256°F (-40 to 216°F to make the math easier). That would give a maximum temperature resolution of 256°/256 levels or 1° per level. This is under perfect conditions and generally I would expect to lose half that resolution for all kinds of reasons I’m not going explain here. That means the smallest temperature change we could detect is 2°F

I think we can do better than that. Let’s see what a 12 bit ADC can give us for a resolution? For 12 bits we get 4,096 levels! From the math (256°F/4,096 levels) we get a maximum resolution of .06°F per bit. Even half of that is around .1°F. That seems more reasonable and I think I’ll stick with that. So now that we’ve chosen 12 bits as guide, lets find a few ADCs that will work for the needs of this project.

12 Bit Analog to Digital Converters:

• MeasurementComputing – These guys handle all kinds of test and measurement equipment, but for out purposes, I found this little gem (miniLab 1008) that isn’t too expensive ($129.00). It has 8 single-ended or 4 differential analog 12 bit inputs.

• DataQ Intsturments – Again, another company that carries many types of measurement and testing equipment. They also have a similar 12 bit ADC with 4 differential inputs (DI-158U). Currently it’s listed at $99.00.

• Labjack – Looks like a smaller company that really focuses on AD conversion. Their products even look cool. The one that seems to work for this project is the U3-LV pictured here at $108.00

I’m not sure which one these I’ll be getting to prototype some sensor measurement, but I’ll be making a decision fairly soon. If anyone would like to comment on these or similar ADCs I would really appreciate it.

I’ve researched what type of cheap current and temperature sensors I would like to prototype for my project and decided upon the following:

1. An NTC Type Thermistor from Cantherm (CWF1B104J3950) that provides relatively high precision, reliability, and response time. This type of sensor also allows for a fairly simple conditioning circuit.

2. An Integrated-circuit precision temperature sensor (LM34) from National Semiconductor. This sensor provides a very linear output (+10mv/°F scale) at a very low cost. The problem with the IC type sensor is that it requires a more complicated power supply if you wish to measure temperatures around or below 0° Fahrenheit. I’m prototyping these since I already have a decent supply of them.

3. The CR Magnetics’ CR8410 current-sensing transformer. This a relatively cheap current transformer that is capable of measuring up to 20 amps. You need to take precautions when using this type of device since there is a potential to create high voltages when reading house current so if you aren’t sure, get someone who is experienced with these.

These parts were ordered from Digikey. I use both Jameco and Digikey when buying electronic components and I’ve always had good results with both companies.

Also, some thanks to the people over at All About Circuits, I wasn’t sure on the transformers and they were there to help me out.

When they arrive, I’ll set these components up to do some initial testing and evaluation. In the meantime, I’ll start some initial work on the Computer measurements I discussed earlier as well as a solution for converting the conditioned signals from the current and temperature sensors to a digital signal a computer can understand.

I would like to initially take three different types of measurements and prototype the transducer, sensor, conversion from signal output to a computer-friendly signal, and a rough software service to monitor these signals.

The previous post touched on one these properties, namely temperature. The other two properties I would like to address are electrical current and several computer measures such as free memory and disk space. I realize the computer measurements are not common to the others, but I feel I need to test those types of properties as well.

I’d like to examine some possible sensor types for current. The main priorities are price, simple circuitry for a reasonable output signal, and sensitivity.

Next I will be looking into various ways to measure alternating current (AC) in the house.

I guess I really didn’t expand on the idea of sensing in the original goals of the project, but that’s what this project is really about. Measuring, recording, displaying, and trending a large number properties inside and outside the home is a large part of this project. I’m not sure of the exact number, but let’s just say when I’m finished, it should be more than a hundred and less than a thousand. Since a significant amount of these properties will be in the form of temperature, we need to determine a reliable, somewhat simple, and cost effective solution to measure ambient and surface temperatures.

Having worked with temperature measurement in the past, there really are three ways to cheaply and accurately measure ambient and surface temperatures;

1) Integrated Circuit (IC) sensor

2) Resistive Thermal Device (RTD) – a type of thermal resistor

3) Thermistor – another type of thermal resistor

Of course, there are other ways to measure temperature such as thermocouples and fiber optics, but I really see these three as my main choices.

Let’s list out the types and some pros and cons of each:

Three parameters that are important to sensors are accuracy, repeatability, and sensitivity. Many times it’s a trade-off between these parameters, complexity, and cost to get the best type of sensor for the application. Let me cover these three parameters briefly;

1) Accuracy -  This tells us exactly how close the sensor’s output is. Here is a definition I had:

2) Repeatability – A measure of how closely the sensor will measure the same value every time. Here is a more formal definition:

3) Sensitivity – This indicates how much the sensor’s output changes when the measured quantity changes. Sensors that measure very small changes must have very high sensitivities.

Here is a good article on various temperature sensors.

I suppose I could buy a solution from a Home Automation vendor, but I’m really after a high-volume, repeatable, and simple (maybe relatively) solution that keeps me in my limited budget. After some initial trials (more on them later), I hope to find a good solution for a large number of temperature measurements.

Next, I’ll have to determine what kind of circuitry I’ll use to convert the property changes in these devices to a usable signal (think transducer and sensor) and then some method (analog to digital conversion) to get them into a computer. That sounds like at LEAST two more topics for future articles.