Correcting values in Domoticz database

The underlying database in Domoticz is a SQLite3 database. Make sure that you have the sqlite3 package installed.

sudo apt-get install sqlite3

Stop Domoticz just to be sure

sudo service domoticz.sh stop

Make a backup copy of the Domoticz database.

cd ~/domoticz
cp domoticz.db domoticz.db.bak

Start SQLite3 on the Domoticz DB

sqlite3 domoticz.db

Some useful commands
.header on
.mode column
.tables
.dump

Temperature values for today are stored in the Temperature table. Historic values are stored in the Temperature_Calendar.

SELECT * FROM Temperature_Calendar;

Show all values for a certain date or earlier

SELECT * FROM Temperature_Calendar WHERE [Date] = '2017-03-13';
SELECT * FROM Temperature_Calendar WHERE [Date] < '2017-03-13';

Show all values for a specific device. You can find the right DeviceRowID in the Domoticz interface by opening the graphs and looking at the values in the parameters of the URL.

SELECT * FROM Temperature_Calendar WHERE [DeviceRowID] = 8;

You can delete old values as follows (useful to test your query first with a SELECT):

SELECT * FROM Temperature_Calendar WHERE [DeviceRowID] = 107 AND [Date] < '2017-02-19';
DELETE FROM Temperature_Calendar WHERE [DeviceRowID] = 107 AND [Date] < '2017-02-19';

You can correct certain values by using the UPDATE statement for a device on a certain date.

UPDATE Temperature_Calendar SET [Temp_Max] = 20.9 WHERE [DeviceRowID] = 8 AND [Date] = '2017-03-13';

I'm not sure why it happens, but the records for the Sunday on which the summer time starts, get stored under the Saterday, which results in two Temperature_Calendar records with the same date. This translates in the graphs to a vertical jump. This can be fixed in the database by listing the entries for that date and updating the date of the second record.

SELECT * FROM Temperature_Calendar WHERE [Date] = '2017-03-25'
 
