Extracting Data from an SMA WebBox

---

Download SMAwebBox_dataExtractor.tar.gz

General Overview

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.

Elements of the System

In this section we describe the applications used.

• MySql Server: Database manager.
• PhpMyAdmin: GUI interface between a user and MySql Server
• SMA WebBox PushFTP function: Datalogger that collects data from SMA devices. The PushFTP function can be configured to upload data to an FTP server. We don´t cover the configuration here, as you can find iinstructions in the webBox user guide
• Filezilla Server:  FTP server program.
• SMAwebBox_dataExtractor: Application which checks if there are new files uploaded from the webBox. If this is the case, it unzips them, resulting in the xml files which contain the data. The application extracts the data and inserts it in the database.
• PHP: "SMAwebBox_dataExtractor" is written in PHP. Language commonly used for writing web document code to create dynamic HTML documents. Useful for developing OS-independent software. The applications can easily be launched from the PHP Command Line Interface (CLI) instead of a web browser.
• Scheduled tasks: Windows application that runs the SMAwebBox_dataExtractor.php according to a user defined schedule.

System Description

Directory Structure

The SMAwebBox_dataExtractor.php is organized as it follows:

• SMAwebBox_dataExtractor: Application root
• SMAwebBox_dataExtractor/pushFTP_files: Where the webBox pushFTP function uploads the files
• SMAwebBox_dataExtractor/zipFILES: Location the application moves the original files to (from pushFTP_files) after the data has been extracted
• SMAwebBox_dataExtractor r/unextractedFILES: When the application has a problem unzipping a file or inserting the data in the database, the file is moved to this directory
• SMAwebBox_dataExtractor/miscelanea: Optional information. e.g. this documentation)
• SMAwebBox_dataExtractor/log: Where the log files are stored  

Database

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". 

PushFTP File Format

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

XML File Format

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.

Log file

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:

• Element Info
  • Child elements: Created, Culture
• Element Event
  • Child elements: Key, DateTime, EventType, AccessLevel, Category, Device, Module, MessageCode, MessageArgs, Message.

The useful data in the XML files are the child elements of "Event". All are inserted in the database.

Data file

Filename format: the word Mean, date and time when the file was generated and file extension.
E.g.: Mean.20100111_220028.xml

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:

• Element "Info"
  • Child elements: Created, Culture
• Element MeanPublic
  • Child elements: Key, First, Last, Min, Max, Mean, Base, Period, TimeStamp.

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".

Launching  "SMAwebBox_dataExtractor"

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.

  Log files

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:

• Application start and stop
• Connection to the database
• A file is moved, deleted or unzipped
• XML data successfully inserted in database
• Any error in the above listed actions, and some helpful info

SMAwebBox_dataExtractor code

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.

When connecting a new device to the webBox

 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:

..\SMAwebBox_dataExtractor\miscelanea\files_for_inserting_tables_in_DB

It is highly recommended you use the original files from your webBox that it uploaded to the ftp server.

Installation Instructions

XAMPP

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.

SMAwebBox_dataExtractor

If you are reading this it means you have downloaded and unzipped the "SMAwebBox_dataExtractor" package. Copy it to:

C:\Program Files\apachefriends\xampp\htdocs\

So that you have:

C:\Program Files\apachefriends\xampp\htdocs\SMAwebBox_dataExtractor\

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.

Configuring Filezilla Server

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):

• User: webBox, password: userpassword
• Shared folders (for the given user): C:\Program Files\apachefriends\xampp\htdocs\SMAwebBox_dataExtractor\pushFTP_files
• File rights: read and write
• Directory rights: list and subdirs

For security reasons you can set another user with all rights:

• User: admin, password: adminpassword
• Shared folder: C:\Program Files\apachefriends\xampp\htdocs\

webBox PushFTP Function

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.

mySQL Server Users

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:

• User: your_user
• Password: your_password
• Privileges: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP

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.

PHPMyAdmin

"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]['extension'] = 'mysql'; --> $cfg['Servers'][$i]['extension'] = mysqli';

$cfg['Servers'][$i]['auth_type'] = 'config'; --> $cfg['Servers'][$i]['auth_type'] = 'http';

Database

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.

php.ini

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)

• extension_dir = ".\ext"
• ;extension=php_mysql.dll
• extension=php_mysqli.dll

Scheduled Tasks

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"

• "Run when the session starts"
• Set the task to run every day
• Repeat the task every minute

How to Configure SMAwebBox_dataExtractor.php

You can find the configuration file in

C:\Program Files\apachefriends\xampp\htdocs\SMAwebBox_dataExtractor\SMAwebBox_dataExtractor.conf.php

Open it and write the data indicated which is:

• DB_SERVER: location of the MySQL server, typically "localhost"
• DB_NAME: the name you gave the database
• DB_USER: the user you set up in the section "mySQL Server users"
• DB_PASSWORD: The MySQL user's password

 Troubleshooting

• 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)

  • The user doesn't have write privileges in that directory. You need to change them.

FAQ

• 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]