External logging describes the possibility of creating a data image. You can log to a database as well as to a file system.
External logging is always set to the entire data source with all the variables it contains. There are 2 options for external logging:
- Data image
A deduction of the current value of the variable from the data source is created.
- Data log
A log is created which shows the changes to the variables.
External logging to the database
You can add additional interfaces to your QuickHMI system with just a few clicks.
The interface offers QuickHMI developers an easy way to provide the user with an interface with the following features via an existing database:
- Data archiver
All data from the end devices accumulating in QuickHMI is written to tables in the database here.
- Data image
Complete image of the variable with current value and time of the last change.
For what purposes can I use the database interface?
There are countless possible uses for the interface.
It is always available to you if you want to access QuickHMI or the connected devices such as PLCs, MODBUS participants or similar from “outside”. You can also use the data for external evaluations, charts, reports or simply for archiving purposes.
What do I need to operate a database interface?
Before a database interface can be operated, a database connection must be created.
You can find out how to do this in the chapter “Creating database connections“.
To operate the QuickHMI database interface, you need a customer-provided and administered database and a corresponding JDBC driver.
Depending on the amount of data generated and taking into account the storage time, it is also possible to use the free express versions.
The database must be accessible from the QuickHMI processes and communication must not be interrupted by firewalls or similar.
The network connection between QuickHMI system and database should be realized with a speed of 100Mbit/s. Lower transmission values may mean that the archive data cannot be written with the desired performance.
Please note: QuickHMI does not provide a database server or database licenses, but offers the connection of an existing database. The necessary database and/or access licenses must be available to the user/customer.
If, for various reasons, the QuickHMI system provides more data than can be written to the archive, the system temporarily stores this data in the working memory and processes it one after the other. However, if this internally stored data volume exceeds a threshold value of 50,000 data records per data source, no new data records are accepted, but are discarded.
The problem described above can occur, for example, if the hardware is otherwise underutilized or too few resources are available (e.g. virtual server).
For these reasons, please check the utilization and performance of the target server system before using it productively.
In addition, a sufficient amount of free storage space must be made available on the target database. A general recommendation can only be made to the extent that 20 GB of free storage space should be available when the archive functionality is activated. An exact specification depends on the number of variables used and the intensity and quantity of variable value changes.
Making settings for the database interface
You can make the following settings for the interface:
- Connection to the target database
- Switching the archive functionality on or off
- Switching the provision of a variable image on or off (Image)
To create and archive data sources, click on 
“Data sources” in the Project Explorer under Communication and select “Add data source” or “Edit data source“.
After determining the specifications, select “External logging“.
Here you now have the options “Write data log” and “Write data image“.
Data log
The data log records every change to a variable. With a high number of variables and variable changes, a lot of memory resources can be required for the log very quickly. If you do not need the data log, you can save memory resources by deactivating the log.
Data image
The data image always has only one entry, namely the last value of the variable.
Selection of variables
You also have the option of deciding which variables are logged and which are not.
The “Blacklist” and “Whitelist” modes are available for this purpose, which you can find in the “Variables” section of external logging.
With the “Blacklist“, you can use the button on the right to select the variables available in the project via a checkbox.
The selected variables are then NOT logged. If you set the mode to “Whitelist“, the selection of variables is retained, but they are then logged. Of course, you can also select and deselect variables via the whitelist.
Once you have made all the relevant entries, click on “OK” to continue.
Data archiving Database interface
With the data archiver functionality, all data changes are logged in the archive tables. You can use this data for any purpose.
The data is stored in the QHMI_DATALOG table for this purpose.
There is a row within the table for each variable change.
Table structure:
| Column name | Column type | Primary key | Information |
| ID | NUMBER(*, 0) Not zero | ✓ | Consecutive ID (must be generated automatically by the database system (AUTO_INCREMENT)) |
| TS | TIMESTAMP(6) Not zero | Time, timestamp | |
| DATASOURCE | VARCHAR2(50) Not zero | Name of the data source | |
| DATATYPE | VARCHAR2(50) Not zero | Data type | |
| VARNAME | VARCHAR2(100) Not zero | Name of the variable | |
| VALUE | VARCHAR2(2048) Zero | Value of the variable | |
| QUALITY | VARCHAR2(50) Zero | Quality (“GOOD” if the QuickHMI connection to the target device is working correctly or “BAD” if problems, e.g. timeouts, occur). | |
| MESSAGE | VARCHAR2(1024) Zero | Message |
Current data screen Database interface
Only required if the“Create image” option is activated.
You can use the data image to access the QuickHMI system’s variable pool via external applications and read out current statuses.
The data is stored in the QHMI_DATAIMAGE table for this purpose.
There is one row within the table for each variable.
Table structure:
| Column name | Column type | Primary key | Information |
| TS | TIMESTAMP(6) Not zero | Time, timestamp | |
| DATASOURCE | VARCHAR2(50) Not zero | Name of the data source | |
| DATATYPE | VARCHAR2(50) Not zero | Data type | |
| VARNAME | VARCHAR2(100) Not zero | Name of the variable | |
| VALUE | VARCHAR2(2048) Zero | Value of the variable | |
| QUALITY | VARCHAR2(50) Zero | Quality (“GOOD” if the QuickHMI connection to the target device is working correctly or “BAD” if problems occur, e.g. timeouts). |
Write back database interface
Only required if the“Write back” option is activated.
The“Write back” table can be used to write data from the table (QHMI_DATAWRITEBACK) back to the data source.
If a new data record is created in this table, it is processed by the QuickHMI system and removed from this table after processing.
The QuickHMI system writes the value taken from the data record back to the underlying data source.
Table structure:
| Column name | Column type | Primary key | Information |
| DATASOURCE | VARCHAR2(50) Not zero | ✓ | Name of the data source |
| VARNAME | VARCHAR2(100) Not zero | ✓ | Name of the variable |
| VALUE | VARCHAR2(2048) Not Null | Value to be written |
External logging in the file system
The interface in the file system offers QuickHMI developers an easy way to provide the user with an interface with the following features via the file system:
- Data archiver
Here, all data from the end devices accumulating in QuickHMI is written to the file system in consecutive files on a daily basis.
The maximum file size and the maximum number of files can be set.
- Data image
Complete image of the variables with current value and time of last change in one file per data source.
- Optional encryption
Accumulating data is stored in encrypted form. Tools for easy decryption are included.
- Automatic reorganization of data
For example, an external application can access the system’s database via this interface.
For optimum protection, all data can also be stored in 256-bit AES encryption. Only the user/application that has the freely definable encryption key has access here.
For what purposes can I use the file system interface?
There are countless possible uses for the interface. It is always available to you if you want to access QuickHMI or the connected devices such as PLCs, MODBUS participants or similar from “outside”. You can also use the data for external evaluations, charts, reports or simply for archiving purposes.
For example, a self-programmed application can access the system’s database via this interface.
Thanks to its universality, you no longer need to familiarize yourself with various scripting languages, but can use a programming environment of your choice.
How do I have to prepare the file system interface?
You must provide the QuickHMI system with a fixed, accessible folder in the file system. The folder must exist. However, the system will also try,
create the target folder if it does not exist. The processes require read, write and delete rights within this folder. If these rights are
is not available, malfunctions may occur with regard to the interface processes.
In particular, the deletion rights must be available. Otherwise, the reorganization functions will not be able to remove old files and this can lead to problems, including a crash of the operating system, due to full use of the storage space on the data carrier. The same applies if the files are permanently locked by external processes. Please also check during operation that sufficient storage space is available on the target data carrier.
The rights must be available for current Windows users during the development time so that the data can be written and read in the editor’s runtime mode.
In addition, the above-mentioned rights are required during runtime operation for the user under which the data source services are running.
Please note when preparing the target system:
Archiving to the file system is highly performant and has been tested with standard hardware and a data volume of 1000 variable changes per 100ms without any problems.
However, if for various reasons the QuickHMI system provides more data than can be written to the archive, the system temporarily stores this data in the working memory and processes it one after the other.
However, if this internally stored data volume exceeds a threshold value of 50,000 data records per data source, no new data records are accepted, but discarded.
The problem described can occur, for example, if the hardware is otherwise underutilized or if too few resources are provided (e.g. virtual server). For this reason, please check the utilization of the target system before using it productively. In addition, a sufficient amount of free storage space must be made available on the target data carrier.
A general recommendation can only be made to the extent that 20 GB of free memory should be available. An exact specification depends on the number of variables used and the intensity and quantity of variable value changes.
Making settings for the file system interface
You can make the following settings for the file system interface:
- Target path (Please note that the target path must also exist on the server).
- Switching the archive functionality on or off
- (option) Maximum number of archive files within the target folder. If the maximum number is exceeded, the oldest archive files are automatically deleted.
- (option) The maximum age of the archive files after creation in hours. If the specified age of the archive files is exceeded, these files are automatically deleted.
- Maximum size of the archive files in MB. When the specified size has been reached, a new file with the following sequence number is written. If no sizes are specified, the maximum file size is 2GB.
- Switch automatic reorganization within the archive on or off. If deactivated, the listed deletion functions are not executed. In this case, the user must delete the files themselves.
- Enable or disable the provision of a variable image, optionally in CSV or XML format.
- Switching the receipt of write commands via the file system on or off
- Switching file encryption on or off
- (Optional) Transfer of a password for encryption. When the encryption is switched on, the password must be provided. Please keep the password out of the reach of others and do not pass it on.
To create and archive data sources, click on “Data sources” in the Project Explorer under Communication and then select “Add data source” or “Edit data source“.
After determining the specifications, select “External logging“.
In the window that opens, you can now select “File” by activating the corresponding checkbox.
Then select the path in which the file is to be saved and specify whether a data log is to be written or not.
You can also enter an encryption password and use the “Blacklist” and “Whitelist” modes to select which variables should be logged and which should not.
A brief description of the data log and data image, as well as the blacklist and whitelist, can be found in the chapter “Making settings for the database interface“.
Click on “OK” to continue.
Data archiving file system interface
With the data archiver functionality, all data changes are logged in the file system. You can use this data for any purpose.
For this purpose, files are created according to the following naming convention:
<date>_<counter 5-digit>_<data source name>.log
e.g. 20140112_00001_DataSource1.log
Optionally encrypted archive files have the same file name with the extension *.logx. If the file reaches the specified maximum size or 2 GB, a new file is created with a subsequent counter.
The fields in the files are separated by semicolons. The column names are in the first line.
The following information is written:
- Time with milliseconds
- Sender (name of the data source)
- Data type
- Name of the variable
- Current value
- Quality (“GOOD” if the QuickHMI connection to the target device is working correctly or “BAD” if problems occur, e.g. timeouts).
Current data screen file system interface
You can use the data image to access the QuickHMI system’s variable pool via external applications and read out current statuses.
There are two options for outputting the data image: in text format or as an XML file.
Structure of data image in text format
Files with the following naming convention are created for this purpose:
<Data source name>.dat
e.g. DataSource1.dat
Optionally encrypted image files have the same name with the extension *.datx.
The fields in the files are separated by semicolons. The column names are in the first line.
There is one line within the file for each variable. The following information is written:
- Time with milliseconds
- Sender (name of the data source)
- Data type
- Name of the variable
- Current value
- Quality (“GOOD” if the QuickHMI connection to the target device is working correctly or “BAD” if problems occur, e.g. timeouts).
Structure of data image in XML format
Files with the following naming convention are created for this purpose:
<Data source name>.xml
e.g. DataSource1.xml
In xml format, optionally encrypted image files have the same file name with the same extension *.xml.
There is one line within the file for each variable. The following information is written:
- Flag whether data is encrypted
- Time with milliseconds
- Sender (name of the data source)
- Data type
- Name of the variable
- Current value
- Quality (“GOOD” if the QuickHMI connection to the target device is working correctly or “BAD” if problems occur, e.g. timeouts).
For encrypted files, the XML format is still retained, only the field contents are encrypted.
Encryption and decryption
In order to provide optimum data security, the QuickHMI file interfaces are equipped with optional encryption.
Encryption is carried out directly once you have configured the necessary settings within the data sources.
Please note: You must assign a password when setting up the encryption. Please keep this password in a safe place! If it is lost, the encrypted files can no longer be opened!
Even we can no longer help in this case. The encryption routines require CPU resources!
When using the program, plan for a slightly higher computing power, especially if many variables are changed at very short intervals. However, you can test the load in advance in the editor during runtime mode.
A command line program is included with your QuickHMI system for encryption and decryption.
You can find it at [QuickHMI-Home]/qhmisecu.exe within the Editor or server installation.
You can perform the following actions:
Decrypt log files in logx format or image files in datx format
To do this, call up the qhmisecu.exe program with the following parameters:qhmisecu.exe -d <source file> <target file> <your password>
The unencrypted target file is then created.
Decrypt image files in xml format
To do this, call up the qhmisecu.exe program with the following parameters:qhmisecu.exe -x <source file> <target file> <your password>
The unencrypted target file is then created.
External logging for internal data sources
In QuickHMI, you have the option of carrying out external logging not only for external data sources, but also for some internal data sources.
Click on “Data sources” in the editor to access an overview of all data sources created to date. Here you can also display the internal data sources using the button.
External logging now also works with the internal data source and the system data source.
These can be opened by double-clicking to activate external logging.
The rest of the external logging process is the same as described in the previous chapters.
