Demo: Storing data in InfluxDB with ogamma visual Logger for OPC, with processing and visualization.

This article describes an end-to-end solution built with open source components InfluxDb and Grafana in conjunction with ogamma Visual Logger for OPC, to collect industrial process control data, analyze it in streaming mode, and visualize in a dashboard.

Problem statement.

For demo purposes, we are using some hypothetical industrial Pump, which has few variables monitored and real-time values for them are available over the OPC UA Server interface:

  • Input Pressure;
  • Output Pressure;
  • Temperature;
  • Power status.

In a dashboard, we would like to visualize the following information:

  • Current values of these variables,
  • Historical values in a graph,
  • Calculate the difference between the average values of input and output pressure and display it,
  • Periodically check pressure difference calculated above, and generate notifications if it is out of desired range, and display them in the dashboard.

Solution.

The context diagram of the proposed solution for this problem is shown below:

DemoPumpSetupDiagram

The following sections describe all the details about what components are used and how they interact.

OPC UA Data Source.

As a data source providing values for monitored Pump variables we are using Prosys OPC UA Simulation Server. It differs from other available simulation servers in that you can build custom OPC UA address space up to your specific requirements, and values for variables can be automatically generated according to selected patterns like random, sinusoid, sawtooth, and so on.

Time-Series Database and stream analytics engine.

As a time-series database, InfluxDB is used. Why? Because with it you can, first, obviously, store data in a high-efficient way, and second, most important: it supports real-time stream analytics. So you can analyze data using Flux scripts according to your requirements and provide results on the fly.

Moving data from OPC UA Server to InfluxDB.

To collect data from OPC UA Servers and push it into the InfluxDB time-series database, ogamma Visual Logger for OPC is used. It can be deployed in Windows, Linux or Docker, has web-based configuration GUI, high-performant, and does not require much hardware resources (written in C++).

Visualization of information in dashboards.

For this purpose another open-source solution Grafana is used. Alternatively, it is also possible to build dashboards in InfluxDB, using its component Chronograf.

The end result: screenshot.

DemoPump

 

The end result: screen recording.

DemoPump

Technical Details.

Configuring the Prosys OPC UA Simulation Server.

An instance of the Prosys OPC UA Simulation server is installed in Windows Virtual Machine.

Under Objects folder, for this demo purposes, objects and variables are created:

Objects And Variables

 

From the OPC UA Client perspective the address space looks like this:

 AddressSpaceFromOpcUaClient

 

Variable configuration settings are captured in the following below screenshots:

Variable Simulation Settings Variable Simulation Settings Variable Simulation Settings Variable Simulation Settings

 

Configuring ogamma visual Logger for OPC to connect to the server in secured mode.

First, a new OPC UA Server node should be created in the Address Space panel. Note that the Security mode is set to Sign&Encrypt. It means that some steps to configure trust on the server side will be required.


After adding the OPC UA Server node, the initial attempt to connect will fail. On the server-side, gamma Visual Logger's OPC UA application instance certificate will be saved in the rejected certificates folder. From the Prosys OPC UA Simulation Server GUI, it needs to be marked as trusted. This can be done after switching to the Expert Mode (menu Options / Switch to Expert Mode), via the Certificates tab page:

Just trusting the ogamma Visual Logger instance certificate is not enough though. You will need to:

  • download its CA certificate (via menu Settings / Download Certificates / CA Certificate) and save it in the server-side into folder C:\Users\Administrator\.prosysopc\prosys-opc-ua-simulation-server\PKI\CA\certs, 
  • download CRL (via menu Settings / Download Certificates / CA CRL) and save it in the server-side into folder C:\Users\Administrator\.prosysopc\prosys-opc-ua-simulation-server\PKI\CA\crl. 

Note: Actual folder where certificates are located can be different in your installation. Therefore, open the folder using Prosys OPC UA Simulation Server GUI, via menu Certificates / Open in file Explorer.

Download Certificates

When the certificate trust configuration is correct, ogamma Visual Logger for OPC should be able to connect to the server. To verify that, you can try to browse it by expanding nodes in the Address Space panel.

Configuring ogamma visual Logger for OPC to log desired variables.

Select nodes from the Address Space panel and add them to the Logged Variables table by clicking on the button Log. If everything is correct, in the Status column an icon with a checkmark should be displayed.

 

Logged Variables

 

Configuring ogamma Visual Logger for OPC to connect to the InfluxDB instance.

First, add a new record in the time-series database list (menu Settings / Time-Series Databases). Set value of the field Type to InfluxDB 2.0, and modify fields Host, Port, Use Secure Mode, and JSON. In the last field (JSON) set option "measurement" to "[TN]", and make sure that options organization, bucket, token are set correctly. 

Connection To InfluxDB 1

Connection To InfluxDB 2 

Verify that connection settings are correct by clicking on the button Test Connection.

Now real-time data values are logged into the InfluxDB database.

Analysis of variable values in streaming mode and generating notifications.

