How to convert the below into a tabular format using rex

[sowbhagya]

message: Updated Components { "servicechannel": [ { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-15T17:20:09.000Z", "ComponentName": "ServiceChannel", "RecordId": "0N98X001200Gvv3SAC" } ], "omnisupervisorconfig": [ { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-16T16:17:37.000Z", "ComponentName": "OmniSupervisorConfig", "RecordId": "0Q27X000000KyrESAS" } ], "livechatbutton": [ { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-16T16:31:35.000Z", "ComponentName": "LiveChatButton", "RecordId": "5638X000000Xw55QAC" } ] }

LastModifiedBy

ModifiedBy

Component

RecordId

[cbrewer_splunk]

Example rex

|rex ".*\"LastmodifiedBy\":\s\"(?<LastmodifiedBy>[^\"]+)\""
|rex ".*\"ModifiedDate\":\s\"(?<ModifiedDate>[^\"]+)\""
|rex ".*\"ComponentName\":\s\"(?<ComponentName>[^\"]+)\""
|rex ".*\"RecordId\":\s\"(?<RecordId>[^\"]+)\""

[yuanliu]

Simply put: Don't.  Do not treat structured data as text and use rex to extract fields, use rex to extract the structured data.  In this case, the structure is in JSON.

Not only that.  Your data contains very different JSON nodes that both have "LastModifiedBy", "RecordId", etc.  Your result table must distinguish between JSON nodes "servicechannel" and "omnisupervisionconfig", etc.  Does this make sense?

Further, each of these nodes uses an array.  You may need to distinguish between each element in the arrays.  Because your illustration does not include multiple elements in the array, I cannot speculate what your developers' intention (semantics) is to use array for three distinct nodes.  It is possible that they committed the ultimate JSON data sin by assuming an implied semantic meaning in the arrays.   In light of this, I will not introduce the more intricate part of mixed kv-array data processing and just assume that all your data come with a single element in every of the three arrays. (When your developers give this type data, it is even more dangerous to use rex to extract data elements because no regex is compatible with the inherent flexibility afforded by the data structure.)

Here is my suggestion:

| rex "^[^{]+(?<jsondata>.+)"
| spath input=jsondata

Your sample data gives 3 sets of the 4 columns you desired, for a total of 12 columns.  That's too wide for display, so I will show a transposed table:

field_name	field_value
livechatbutton{}.ComponentName	LiveChatButton
livechatbutton{}.LastmodifiedBy	XYZ
livechatbutton{}.ModifiedDate	2024-04-16T16:31:35.000Z
livechatbutton{}.RecordId	5638X000000Xw55QAC
omnisupervisorconfig{}.ComponentName	OmniSupervisorConfig
omnisupervisorconfig{}.LastmodifiedBy	XYZ
omnisupervisorconfig{}.ModifiedDate	2024-04-16T16:17:37.000Z
omnisupervisorconfig{}.RecordId	0Q27X000000KyrESAS
servicechannel{}.ComponentName	ServiceChannel
servicechannel{}.LastmodifiedBy	XYZ
servicechannel{}.ModifiedDate	2024-04-15T17:20:09.000Z
servicechannel{}.RecordId	0N98X001200Gvv3SAC

You must then decide how you want to present such data.  I do notice that each JSON node key and each ComponentName have an apparent semantic relationship.  If key name servicechannel and ComponentName ServiceChannel, etc., are indeed semantically related, your developers also committed a different type of data structure sin: that of duplicating semantic notation (without declaration).  The data could easily be presented without losing resolution but in a much simpler and more comprehensible form:

[
  { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-15T17:20:09.000Z", "ComponentName": "ServiceChannel", "RecordId": "0N98X001200Gvv3SAC" },
  { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-16T16:17:37.000Z", "ComponentName": "OmniSupervisorConfig", "RecordId": "0Q27X000000KyrESAS" },
  { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-16T16:31:35.000Z", "ComponentName": "LiveChatButton", "RecordId": "5638X000000Xw55QAC" }
]

If you have any influence on developers, discuss data structure with them, ask them to clarify the intention/semantics of the structure and help improve structure.  This is better for everybody in the long run.

If you have no influence, one possible way to deal with this mess is to ignore all the key names and treat them like single name, i.e., by assuming the data to be

[
  {"array": [ { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-15T17:20:09.000Z", "ComponentName": "ServiceChannel", "RecordId": "0N98X001200Gvv3SAC" } ]},
  {"array": [ { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-16T16:17:37.000Z", "ComponentName": "OmniSupervisorConfig", "RecordId": "0Q27X000000KyrESAS" } ]},
  {"array": [ { "LastmodifiedBy": "XYZ", "ModifiedDate": "2024-04-16T16:31:35.000Z", "ComponentName": "LiveChatButton", "RecordId": "5638X000000Xw55QAC" } ]}
]

To do the equivalent in SPL,  (and also handle potential multiple array elements in absence of semantic knowledge)

| rex "^[^{]+(?<jsondata>.+)"
| eval jsonnode = json_keys(jsondata)
| foreach jsonnode mode=json_array
    [eval newjson = mvappend(newjson, json_object("array", json_extract(jsondata, <<ITEM>>)))]
| mvexpand newjson
| spath input=newjson path=array{}
| mvexpand array{} ``` potential multiple elements ```
| spath input=array{}

 Your sample data will give

ComponentName	LastmodiefiedBy	ModifiedDate	RecordId	newjson
ServiceChannel	XYZ	2024-04-15T17:20:09.000Z	0N98X001200Gvv3SAC	{"array":[{"LastmodifiedBy":"XYZ","ModifiedDate":"2024-04-15T17:20:09.000Z","ComponentName":"ServiceChannel","RecordId":"0N98X001200Gvv3SAC"}]}
OmniSupervisorConfig	XYZ	2024-04-16T16:17:37.000Z	0Q27X000000KyrESAS	{"array":[{"LastmodifiedBy":"XYZ","ModifiedDate":"2024-04-16T16:17:37.000Z","ComponentName":"OmniSupervisorConfig","RecordId":"0Q27X000000KyrESAS"}]}
LiveChatButton	XYZ	2024-04-16T16:31:35.000Z	5638X000000Xw55QAC	{"array":[{"LastmodifiedBy":"XYZ","ModifiedDate":"2024-04-16T16:31:35.000Z","ComponentName":"LiveChatButton","RecordId":"5638X000000Xw55QAC"}]}

Here is an emulation for you to play with and compare with real data

| makeresults 
| eval _raw="message: Updated Components { \"servicechannel\": [ { \"LastmodifiedBy\": \"XYZ\", \"ModifiedDate\": \"2024-04-15T17:20:09.000Z\", \"ComponentName\": \"ServiceChannel\", \"RecordId\": \"0N98X001200Gvv3SAC\" } ], \"omnisupervisorconfig\": [ { \"LastmodifiedBy\": \"XYZ\", \"ModifiedDate\": \"2024-04-16T16:17:37.000Z\", \"ComponentName\": \"OmniSupervisorConfig\", \"RecordId\": \"0Q27X000000KyrESAS\" } ], \"livechatbutton\": [ { \"LastmodifiedBy\": \"XYZ\", \"ModifiedDate\": \"2024-04-16T16:31:35.000Z\", \"ComponentName\": \"LiveChatButton\", \"RecordId\": \"5638X000000Xw55QAC\" } ] }"