How to extract the raw PLC data from array of json object in KQL

Question

I have created one ADX and integrated with Event Hub. This event hub is configured into message routing of IoT Hub.

And I am able to get the raw data successfully as per attached screenshot. However, This coming from the PLC like PLC to Lower Layer Server to Upper Layer Server - IoT Hub - via Even Hub routing to Azure Data Explorer.

So, It is containing lot of . value after string and want to extract the data but no luck. Please help here if any workaround.

Answer 1

Given the example, I create the example script below. I took the body as the original text. If you want the different values from the id field, you can use the split operator to create an array. Then, I made a column of the first value of the array.

print originaltext=dynamic({"timestamp":1696918303323,"values":[{"id":"Lower Layer Server.ABC-XYZ-Weld.ABC_XYZ_Weld.M26050.ServerInterfaces.Machines.M10230.Coil.Components.CoilThickness.AnalogSignal","v":0,"q":true,"t":1348936641086},{"id":"Lower Layer Server.ABC-XYZ-Weld.ABC_XYZ_Weld.M26050.ServerInterfaces.Machines.M10230.Coil.Identification.SerialNumber","v":"0","q":true,"t":1348936641090}]})
| mv-expand values = originaltext.values
| extend splitted_string = split(values.id,'.')
| extend firstvalue = splitted_string[0]

Hope this will help.

Kind regards, Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".