You have to choose one of the values that identifies the second record, because you have to tell the database which one to update (and you can't say the second stored record, AFAIK). I chose the Temp_Min value.

UPDATE Temperature_Calendar SET [Date] = '2017-03-26' WHERE [Date] = '2017-03-25' AND [Temp_Min] = 21.1;
Make sure that you filter on both DeviceRowID and on Date or you will be changing the value for all devices or for all dates. Exit SQLite3 with .quit.

.quit

Start Domoticz to check your results.

sudo service domoticz.sh start

Adding an https certificate to ownCloud on FreeNAS 9

1. Copy the certificate file (e.g. .crt) and the decrypted key file (e.g. .key.decrypted) to the jail in which ownCloud runs.
   
   cp {servername.crt,servername.key.decrypted} /mnt/tank/jails/owncloud_1/usr/pbi/owncloud-amd64/etc/apache24/
2. Click on the Jails icon in the FreeNAS top menu, select the ownCloud jail (owncloud_1) and open a Shell by pressing the shell icon at the bottom.
3. Edit the httpd-ssl.conf file in the 'extra' folder of the Apache config folder.
   
   vi /usr/pbi/owncloud-amd64/etc/apache24/extra/httpd-ssl.conf
4. Change the value of the SSLCertificateFile setting.
   
   SSLCertificateFile "/usr/pbi/owncloud-amd64/etc/apache24/servername.crt"
5. Change the value of the SSLCertificateKeyFile setting
   
   SSLCertificateKeyFile "/usr/pbi/owncloud-amd64/etc/apache24/servername.key.decrypted"
6. Restart Apache
   
   /usr/pbi/owncloud-amd64/etc/rc.d/apache24 restart

The certificate should be working.

Updating LSI Logic SAS 9211-8i IT Firmware

Some time ago I have cross-flashed my IBM ServeRAID M1015 SAS/SATA controller to an LSI SAS 9211-8i in IT mode. I did that using version 17 of the firmware. FreeNAS was now complaining for some time on the old version. It recommended upgrading to version 21 to be fully compatible with the driver. It turned out that version 20 was the newest version. I took the following steps to upgrade my firmware.

I download the newest firmware from Avago (part of Broadcom), who bought LSI Logic. Their site links to the Broadcom site.

Download Firmware

I downloaded the "9211-8i_Package_P20_IR_IT_Firmware_BIOS_for_MSDOS_Window". The ZIP contains both the 2118it.bin (in Firmware/HBA_9211_8i_IT) and sas2flsh.exe (in sas2flash_dos_rel).

Next I created a USB Drive that boots FreeDOS by using Rufus 2.11. I had some issues booting of the Flash Drive. Make sure that USB Compatibility Mode is set to auto (or enabled) and use F8 for the BIOS/UEFI boot menu while booting. I was not able to set the correct drive in the BIOS Setup itself, but I was able to choose it in the boot menu.

I copied both files (2118it.bin and sas2flsh.exe) to the root of the flash drive and booted FreeDOS. First I executed the command to see which SAS cards are in your system and the versions that go with it.

C:\>sas2flsh -listall
LSI Corporation SAS2 Flash Utility
Version 20.00.00.00 (2014.09.10)
Copyright (c) 2008-2014 LSI Corporation. All rights reserved

        Adapter Selected is a LSI SAS: SAS2008(B1)

Num Ctlr         FW Ver       NVDATA       x86-BIOS     PCI Addr
-------------------------------------------------------------------
0   SAS2008(B1)  17.00.01.00  11.00.00.07  07.33.00.00  00:02:00:00

        Finished Processing Commands Successfully.
        Exiting SAS2Flash

After that the firmware ROM needs to be deleted (do not reboot your system during or after this)

C:\>sas2flsh -c 0 -o -e 6

Then flash the new firmware.

C:\>sas2flsh -c 0 -f 2118it.bin

I did not flash the boot ROM with it, because I boot from a 'normal' SATA disk connected to the mainboard. If you need to (and I probably did that the first time I cross flashed the card), you can find the boot ROM in the same ZIP (sasbios_rel/mptsas2.rom) and use the following command:

C:\>sas2flsh -c 0 -f 2118it.bin -b mptsas2.rom

With sas2flsh -listall you can verify if the update was successful. If so, you should restart your system.

For CAT5/6 use T568B wiring

This is also one that I constantly keep forgetting: What is the best way of wiring my UTP/STP CAT5 / CAT5E / CAT6 cables. Apparently I was always using the T568B scheme, but every time I attach a new connector or socket I search the Internet, trying to find which is better T568A or T568B :).

From what I understand from my searches, it doesn't matter which one you choose, as long as you use it consistently. Some sites indicate that T568A is more suitable for homes and T568B for offices, but I have not found a solid reason for that. Furthermore, as this MeOnTech on YouTube shows, (almost?) all factory punched cables are T568B. (Which can also perfectly work in a T568A socket ;))

OpenWRT on Cisco Linksys E2000 and back to stock firmware

The goal of my effort was to create two separate WLAN SSID's that linked to two different VLAN's on the Linksys E2000. I did not get it working at that time and reverted back to the original firmware. This article describes my painful (took me a lot of time to find things out) journey.

Background

I replaced my Linksys / Cisco E2000 by a TP-Link TL-WA901ND, because I wasn't really satisfied with the coverage of the Linksys / Cisco E2000 and I wanted to have a separate SSID for guest Wifi access in my house. The TP-Link supports multiple SSID's and VLANning out of the box.



For devices that still are in their warranty period, I prefer to stay with stock firmware as long as it still does what I'd like it to do (hmmm, the clock is way off and I still can't find the NTP settings...).

