topic Create table from nested array of json objects that includes lookup value in Splunk Search

Create table from nested array of json objects that includes lookup value

tomporterfield — Wed, 09 Jul 2025 14:50:40 GMT

I have an event that looks as follows:

{ "app_name": "my_app", "audit_details": { "audit": { "responseContentLength": "-1", "name": "app_name", "details": { "detail": [{ "messageId": "-4", "time": "1752065281146", "ordinal": "0" }, { "messageId": "7103", "time": "1752065281146", "ordinal": "1" }, { "messageId": "7101", "time": "1752065281146", "ordinal": "2" } ] } } } }

I want to create a table that includes a row for each detail record that includes the messageId, time and ordinal, but also a messageIdDescription that is retrieved from a lookup similar to as follows:

lookup Table_MessageId message_Id as messageId OUTPUT definition as messageIdDescription

the Table_MessageId has three columns - message_Id, definition, audit_Level

Any pointers are appreciated.

Re: Create table from nested array of json objects that includes lookup value

yuanliu — Wed, 09 Jul 2025 16:25:52 GMT

Use spath and mvexpand

| spath path=audit_details.audit.details.detail{} | mvexpand audit_details.audit.details.detail{} | spath input=audit_details.audit.details.detail{} | fields - audit_details.audit.details.detail{}*

Your would give

app_name	audit_details.audit.name	audit_details.audit.responseContentLength	messageId	ordinal	time
my_app	app_name	-1	-4	0	1752065281146
my_app	app_name	-1	7103	1	1752065281146
my_app	app_name	-1	7101	2	1752065281146

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

| makeresults | fields - _time | eval _raw = "{ \"app_name\": \"my_app\", \"audit_details\": { \"audit\": { \"responseContentLength\": \"-1\", \"name\": \"app_name\", \"details\": { \"detail\": [{ \"messageId\": \"-4\", \"time\": \"1752065281146\", \"ordinal\": \"0\" }, { \"messageId\": \"7103\", \"time\": \"1752065281146\", \"ordinal\": \"1\" }, { \"messageId\": \"7101\", \"time\": \"1752065281146\", \"ordinal\": \"2\" } ] } } } }" | spath ``` data emulation above ```

Re: Create table from nested array of json objects that includes lookup value

tomporterfield — Wed, 09 Jul 2025 19:22:00 GMT

Thanks, let me give that a go in the overall solution, but it looks very promising.

Re: Create table from nested array of json objects that includes lookup value

tomporterfield — Mon, 14 Jul 2025 11:30:46 GMT

I was able to successfully get this working with the guidance, thanks.