Fun with DB Logging of Elk/etc


Senior Member
This is cool - I just setup automated data logging of my Elk temperature sensors and my callerid history into an ODBC database using the DatalogDB driver that jscheller wrote for CQC. I used SQLServerExpress (free version) as I was having issues getting the ODBC driver for MySQL to work.

I'm logging each of these fields every 30 seconds, but there's a flag on the driver schedule table to indicate whether you want to always write it or only write if there's a change.

For stuff like the temperature, you'd want to always record it so you could create a graph to see how it moves over time. I'll record the temperature in various areas of the house, compared to the temperature my aprilaire is set at, and plot them on the same graph so I can see how efficient the air flow is.

In a (to me) much more clear value add, i'll have the name, number, & time of every phone call into my house recorded into a database. This is something that bit me 3 weeks ago as my college buddy called, I accidentally deleted the message. I had the wrong email and # for him, and no one I knew had his # as he had just moved and was unlisted. No joke, I sent him a physical letter telling him to call me again, as I knew that would get to him (eventually).

No coding needed for this. I told the driver what fields to log by simply adding some rows to a table as such:


That results in a table with the following values:

You da man. :D

Does your MySQL connection seem to time out? Thats what mine has done in the past.

ODBC is an automationists ( :D Surely I'm no the first) dream come true. Would you know if MainLobby has a simular capacity?

What kind of DB is CQC storing it's data in?

Geovision stores it's CCTV data in a MS ACCESS DB *.mdb. So you can OBDC into/out of it as well.

Some of the things it tracks:

50 different event types, you might be able to add more. Never tried.

Object counter data > You can count stuff, cars... people...

Well... actually more then you can put in a forum post... If you want it in the future let me know.


In the CQC thread I noticed you mentioned caller ID data...

I have been using a package called FaxTalk Messenger Pro, doesn't appear to easily integrate but it has some cool background features for the automator.

1. Runs as a service in XP
2. Encodes voicemails to *.WAV
3. Emails the voicemail *.WAVs with CallerID info.
4. Supports many user mailboxes with custom prompts and greetings.
5. Automagically detects and stores incoming faxes, can auto print across network.
6. Supports transfers to second lines. ?? Never tried, basically you can call in and have it confrence outbound to somewhere else. Cel > Home VoIP > China I guess.
7. Fax on demand, you can have a general contract stored on it and request it fax it back to your CallerID. ?? Never tried.

The service is terrible but the product works well. You can save some dough by getting a Aopen FM56 voice modem. It comes packaged with an SE version and they are only $10 on ebay, I bought one locally used for $2. Once you have the SE the upgrade is one like $30. Going that route is a little more then half the price of just buying it online. If you find the modem but no software let me know and I'll give you a copy of the driver CD originally included with it.
Actually, jscheller is the man - he wrote the driver, and even some XML graphing utility to plot the temperature stuff. All I really did was import the driver and insert a few rows into a table.

CQC has ODBC drivers, so any ODBC db will do. You could even share that Geo db if you wanted, the driver just needs 2 standalone tables. There's also a "generic db" driver for CQC so that you can read/write/update/delete out of any ODBC table, so you could even display that CCTV data inside CQC if you want.

Re: MySQL vs SSE:
I happened to have mySQL downloaded already as I'm in the process of setting up so the community can have recipes visible through their touchscreens, but that's a seriously user-unfriendly tool. I'm sure it was some silly user-error thing, but command line is not my idea of GUI best practices.

SQLServerExpress may be from the evil empire, but dang is it easy to setup & use, and it's a free download to boot.

Absolutely zero idea whether ML has this or not, but given that there's already a wildfire spreading through CQC now with applications for this (just came out a few weeks ago), I would imagine if ML had it we'd have seen something by now.
It's crazy that nobody else thinks this is uber cool!!!

#1 This allows statistical processing!!!

All sorts of killer logical controls can be implemented even predicting the future in some instances. Anything cyclical can be forecast, many things are.
That is pretty cool. I wish CQC had some on the fly graphic rendering utilities so that you can display graphs in the interface viewer.
I agree, this is very revealing from an HVAC perspective. However, I think the impact of 2way ODBC integration with your HA system is even greater than we can conceive right now.

I'm now replanning my caller-id integration details as I'm realizing what I can do is to have a single db that imports my outlook DB and every call i've ever gotten. Then, when I'm looking for a #, I can use a CQC screen to search on a name (or portion of a name with the CQC wildcards), and have the system find it regardless of location. In addition, I can tag records that aren't currently in my outlook contacts list, mark them somehow, and automatically update my outlook list with the missing records.

Another application of this is to log every time CQC has to take "corrective" action to address bad behavior (ie, closet or other light was left on, CQC detected no motion so it turned it off, no one immediately turned it back on indicating erroneous shutoff).

There's probably 2-3 other big things you could do, but you get the point. Of course, not all of that is possible at this minute, but the ODBC integration is a HUGE first step down that path.
Well you can make the whole system "smart", every event gets logged and compared against timed events. Cyclical events not on a timer can automatically be added. You are getting into a whole different level of automation and logic but having dynamic systems that don't need nearly as much hard coded programming past device recognition and assignment.
That is pretty cool. I wish CQC had some on the fly graphic rendering utilities so that you can display graphs in the interface viewer.

That's come. The first step will just be a live graph so that you can graph any numeric/boolean field live on the user interface. That will have it's own uses, but it won't retain any information, it'll just be a live graph.

We'll later do a driver that can do historical storage of the data, and which can serve up the last X number of samples to a variant of the graph widget, so that it can come up with previous data and then pick up from there with live graphing.

Doing a purely historical data spelunking interface would be something further out, since it can be a lot of work. We'd initially just offer an export feature to export out the historical data for a selected set of fields into some sort of CSV format or something, whcih can easily be imported into the tool of your choice.
When I was on HS, the graphing pieces were handled through Excel and ActiveX. You do need to have office installed, but it was pretty simple to generate some decent graphs with very little effort. You would still need to generate them somehow like on a web page displayed in a browser widget, but it would probably be doable today.

It would probably get us through until Dean codes native graphing capabilities into CQC itself...

That is my plan if I ever can get around to loading the driver... :)