I still have a purpose for the E2000, as I would like to have some better coverage in the attic. The stock firmware however does not support VLAN's. I also had problems configuring it so that it behaved as 'just' an Access Point. I was able to configure it as an access point, but that immediately disabled access to the interface. If I wanted to change the config, I had to reset it and configure it all over again. I never found out what caused it. That's why I wanted to play around with OpenWRT

 

 'Flashing' the Cisco Linksys E2000 with OpenWRT

To install OpenWRT on the E2000, I, of course, read the OpenWRT wiki page on the Linksys E2000. And the page on the WRT320N v2.0, as it was based on the same hardware according to the wiki. And the page on flashing using the TFTP server in the bootloader. But nothing worked.

As a last resort, I tried to upload the firmware using the standard 'Update Firmware' functionality of the stock Cisco firmware as is described on the OpenWRT Linksys WRT320N/E2000 page (huh, another page?). Much to my surprise, this worked. I uploaded openwrt-e2000_v1-squashfs.bin. Unfortunately, I didn't not create this blogposting right away, but I think I got it from here.

After that, you can telnet to 192.168.1.1, set a root password and replace telnet with SSH, see first login. You can use the normal reset procedure (press & hold the reset button for ~5 s) to revert the config to the start state (if the management interface becomes unreachable for instance).

 

Configuring OpenWRT

Multiple SSIDs and 802.11n are not supported by the b43 driver

The Linksys E2000 V1 is powered by a Broadcom BCM4716 System on a Chip. According to Linux Wireless drivers (b43) page, it is a BCMA SoC. This leaves you with two drivers for the E2000, either the default b43 driver or the brcmsmac driver. I wasn't able to install the brcmsmac driver, using opkg on Chaos Calmer (Bleeding Edge, r42687), due to problems in dependencies (because the packages are updated frequently and expect a newer kernel). I haven't put much effort in that, as I haven't seen any positive results when someone got it installed (see here and here). The problems with the b43 driver are:

1. I wasn't able to get 802.11n working, only 802.11g. That's of course quite a dealbreaker.

2. I wasn't able to get multiple SSIDs in the air. If I configured multiple SSIDs, either the second one in the config file showed up, or no SSID showed up at all (probably depending on the OpenWRT version)

 

Bricking and Unbricking

When I was initially done with installing and configuring the trunk version. I wanted to install a stable version, as I wasn't planning on flashing the device daily to patch any security problems.

I downloaded Attitude Adjustment, checked the integrity and flashed it, using sysupgrade:

wget http://downloads.openwrt.org/attitude_adjustment/12.09/brcm47xx/generic/openwrt-brcm47xx-squashfs.trx

sysupgrade -v /tmp/openwrt-brcm47xx-squashfs.trx

Unfortunately, after that flash, the device wasn't reachable anymore over ethernet. I tried to reset it and even performed the 30-30-30 reset which should only work on dd-wrt (?). That didn't help. Also, trying to flash the firmware using tftp still did not work.

The only way to unbrick it, was to open the E2000, solder wires to the connections for the serial port and connect that to a TTL to USB cable to get console access. A lot of information for this (such as the pin layout for serial port) is in this thread on the dd-wrt forum.

As this was my first attempt to solder something to a PCB and it worked, I guess it doesn't require much precision (as you can see). I used an Ada Fruit USB to Serial TTL cable (PL2303TA chipset).

With that console access, I found out that my ethernet ports were not functioning at all. I wasn't able to find out what was the cause of this. The WLAN was functioning however (but since I reset it, the default config was to disable WLAN). I could turn on the WLAN and, using Linux on my laptop, use netcat to send the trunk version of OpenWRT to the E2000. Once that succeeded, I could successfully flash the trunk version and it worked again!

I am still wondering what I could've done if I had no wireless, but only the console. I found all kinds of methods, but they basically used z-, y-, or xmodem or kermit to transfer files using the console. But the default OpenWRT image does not have these tools installed (at least, I didn't find them).