For stream analysis, the InfluxDB Tasks feature is used. Created using this feature Flux script every 5 seconds calculates mean of the values for variables Input Pressure and Output Pressure and their difference. Then based on the value of this difference, a notification message is generated. If the difference is below the specified thread, the message level is set to critical, otherwise, set to warn, info, or ok, depending on value. Then notification is written into special system bucket _monitoring, with _measurement set to statuses.

 InfluxDB Tasks

 

Full Flux script code is quoted below:

import "influxdata/influxdb/monitor"

option task = {name: "Check Pump Pressure", every: 5s, offset: 1s}

currentTime = now()

join(tables: {ip: from(bucket: "ogamma")
	|> range(start: -task.every)
	|> filter(fn: (r) =>
		(r._measurement == "InputPressure" and r.unit == "Pump" and r._field == "v"))
	|> drop(columns: ["_source_timestamp"])
	|> mean(column: "_value")
	|> map(fn: (r) =>
		({
			_time: currentTime,
			_value: r._value,
			unit: r.unit,
			_measurement: "OutputPressure",
		})), op: from(bucket: "ogamma")
	|> range(start: -task.every)
	|> filter(fn: (r) =>
		(r._measurement == "OutputPressure" and r.unit == "Pump" and r._field == "v"))
	|> drop(columns: ["_source_timestamp"])
	|> mean(column: "_value")
	|> map(fn: (r) =>
		({
			_time: currentTime,
			_value: r._value,
			unit: r.unit,
			_measurement: "OutputPressure",
		}))}, on: ["_time", "_measurement"])
	|> map(fn: (r) =>
		({
			_time: r._time,
			_pd: r._value_op - r._value_ip,
			unit: r.unit_op,
			_measurement: "OutputPressure",
		}))
	|> monitor.check(
		crit: (r) =>
			(r._pd < 10.0),
		warn: (r) =>
			(r._pd < 15.0),
		info: (r) =>
			(r._pd < 25.0),
		ok: (r) =>
			(r._pd >= 25.0),
		messageFn: (r) =>
			(if r._pd < 10.0 then "Critical alert!! error: Pressure difference is too low: ${string(v: r._pd)}!" else if r._pd < 15.0 then "Warning! Pressure difference is low: ${string(v: r._pd)}!" else if r._pd < 25.0 then "Pressure difference is: ${string(v: r._pd)}!" else "ok"),
		data: {
			_check_name: "Pressure difference check",
			_check_id: "pres_diff",
			_type: "threshold",
			tags: {unit: "Pump"},
			_measurement: "statuses",
		},
	)

 

Visualization of real-time and stream-processed data in Grafana.

Data source plugin Cloud Flux (InfluxDB) 2.0 needs to the installed in Grafana in order to read stored data. For details on how to install this plugin, refer to its home page.

After installing the plugin, the data source needs to be created to connect to the InfluxDB instance.

And then in each panel to retrieve desired data from InfluxDB Flux scripts are used. Description of panels and corresponding Flux script code quotes follow below. 

  1. Status.

    This panel displays the current status of the Pump, based on the value of the boolean type variable Power.

    from(bucket: "ogamma")
      |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
      |> filter(fn: (r) =>
        r._measurement == "Power" and
        r._field == "v"
      )
      |> last()
     >
  2. Input Pressure

    In this panel, the current value of the Input Pressure variable is displayed.

    from(bucket: "ogamma")
      |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
      |> filter(fn: (r) =>
        r._measurement == "InputPressure" and
        r._field == "v"
      )
  3. Output Pressure

    In this panel, the current value of the Output Pressure variable is displayed.
    from(bucket: "ogamma")
      |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
      |> filter(fn: (r) =>
        r._measurement == "OutputPressure" and
        r._field == "v"
      )
  4. Relative pressure

    In this panel, the current value of the calculated difference between Output and Input Pressure is displayed. Note that for each input and output pressure variables first the mean value is calculated, therefore the difference is not equal to the difference between current values.

    from(bucket: "_monitoring")
      |> range(start: -10s, stop:now())
      |> filter(fn: (r) =>
        r._measurement == "statuses" and
        r.unit == "Pump"
      )
      |> drop (columns:["_source_timestamp", "_check_id", "_check_name", "_start", "_stop"])
      
  5. Pump: Input and Output Pressure.

    In this panel, historical values for pressure variables for the latest display interval are displayed.
    from(bucket: "ogamma")
      |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
      |> filter(fn: (r) =>
        r._measurement == "InputPressure" and
        r._field == "v"
      )
  6.  Pump notifications.

    In this panel notifications generated as a result of the processing of streamed values are displayed. 
    from(bucket: "_monitoring")
      |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
      |> filter(fn: (r) =>
        r._measurement == "statuses" and
        r.unit == "Pump"
      )
      |> drop(columns: ["_source_timestamp"])
    
  7. Temperature

    In this panel, the historical values of the Temperature variable are displayed.
    from(bucket: "ogamma") |> range(start: v.timeRangeStart, stop:v.timeRangeStop) |> filter(fn: (r) => r._measurement == "Temperature" and r._field == "v" )

 The end result.

And in the end, we get this working dashboard in Grafana:

DemoPump