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

Vikas Singh 145 Reputation points
2023-10-18T14:25:53.9266667+00:00

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.

Azure Data Explorer
Azure Data Explorer
An Azure data analytics service for real-time analysis on large volumes of data streaming from sources including applications, websites, and internet of things devices.
487 questions
{count} votes

Accepted answer
  1. Wilko van de Velde 2,226 Reputation points
    2023-10-25T12:17:10.96+00:00

    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".

    2 people found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful