The other question is though, if you're logging counter values to RRD, I think you can set the max counter value for a field and it will take care of counter wrap. I'd have to read the docs more thoroughly to ensure that this is actually the case though. If RRD handles it, you do not need to. The other question though, is can RRD handle counters that *decrease* in value, like if you were putting power back on the grid with a wind turbine or solar?
Brultech ECM-1240 Software Development
- Thread starter 3tones
- Start date
The other question is though, if you're logging counter values to RRD, I think you can set the max counter value for a field and it will take care of counter wrap. I'd have to read the docs more thoroughly to ensure that this is actually the case though. If RRD handles it, you do not need to. The other question though, is can RRD handle counters that *decrease* in value, like if you were putting power back on the grid with a wind turbine or solar?
bpwwer
Member
The counters never decrease. The non-polarized counter will increment anytime energy is flowing regardless of direction, the polarized counter will only increment when energy is flowing in one direction. If energy is flowing in the other direction, the polarized counter simply doesn't increment.
castlestone
New Member
@tenholde
You obviously know your way around DBs. Any chance that you could show me a sample SQL query I could use in the Brultech DB to find the occurance of a missing record by comparing the time stamp of successive records as you step through the table. If I had a sample that would let me select a range of records to test it would get me started and I could build on it frpm there.
Thanks
tenholde can correct me if I am wrong but it is my understanding he has his own DB. if you scroll back in the thread there is a link to his website where he has some APIs.
I was the one looking at the brultech db. I have wrote up some of it on the CQC forum. Most of the write up deals with the database itself so even if you dont use CQC it would be helpful.
http://www.charmedquark.com/vb_forum/showthread.php?t=8915
there is some info there on how to connect to the DB etc..
I just started documenting the usage/billing queries on the CQC site and hope to list out a bunch of the usage calculations this weekend.
honesty you dont really need to do anything fancy about looking at the gap. Brultech logs the data in two tables. They log watts and KWH (FROM THE LAST RESET OF THE DEVICE) in a table monitordata_minute every few seconds(even though the table name says minute). Once an hour they sweep the usage and log the deltas in the KWH into monitordata_hourly. You can query that table to get the usage. If the device is down for some time the next write to monitordata_minute table will have the higher KWH reading and at the top of the next hour the delta from the last good reading will be writing to the monitordata_hourly table.
Here are some notes from the CQC thread about 30 day usage. I will post back in a little while when I update this to display 30 days of costs instead.
----------------------
To view the 7 or 30 day usage run the following command, replace the -30 with -7 if you want 7 days. To clarify this table has a record per hour so if you are selecting a 7 day history you get 168 hours worth. What I mean by this is that if you are in the middle of the Friday you will still get a full 7 day slice rather then half of Friday plus the 6 preceding days.
Replace deviceaddress=142265 with your device address. If you want to get really tricky and you want to name your channels you can for example replace "AS SumOfch1kwh" with "AS MainMeter"
SELECT monitordata_hourly.deviceaddress, Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh, Round(Sum(monitordata_hourly.ch2kwh),3) AS SumOfch2kwh,
Sum(monitordata_hourly.aux1kwh) AS SumOfAux1kwh, Sum(monitordata_hourly.aux2kwh) AS SumOfAux2kwh,
Sum(monitordata_hourly.aux3kwh) AS SumOfAux3kwh, Sum(monitordata_hourly.aux4kwh) AS SumOfAux4kwh,
Sum(monitordata_hourly.aux5kwh) AS SumOfAux5kwh
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=datetime('now ', 'localtime', '-30 days')
Group by monitordata_hourly.deviceaddress;
I will report back in a few with a query that will calculate the usage costs over the same period.
I was the one looking at the brultech db. I have wrote up some of it on the CQC forum. Most of the write up deals with the database itself so even if you dont use CQC it would be helpful.
http://www.charmedquark.com/vb_forum/showthread.php?t=8915
there is some info there on how to connect to the DB etc..
I just started documenting the usage/billing queries on the CQC site and hope to list out a bunch of the usage calculations this weekend.
honesty you dont really need to do anything fancy about looking at the gap. Brultech logs the data in two tables. They log watts and KWH (FROM THE LAST RESET OF THE DEVICE) in a table monitordata_minute every few seconds(even though the table name says minute). Once an hour they sweep the usage and log the deltas in the KWH into monitordata_hourly. You can query that table to get the usage. If the device is down for some time the next write to monitordata_minute table will have the higher KWH reading and at the top of the next hour the delta from the last good reading will be writing to the monitordata_hourly table.
Here are some notes from the CQC thread about 30 day usage. I will post back in a little while when I update this to display 30 days of costs instead.
----------------------
To view the 7 or 30 day usage run the following command, replace the -30 with -7 if you want 7 days. To clarify this table has a record per hour so if you are selecting a 7 day history you get 168 hours worth. What I mean by this is that if you are in the middle of the Friday you will still get a full 7 day slice rather then half of Friday plus the 6 preceding days.
Replace deviceaddress=142265 with your device address. If you want to get really tricky and you want to name your channels you can for example replace "AS SumOfch1kwh" with "AS MainMeter"
SELECT monitordata_hourly.deviceaddress, Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh, Round(Sum(monitordata_hourly.ch2kwh),3) AS SumOfch2kwh,
Sum(monitordata_hourly.aux1kwh) AS SumOfAux1kwh, Sum(monitordata_hourly.aux2kwh) AS SumOfAux2kwh,
Sum(monitordata_hourly.aux3kwh) AS SumOfAux3kwh, Sum(monitordata_hourly.aux4kwh) AS SumOfAux4kwh,
Sum(monitordata_hourly.aux5kwh) AS SumOfAux5kwh
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=datetime('now ', 'localtime', '-30 days')
Group by monitordata_hourly.deviceaddress;
I will report back in a few with a query that will calculate the usage costs over the same period.
WayneW
Senior Member
A new beta has been posted.
http://www.brultech.com/dashboard.html
http://www.brultech.com/dashboard.html
castlestone
New Member
Here is the calc for 7/30 usage with cost. The kwhcost1 is the KWH cost field in the dashboard.
SELECT monitordata_hourly.deviceaddress,(datetime(monitordata_hourly.date)),
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from applications) AS SumOfch1kwh_cost,
Round(Sum(monitordata_hourly.ch2kwh),3) AS SumOfch2kwh,
Round(Sum(monitordata_hourly.ch2kwh),3)*(select kwhcost1 from applications) AS SumOfch2kwh_cost,
Sum(monitordata_hourly.aux1kwh) AS SumOfAux1kwh,
Sum(monitordata_hourly.aux1kwh)*(select kwhcost1 from applications) AS SumOfAux1kwh_cost,
Sum(monitordata_hourly.aux2kwh) AS SumOfAux2kwh,
Sum(monitordata_hourly.aux2kwh)*(select kwhcost1 from applications) AS SumOfAux2kwh_cost,
Sum(monitordata_hourly.aux3kwh) AS SumOfAux3kwh,
Sum(monitordata_hourly.aux3kwh)*(select kwhcost1 from applications) AS SumOfAux3kwh_cost,
Sum(monitordata_hourly.aux4kwh) AS SumOfAux4kwh,
Sum(monitordata_hourly.aux4kwh)*(select kwhcost1 from applications) AS SumOfAux4kwh_cost,
Sum(monitordata_hourly.aux5kwh) AS SumOfAux5kwh,
Sum(monitordata_hourly.aux5kwh)*(select kwhcost1 from applications) AS SumOfAux5kwh_cost
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=(datetime('now','localtime','-30 days'))
Group by monitordata_hourly.deviceaddress
SELECT monitordata_hourly.deviceaddress,(datetime(monitordata_hourly.date)),
Round(Sum(monitordata_hourly.ch1kwh),3) AS SumOfch1kwh,
Round(Sum(monitordata_hourly.ch1kwh),3)*(select kwhcost1 from applications) AS SumOfch1kwh_cost,
Round(Sum(monitordata_hourly.ch2kwh),3) AS SumOfch2kwh,
Round(Sum(monitordata_hourly.ch2kwh),3)*(select kwhcost1 from applications) AS SumOfch2kwh_cost,
Sum(monitordata_hourly.aux1kwh) AS SumOfAux1kwh,
Sum(monitordata_hourly.aux1kwh)*(select kwhcost1 from applications) AS SumOfAux1kwh_cost,
Sum(monitordata_hourly.aux2kwh) AS SumOfAux2kwh,
Sum(monitordata_hourly.aux2kwh)*(select kwhcost1 from applications) AS SumOfAux2kwh_cost,
Sum(monitordata_hourly.aux3kwh) AS SumOfAux3kwh,
Sum(monitordata_hourly.aux3kwh)*(select kwhcost1 from applications) AS SumOfAux3kwh_cost,
Sum(monitordata_hourly.aux4kwh) AS SumOfAux4kwh,
Sum(monitordata_hourly.aux4kwh)*(select kwhcost1 from applications) AS SumOfAux4kwh_cost,
Sum(monitordata_hourly.aux5kwh) AS SumOfAux5kwh,
Sum(monitordata_hourly.aux5kwh)*(select kwhcost1 from applications) AS SumOfAux5kwh_cost
FROM monitordata_hourly
Where monitordata_hourly.deviceaddress=142265 and (datetime(monitordata_hourly.date))>=(datetime('now','localtime','-30 days'))
Group by monitordata_hourly.deviceaddress
I've updated my brultech.pl script to 0.85. Fixes include:
- Fixed large value calculated by client if it was started right after the server.
- Added timeout on read of 5 second after which the connection will be reset.
- Detect counter reset and react accordingly.
http://www.mentasm.com/brultech
- Mark.
- Fixed large value calculated by client if it was started right after the server.
- Added timeout on read of 5 second after which the connection will be reset.
- Detect counter reset and react accordingly.
http://www.mentasm.com/brultech
- Mark.
Awesome. Any plans on adding support for Google Powermeter? I see the official Brultech software has something now, but I haven't upgraded yet.
I have written some code to run an application as a service, with the Brultech software in mind. The only caveat is that with windows 7 (and Vista I'm assuming) you can run a service to "interact with the desktop" but it is crippled. I wrote it in C#, so it needs .net 3.5. I haven't done any long term testing yet.
1) Is anyone interested in this?
2) Does anyone know how to work around the Vista/7 issue, without having access to the code of the brultech software?
Tom
1) Is anyone interested in this?
2) Does anyone know how to work around the Vista/7 issue, without having access to the code of the brultech software?
Tom
beelzerob
Senior Member
I've been asked recently for some of the data stored in the .db1 file the engine keeps everything in. I'm a relative db illiterate (though I can form sql if I have to)...what is the easiest way for me to extract the kWh data for the 1240 channels at 8am and 9pm for the past X days?
beelzerob
Senior Member
I'd prefer a record for each day.
So, in guesswork-form, it'd give me:
Mar 23 8am Ch1 kwh=xxxx ch2 kwh=yyy aux1 kwh=zzz etc.
Mar 23 9pm Ch1 kwh=xxxx ch2 kwh
Mar 24 8am Ch1 kwh=xxx etc.
I don't mean this exact form of output, but just an example of the kind of data I'm looking for from the db.
So, in guesswork-form, it'd give me:
Mar 23 8am Ch1 kwh=xxxx ch2 kwh=yyy aux1 kwh=zzz etc.
Mar 23 9pm Ch1 kwh=xxxx ch2 kwh
Mar 24 8am Ch1 kwh=xxx etc.
I don't mean this exact form of output, but just an example of the kind of data I'm looking for from the db.
I will post back in a few hours when I get home.. I think I know the answer but want to test it againt my database first.
I have a question about the Auto Refresh feature in the Brultech Dashboard software. If this has already been asked and answered, I apologize, but I couldn't find it...
I have a dedicated PC running the Brultech software. I usually just leave it on the "Live Data" tab. I want to be able to glance over and always see the most recent data. There is a button to set "Auto Refresh Status" to on, but all that does is refresh the current hour which may be several hours in the past. There is another button "View Current Time (Now)", but I don't know of a way to get it to automatically press this button at some regular interval.
Thanks,
Brian
I have a dedicated PC running the Brultech software. I usually just leave it on the "Live Data" tab. I want to be able to glance over and always see the most recent data. There is a button to set "Auto Refresh Status" to on, but all that does is refresh the current hour which may be several hours in the past. There is another button "View Current Time (Now)", but I don't know of a way to get it to automatically press this button at some regular interval.
Thanks,
Brian
