All posts by vogler

Allgemein, BigData/NoSQL, Frankenstein

QuestDB: My time series data’s new best friend? 📈

My first tests with QuestDB on 10 years of home automation data (1.4 billion rows) are promising.

👉 Fast ingestion of parquet files (~1 hour on an old Intel NUC i5)

I have stored my data in parquet files, one per month, and imported it with a simple Python script. The import on my really old Intel NUC i5 took only about one hour. I’ve never been able to do this so quickly with any other database.

🤔 Btw.: I think storing data in #parquet files, or any other open table format, like Apache Iceberg, is one of the best choices to keep data. Because it’s independent of a database engine.

👉 Familiar SQL syntax. I experienced that QuestDB has a powerful SQL engine. I converted some Postgres SQL statements to QuestDB without big issues or changes. And I love SQL 💚

👉 Great query response times – see image. Not a representative query, but still impressive speed.

👉 By using ZFS with compression the used disk space can be reduced to a good value.

Do you want to log your OPCUA data to QuestDB? I have added this option to the automation-gateway.com seven days ago.

Allgemein, WinCC OA

Node.JS for WinCC OA? And what about Java? GraalVM? Polyglot?

🥳 Last weekend I found some time to try out an upcoming feature in WinCC Open Architecture 3.20. With the Node.js integration you can write your business logic in JavaScript with native connectivity to WinCC OA. You can take full advantage of the Node.js ecosystem.

🧐 But I am a Java enthusiast and I love the JVM ecosystem. Have you ever heard about GraalVM? It is an advanced JDK written in Java. And it has a Node.js Runtime, which gives you the power of Node.js plus the power of polyglot programming, you can mix JavaScript with Java.

👍 And it turned out that the GraalVM Node.js Runtime also works with WinCC OA! It took me some time to figure out how the polyglot interoperability works, but now I have a first draft of a Java-Library which makes it easy to use Java and OA in the Node.js environment.

🤩 I can now use Java to develop great solutions with WinCC OA.

Allgemein, WinCC OA

WinCC OA & Node-Red Integration

It is very easy to get data from WinCC Open Architecture to NodeRed.

Add a new user to WinCC OA – System Management / Permission / User Administation.

We will use “node” as username.

Add Config Entry 

[wssServer]
httpsPort = 8449
resourceName = "/websocket"

Start Control Manager “wss.ctl -user <username>:” Note the trailing “:” !!

wss.ctl -user node:

Node-Red: Install Palette “node-red-contrib-winccoa”

You can now add a Node. In that example we will use the dpQuery node and use “SELECT ‘_online.._value’ FROM ‘Meter_Input_WattAct.'” as query. So we just query the online value of one tag.

You have to configure the Server by clicking on the pencil button. This points to the before started Websocket Control Manager and you have to set the username and password we have added in one of the previous steps.

Frankenstein, MQTT

OPC UA Node Tree to MQTT

With just 20 lines of configuration you can publish a OPCUA tree of values to MQTT …

in this example to the HiveMQ cloud … 👉 with the automation-gateway.com

It also supports PLC4x connected devices/plcs…

It can also publish values to Kafka or SQL databases…

git clone https://github.com/vogler75/automation-gateway.git
cd source\app
set GATEWAY_CONFIG=configs/config-opcua-mqtt.yaml
gradle run

Drivers:
  OpcUa:
  - Id: "demo"
    Enabled: true
    LogLevel: INFO
    EndpointUrl: "opc.tcp://192.168.1.3:62541"
    UpdateEndpointUrl: true
    SecurityPolicyUri: http://opcfoundation.org/UA/SecurityPolicy#None
Loggers:
  Mqtt:
    - Id: mqtt1
      Enabled: true
      Host: linux0.rocworks.local
      Port: 1883
      Ssl: false
      Topic: Enterprise/Site
      Logging:
        - Topic: opc/demo/path/Objects/Demo/SimulationMass/#
Unity

Missing UnityEngine.UI

If you are getting the following error while opening a Untiy project:

unity error CS0234: The type or namespace name 'UI' does not exist in the namespace 'UnityEngine' (are you missing an assembly reference?)

Then you just need to add the “Unity Ui” Package in the Unity Package manager.

Allgemein, WinCC Unified

Embed Grafana in WinCC Unified

In this scenario we will host Grafana over the IIS from WinCC Unified. So that it comes from the same origin and that we do not come over a CORS (Cross-Origin Request Blocked) problem.

What is needed to allow Grafana to be embedded in another application is to set allow_embedding = true in the Grafana configuration file.

To host Grafana over the IIS the following settings must be made:

Add a URL Rewrite to your IIS configuration file. Change “desktop-khlb071” to your computer where Grafana is running on. Restart the Webpage with the IIS Manager.

The IIS configuration file can be found here: (C:\Program Files\Siemens\Automation\WinCCUnified\SimaticUA\web.config)

                <rule name="grafana" enabled="true" stopProcessing="false">
                    <match url="grafana(/)?(.*)" ignoreCase="true" />
                    <action type="Rewrite" url="http://desktop-khlb071:3000/{R:0}" appendQueryString="true" logRewrittenUrl="false" />
                </rule>

Change the following configuration of Grafana (defaults.ini). Change the domain to your computer name where Grafana is running on. It must be the same name what you use in the IIS configuration file!

# The public facing domain name used to access grafana from a browser
domain = desktop-khlb071

# Redirect to correct domain if host header does not match domain
# Prevents DNS rebinding attacks
enforce_domain = false

# The full public facing url
root_url = %(protocol)s://%(domain)s:%(http_port)s/grafana

# Serve Grafana from subpath specified in `root_url` setting. By default it is set to `false` for compatibility reasons.
serve_from_sub_path = true

# set to true if you want to allow browsers to render Grafana in a <frame>, <iframe>, <embed> or <object>. default is false.
allow_embedding = true
Frankenstein, MQTT

Bringing PLC values to OPC UA, MQTT, GraphQL

With just some lines of configuration you can bring PLC values to OPC UA, MQTT and GraphQL. And to a variety of databases for tag logging…

💡 In that example with ModBus, but thanks to #plc4x this should work in the same way also for the other protocols supported by PLC4X.

📺 See the video, ModBus values are brought to OPC UA and MQTT.

💣 In MQTT the topic name is enriched with a UNS ISA95 topic path.

⚡ On MQTT SparkplugB encoded messages could be used.

😎 100% GUI free and Open Source.

👉 automation-gateway.com

Servers:
  GraphQL:
    - Port: 4000
      LogLevel: INFO
      GraphiQL: true

  OpcUa:
    - Port: 4841
      Enabled: true
      LogLevel: INFO      
      Topics:
        - Topic: plc/demo/node/holding-register:1:INT
        - Topic: plc/demo/node/holding-register:2:INT
        - Topic: plc/demo/node/holding-register:3:INT
Drivers:
  Plc4x:
    - Id: "demo"
      Enabled: true
      Url: "modbus://localhost:502"
      Polling:
        Time: 100
        OldNew: true
      WriteTimeout: 100
      ReadTimeout: 100
      LogLevel: INFO    

Loggers:
  Mqtt:
    - Id: mqtt1
      Enabled: true
      Host: 192.168.1.4
      Port: 1883
      Topic: modbus
      Format: Raw
      Logging:
        - Topic: plc/demo/node/holding-register:1:INT
          Target: enterprise/area1/line1/cell1/speed
        - Topic: plc/demo/node/holding-register:2:INT
          Target: enterprise/area1/line1/cell1/power
        - Topic: plc/demo/node/holding-register:3:INT
          Target: enterprise/area1/line1/cell1/torque
MQTT

TCP – MQTT – TCP Bridge

Some weeks ago, someone asked at the #HiveMQ Slack channel if there is any way to run SSH over MQTT…
 
👉 Strange idea? I liked it, and I want to share the TCP-MQTT-TCP bridge I made weeks ago. 
 
🤔 Use Case? Imagine you have IoT’s connected to a central MQTT broker and you want to connect from one to another via SSH, transfer files, expose services, a Webpage, without exposing ports to the public internet or creating a VPN. 
  
⚠️ Be careful and please evaluate and consider the possible security impacts before using this “backdoor man” … the doors …
 
⚠️ And transferring files over MQTT may increase your cloud bill 🤑 
 
👉It’s on  GitHub, it is just a draft and can be used as a template to be extended by your needs.  
 
👀 How to? Start the client.js script at the destination host – or more precisely at a node in the local network where the destination node runs – and start the server.js script on the source node from where you want to connect to the remote/destination host and port. For example, if there is a SSH server running on the destination host+port, then you can do a SSH localhost -p 8080 -l username at your source node, and you will get a SSH connection from the source node to the destination node. And the data is transmitted over MQTT topics. 
 

MQTT, Unity

HighByte to Unity3D with SparkplugB

Successfully connected HighByte Intelligence Hub to Unity3D with SparkplugB! ✔

👀 I am sending data from my Automation-Gateway.com to the Intelligence Hub via OPC UA, then the values are published from HighByte to MQTT with the Intelligence Hub SparkplugB connector and then getting consumed in Unity with the MQTT SparkplugB Asset.

👍 Works straight forward and was easy to setup!

🧐 Sparkplug Learning: If a Host Application is not the Primary Host of an Edge Node and it starts up after the Edge Node, it must send the Rebirth command to the Edge Nodes in which it is interested in, to get a birth message with the current/initial values of all the metrics of the nodes. My Unity client ignores incoming metrics of DATA messages which it has not seen before in a BIRTH message…