Splunk Search

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

tomporterfield
Explorer

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.

Labels (1)
Tags (4)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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_nameaudit_details.audit.nameaudit_details.audit.responseContentLengthmessageIdordinaltime
my_appapp_name-1-401752065281146
my_appapp_name-1710311752065281146
my_appapp_name-1710121752065281146

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 ```

View solution in original post

Tags (2)

yuanliu
SplunkTrust
SplunkTrust

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_nameaudit_details.audit.nameaudit_details.audit.responseContentLengthmessageIdordinaltime
my_appapp_name-1-401752065281146
my_appapp_name-1710311752065281146
my_appapp_name-1710121752065281146

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 ```
Tags (2)

tomporterfield
Explorer

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

0 Karma

tomporterfield
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Leveraging Detections from the Splunk Threat Research Team & Cisco Talos

  Now On Demand  Stay ahead of today’s evolving threats with the combined power of the Splunk Threat Research ...

New in Splunk Observability Cloud: Automated Archiving for Unused Metrics

Automated Archival is a new capability within Metrics Management; which is a robust usage & cost optimization ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...