Edit 

Extracting Data from an SMA WebBox

Note: This guide was written in 2010 and may not be compatible with newer versions of SMA webbox hardware.

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.

System Description

Directory Structure

The SMAwebBox_dataExtractor.php is organized as it follows:

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:

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:

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:

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

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

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:

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]['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)

Scheduled Tasks

Create a new "scheduled task." The parameters must be set to:

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:

 Troubleshooting

FAQ

Learn
OpenEnergyMonitor
  Electricity Monitoring
 AC Power Theory
 CT Sensors
 Voltage Sensing
 Current & Voltage
 Networking
 Pulse Counting
 Temperature
 Timeseries
 Emoncms Internals
 Using the Arduino IDE
 Other Software
  Sustainable Energy
 Energy
 Zero Carbon Energy Model
 Building Energy Modelling
 Tools
  PV Diversion
 Introduction
 Background
 MK2 PV Diverter
 PLL Diverter
  Other Utilities
 Water & Gas