The console also allowed me to see why the tftp flashing wasn't working. Apparently, if I timed it right (it was kinda precise, it only worked for me when I had the cable inserted in the port 2 (coincidence?) and started the Windows TFTP client at the same time as a I turned the device on)  the E2000 received the firmware, but it rejected it. I tried it with a couple of versions, including the default Linksys firmware, but also trailed and non-trailed builds of OpenWRT: I couldn't get it to work.

Conclusion

Finally, I reflashed the original firmware. There was a new version, that at least got rid of the bug that I was experiencing which caused me to not be able to login anymore after I had configured it.


I followed the steps on the OpenWRT wiki page on 'uninstalling': I downloaded the latest firmware from the Linksys site and cut of the header as indicated:

dd bs=32 skip=1 if=original.bin of=original.trx

I used sysupgrade to flash the E2000 to the stock firmware.

Adding non-indexed folders on Windows shares to a Windows Library

I've traded in my Windows Server 2012 domain for a FreeNAS server. One of the many advantages is that you can disable Offline Files completely. Unfortunately, you need Offline Files for being able to add shares as folders in your Windows 7/8 libraries. If you just add a shared folder to your library, you will get the error:

This network location can't be included because it's not indexed.

Apparently, you can't choose to do the indexing on your Windows 7/8 system and that's that.
It took me some time to come up with a good solution, but the guys and girls at My Digital Life have put up a workaround. See the lower half of this page.
Here are the steps:

1. Create a dummy folder with any name. You can use the same name with the network folder name.
2. Add and include the dummy folder into the Library.
3. Delete the dummy folder (it won’t be removed from the Library).
4. Open an elevated Command Prompt window (Run as Administrator), and use the following command to make a symbolic link which links the dummy folder to the network location:
   
   mklink /d "\full\path\to\dummy\folder" "\\full\path\to\remote\folder"
   
   Change the "\full\path\to\dummy\folder" and "\\full\path\to\remote\folder" to actual full path to the folders.
5. Visit the Library, and the contents from the non-indexed remote location linked via symbolic link is now appearing inside the Library.

If you want to remove the link, because you want to update it, for instance, just use rmdir "link"

Bootstrap of CMake fails on (n)curses

When I try to bootstrap CMake (regardless of parameters for as far as I can see) it fails with the following error:

CMake Error: The following variables are used in this project, but they are set to NOTFOUND.
Please set them or make sure they are set and tested correctly in the CMake files:
CURSES_CURSES_H_PATH
   used as include directory in directory /usr/src/cmake-2.8.6/Source/CursesDialog/form
   used as include directory in directory /usr/src/cmake-2.8.6/Source

Although it can find libcurses...

-- Looking for wsyncup in /usr/lib/libcurses.so
-- Looking for wsyncup in /usr/lib/libcurses.so - found

The problem seems to lie in the fact that my curses.h header file is in /usr/include/ncursesw instead of in /usr/include. It would seem that CMake needs curses to build and cannot use ncurses... Strangely, I've compiled ncurses conform Linux From Scratch and which places the headers in that subfolder (ncursesw). Compiling non-wide versions didn't work either, as the headers are placed in /usr/include/ncurses.  CMake is part of Beyond Linux From Scratch, but I don't see any workarounds there, so I'm puzzled why BLFS doesn't encounter the same problems...

I've come up with a workaround that works for my situation, but I don't know if this is a good solution or will cause problems in the future...

sed -i "s#FIND_FILE(CURSES_HAVE_CURSES_H curses.h )#FIND_FILE(CURSES_HAVE_CURSES_H curses.h HINTS /usr/include/ncursesw )#" Modules/FindCurses.cmake
sed -i "s#FIND_PATH(CURSES_CURSES_H_PATH curses.h )#FIND_PATH(CURSES_CURSES_H_PATH curses.h HINTS /usr/include/ncursesw )#" Modules/FindCurses.cmake

I've hinted the FindCurses script to look for curses.h in the /usr/include/ncursesw folder.