Note: This guide was written in 2010 and may not be compatible with newer versions of SMA webbox hardware.
The SMA "webBox" has the capacity for logging, web displaying and uploading to a server data from SMA devices connected to it. Typical SMA devices are inverters, controllers, backup power supplies, etc. SMA specializes in interactive grid-connected devices, i.e. devices that feed energy from renewable resources back to the grid.
The application "SMAwebBox_dataExtractor" has been developed under a GPL license for inserting in a database, the data that has been generated by the "webBox" device.
This document describes an environment that has been successfully used with the "SMAwebBox_dataExtractor". It aims to explain system operation, the various sub-systems, troubleshooting and more.
This documentation is for computers running Windows. This does not affect those who want to use the "SMAwebBox_dataExtractor" application with another operating system, as it is written in php. If that is the case, this documentation is still useful, as it explains how the "SMAwebBox_dataExtractor" works.
Every application used has a Linux versions available, (which might be slightly different) so reading the enire document can benefit Linux users as well. The only exception is the "scheduled tasks" application, Linux users will need to use the cron daemon. Applications installed as Windows services are equivalent to Linux daemons.
In this section we describe the applications used.
The SMAwebBox_dataExtractor.php is organized as it follows:
There is table for each device and for the webBox log. The name of each table is the serial number of the device, or "log".
The pushFTP function of the webBox uploads the files generated to a specified directory. The files are in zip format. Filenames are defined as: wbserialnumber (for example: wb150022199), date and time when the file was generated and the file extension. e.g: wb150022199.20100120-121948.zip
The zip files usually contain more than one compressed file. These files are also zip files. They contain the XML files where the data is stored
The data to be inserted in the database is in an XML file. The webBox generates two kind of XML files. One for the events it logs, and the other for device data.
Filename format: the word Log, date and time when the file was generated and file extension. E.g. Log.20100111_214534.xml
The information in the file can be for more than one event.
These files can be defined as:
The useful data in the XML files are the child elements of "Event". All are inserted in the database.
Filename format: the word Mean, date and time when the file was generated and file extension.
The webBox logs the data periodically (every 5, 10 or 15 minutes), and generates a "Mean" file which contains data from all the devices connected to the webBox.
These files can be defined as:
The file contents a "MeanPublic" element for every parameter of every device connected to the webBox. In this way the "Key" child element represents the device and parameter name, while the "Mean" child element contents the data associated to that parameter. In other words, from the "Key" element we know which table and field in the database to insert the "Mean" element in. The rest of the child elements are not used. For example: a device with a serial number of 1234 will have a "Key" element for the "Pac" parameter which is "1234:Pac".
To run the "SMAwebBox_dataExtractor.php" application, we use the PHP command line interface (CLI). We use "php-win.exe" because it allows us to run the application without the DOS box. The simplest command for launching an application is:
php-win.exe -f application.php -- parameters
Following the example above, the command for launching "SMAwebBox_dataExtractor.php" is:
php-win.exe" -f "C:\path\SMAwebBox_dataExtractor\SMAwebBox_dataExtractor_1.0.php"
There is a task programmed in the Wndows task scheduler that launches "SMAwebBox_dataExtractor" every minute.
This section refers to the log files that the "SMAwebBox_dataExtractor" generates - not to be confused with the log files generated by the webBox. The application generates a new log file every day.
The following information is logged:
This section gives a very brief description about how the main program and some of its functions work.
When the aplication is launched, it opens the directory the files were uploaded to by the webBox pushFTP function. It reads the directory from the beginning. If it finds a zip file, the file is unzipped to the same directory and moved or deleted (depending if it is the original uploaded file). If the file is an .xml the data its contents are read and inserted into the database, then the file is deleted. Once processing on a file is complete, the application processes the next zip file it finds. When the end of the directory is reached, it starts again from the beginning until all the files hav been moved or deleted. Most of the actions performed by the application are logged.
The code has been commented enough to make it easy to understand.
If a new device is connected to the webBox, it will store that device's data and send it through the pushFTP function. The "SMAwebBox_dataExtractor" will try to insert the data into a table in the database.
There should be a table in the database for every device connected to the webBox. When the "SMAwebBox_dataExtractor" tries to insert the data of the new device, the MySQL server will generate an error if the database table does not exist. "SMAwebBox_dataExtractor" will move that file to the "\unextracted_files" directory.
Thus, it is very important when a new device is connected to the webBox, a new database table is created for it. The name of this table must have the format: device_serialnumber. It must have a field for every parameter to be is logged. The parameter name and field name must be the same.
To make it easy, you can find some php files we have used to insert the tables of the original devices in the database. The files have comments pointing out the changes you need to do for creating the new tables. The files can be found in:
It is highly recommended you use the original files from your webBox that it uploaded to the ftp server.
This package includes most of the tools we need. It sets up Apache web server, MySql and PHP. like "phpMyAdmin"
XAMP is available here. It will automatically install and configure: Apache, MySQL Server, PHP, phpMyAdmin, and Filezilla Server.
If you are reading this it means you have downloaded and unzipped the "SMAwebBox_dataExtractor" package. Copy it to:
So that you have:
If, when you installed XAMPP, you changed the installation path, you need to take that into account.
This path uses the root directory for the web pages served by Apache.
In this directory, you must have the following directories: pushFTP_files, zipFILES, unextractedFILES and log.
Answer yes when the XAMP installation wizard asks you about installing it as a service. Ensure the service is configured to start when Wndows starts, and to restart in case of failure (control panel --> administrative tools --> services).
For security reasons, set a password for accessing the Filezilla server through the "Filezilla Server Interface".
The Filezilla Server should be listening on the default port, 21
The user account and folder the webBox pushFTP function uses for connecting to the Filezilla Server must be set up in both Filezilla server and the webBox):
For security reasons you can set another user with all rights:
Follow the webBox user manual for setting up this function. Use the same user, password and directories you specified when you set up the FileZilla server.
For security reasons, after installing XAMPP you should set a password for the "root" user.
Next, create the user the SMAwebBox_dataExtractor will use for accessing the database:
Both things can easily be done accessing localhost from a web-browser then going to phpMyAdmin
You may want to set up a user with only "SELECT" privileges, for browsing the database contents.
"pma" is the user phpMyAdmin uses to access the mySQL Server. For security reasons, you should change the pma user's password. The password can be changed with PHPMyAdmin.
You need to change the phpMyAdmin config file which is located in: C:\Program Files\apachefriends\xampp\phpMyAdmin\config.inc.php
Look for the line: $cfg['Servers'][$i]['controluser'] = 'pma'; and then underneath it, make the following change:
$cfg['Servers'][$i]['controlpass'] = ''; --> $cfg['Servers'][$i]['controlpass'] = 'pma_password';
You also need to make the following changes:
$cfg['Servers'][$i]['auth_type'] = 'config'; --> $cfg['Servers'][$i]['auth_type'] = 'http';
First, create the database with "phpMyAdmin." Give it any name you want.
Then, create one table for every device connected to the webBox. Refer to the section "When connecting a new device to the webBox," for instructions.
This is the PHP configuration file. The file is in: C:\Program Files\apachefriends\xampp\apache\bin\php.ini
Ensure the following lines are in the file (Note the presence or absense of the semicolon at the beginning of each line)
Create a new "scheduled task." The parameters must be set to:
Run: "C:\Archivos de programa\apachefriends\xampp\php\php-win.exe" -f "C:\Archivos de programa\apachefriends\xampp\htdocs\SMAwebBox_dataExtractor \push_ftp_extractor_1.0.php"
You can find the configuration file in
Open it and write the data indicated which is:
There are files in "\SMAwebBox_dataExtractor\unextracted_FILES." What does it mean? Do I need to do anything?
If there are any problems unzipping or extracting the contents of the files uploaded from the webBox, they are automatically moved to this directory. Depending on the nature of the problem, something might need to be done.
To find out why a file was not extracted, check the log files. Open the log which has the same date as when the file was moved to the "unextracted_FILES" folder. Then do a search for the name of the file.
If you find there has been an error, you need to fix it. Once the problem has been solved, you need to move the files from the "unextracted_FILES" directory to the "push_FTP" directory so the "SMAwebBox_dataExtractor" can try to unzip or extract the file again.
The log file error is: "problems with the query: Duplicate entry."
The database is configured such that there can´t be two registers with the same timestamp in a device table. As the error says, there is already a register with that timestamp. In this situation "SMAwebBox_dataExtractor" will not extract the rest of the data in that file and will move it to the "unextracted_FILES" directory.
Probably what happened: there was a problem inserting data from that file. You solved the problem and are trying to run the application on that file again. Before the original problem happened the application inserted some data from that file into the database and is trying to do it again. Compare the data that was about to be inserted (you can see it in the log file) with the database register that has the same timestamp. If this is the cause of the problem, you can solve it by deleting the duplicate from the database which will allow the application to insert it again.
The log file says: "problems with the query: Table '....' doesn't exist."
Have you connected a new device to the webBox, but not created a new table for it in the database? Read the "When connecting a new device to the webBox" section.
There are lots of files in the "\pushFTP_files" directory and they don´t move from there. What´s happening?
Check that the "SMAwebBox_dataExtractor" is being launched. For example you can check when today´s log file was last modified. If it was more than 5 minutes ago something is wrong. Check the "Scheduled tasks" section.
If that is not the problem, check the log file. If it says that the "file couldn´t be copied/deleted" verify the following:
The directory structure is corrupt (have a look in the "System description" section)
Why there is a file called "info.xml" in the "unextracted_files" directory?
Sometimes the webBox uploads this file. It contains information about the webBox ( such as serial number, MAC address, etc.). It isn´t a zip, "Mean" or "Log" file so "SMAwebBox_dataExtractor" moves it to this folder. It can be deleted, or simply ignored.
The Hard Disk is becoming full. What can I do?
You can make a backup copy of the "zipFILES" directory then empty it.
How do I check that everything is working correctly?
Tor verify the "SMAwebBox_dataExtractor" application is being launched, you can check today´s log file timestamp. The application is supposed to run every minute. So it should have been modified not more that one minute ago.
Verify there aren´t any files in the "unextracted_Files" directory. If any files are present, something needs attention.
Search the log files for the word error. Refer to the "troubleshooting" section in that case.
To verify the webBox is uploading files, look in the "pushFTP_files" directory. Take into account the "SMAwebBox_dataExtractor" moves the files to the "zipFILES" directory once they have been extracted, so check there too. Check when the newest file were "last modified" to get an idea about when the last files were uploaded.
Who can I talk with if I have a question?
This big mess was developed by Carlos Alonso Gabizó, you can contact me at: [email protected]