How to extract nested JSON fields and array from S...

siksaw33 · ‎06-19-2023

I'm trying to extract some information from nested JSON data stored in Splunk. Here's a simplified and anonymized example of the type of data I'm dealing with:

    {
    "functionAddress": "ReadAccounts.v1/3.5.0",
    "responseTime": 15,
    "httpStatus": 200,
    "additionalParameters": {
        "locale": "en-US",
        "accountToken": "XYZ123ABC456",
        "productType": "CARD_ACCOUNT",
        "primaryAccountToken": "XYZ123ABC456",
        "accountBalance": "3722494",
        "accountStatus": "ACTIVE",
        "relationShipStatus": "ACTIVE",
        "numberOfLinkedCards": 14,
        "primary": true
    },
    "response": "[{\"status\":{\"code\":\"0000\",\"message\":\"SUCCESS\"},\"accountToken\":\"XYZ123ABC456\",\"accountIdentifier\":\"1M06093364\",\"accountStatus\":\"ACTIVE\",\"relationships\":[{\"accountToken\":\"XYZ789GHI012\",\"primary\":false,\"status\":\"ACTIVE\",\"displayAccountNumber\":\"91115\",\"productRelationshipType\":\"ACCOUNT\"},{\"accountToken\":\"JKL345MNO678\",\"primary\":false,\"status\":\"ACTIVE\",\"displayAccountNumber\":\"91107\",\"productRelationshipType\":\"ACCOUNT\"}]}]"
}

The response field is a JSON string that contains an array (even if there's only one element). Inside this array, there's a relationships array that can contain multiple elements.

I'm trying to extract the accountToken, accountIdentifier, accountStatus fields and all the relationships from this data into a table. So far, I've tried the following query but it doesn't seem to work as expected:

    index=my_index ReadAccounts relationshipStatus en-US CANCELLED
| spath input=response path={}.accountToken output=accountToken
| spath input=response path={}.accountIdentifier output=accountIdentifier
| spath input=response path={}.accountStatus output=accountStatus
| spath input=response path={}.relationships{} output=relationships
| mvexpand relationships
| rename relationships as _raw
| spath input=_raw path=accountToken output=relationship_accountToken
| spath input=_raw path=primary output=relationship_primary
| spath input=_raw path=status output=relationship_status
| spath input=_raw path=displayAccountNumber output=relationship_displayAccountNumber
| spath input=_raw path=productRelationshipType output=relationship_productRelationshipType
| table _time, message, accountToken, accountIdentifier, accountStatus, relationship_accountToken, relationship_primary, relationship_status, relationship_displayAccountNumber, relationship_productRelationshipType
| head 10

When I run this query, the table appears blank, except for the _time column and everything is still in message. Could someone please advise what might be going wrong and how I can correct it? I'm trying to extract each relationship as its own row in the table.

Thank you in advance for any help you can provide.

siksaw33 · ‎06-20-2023

@ITWhisperer and @yuanliu sorry I am a Splunk Noob, can you please update your query, this is the entire JSON

{
  "functionAddress":"Function.v1/3.5.0",
  "responseTime":15,
  "httpStatus":200,
  "additionalParameters":{
    "locale":"en-US",
    "channel":"WEB",
    "accountToken":"Token1",
    "productType":"Product1",
    "primaryAccountToken":"Token1",
    "programTier":"Tier1",
    "programType":"Program1",
    "accountBalance":"3722494",
    "accountStatus":"ACTIVE",
    "relationShipStatus":"ACTIVE",
    "numberOfLinkedAccounts":14,
    "basic":true,
    "primary":true
  },
  "response":[
    {
      "status":{
        "code":"0000",
        "message":"SUCCESS"
      },
      "accountToken":"Token1",
      "accountIdentifier":"1M06093364",
      "accountStatus":"ACTIVE",
      "programCode":"Code1",
      "programTier":"Tier1",
      "tierDescription":"Tier First",
      "programType":"Program1",
      "currentBalance":{
        "currencyType":"POINTS",
        "value":3722494
      },
      "accountBalance":{
        "availableBalance":[
          {
            "type":"POINTS",
            "name":"POINTS",
            "pointCount":"3722494"
          }
        ]
      },
      "enrollmentIndicator":true,
      "redemptionIndicator":true,
      "accountAuthorized":true,
      "featureSetIdentifier":"PR",
      "relationships":[
        {
          "accountToken":"Token2",
          "primary":false,
          "status":"ACTIVE",
          "displayAccountNumber":"91115",
          "productRelationshipType":"Product2"
        },
        {
          "accountToken":"Token3",
          "primary":false,
          "status":"CANCELLED",
          "displayAccountNumber":"91016",
          "productRelationshipType":"Product3"
        },
        // more relationship objects here
      ],
      "locale":"en-US",
      "isBasic":true
    }
  ]
}

yuanliu · ‎06-21-2023

This changes the entire structure of the response field. You realize that, right? response in the original sample you posted is a string with escaped JSON. In this sample, response is regular JSON. It is just as easy to extract data, but different data requires different code. The data contain several arrays. So, you need to apply several path-mvexpand combinations.

| spath path=response{}
| mvexpand response{}
| spath input=response{}
| spath input=response{} path=accountBalance.availableBalance{}
| mvexpand accountBalance.availableBalance{}
| spath input=accountBalance.availableBalance{}
| rename type as response_availableBalance_type, name as response_availableBalance_name, pointCount as response_availableBalance_pointCount
| rename account* as response_account*
| spath input=response{} path=relationships{}
| mvexpand relationships{}
| spath input=relationships{}

The new sample gives me

accountToken

additionalParameters.accountBalance

additionalParameters.accountStatus

additionalParameters.accountToken

additionalParameters.basic

additionalParameters.channel

additionalParameters.locale

additionalParameters.numberOfLinkedAccounts

additionalParameters.primary

additionalParameters.primaryAccountToken

additionalParameters.productType

additionalParameters.programTier

additionalParameters.programType

additionalParameters.relationShipStatus

currentBalance.currencyType

currentBalance.value

displayAccountNumber

enrollmentIndicator

featureSetIdentifier

functionAddress

httpStatus

isBasic

locale

primary

productRelationshipType

programCode

programTier

programType

redemptionIndicator

responseTime

response_accountAuthorized

response_accountIdentifier

response_accountStatus

response_accountToken

response_availableBalance_name

response_availableBalance_pointCount

response_availableBalance_type

status

status.code

status.message

tierDescription

Token2

3722494

ACTIVE

Token1

true

WEB

en-US

14

true

Token1

Product1

Tier1

Program1

ACTIVE

POINTS

3722494

91115

true

PR

Function.v1/3.5.0

200

true

en-US

false

Product2

Code1

Tier1

Program1

true

15

true

1M06093364

ACTIVE

Token1

POINTS

3722494

POINTS

ACTIVE

0000

SUCCESS

Tier First

Token3

3722494

ACTIVE

Token1

true

WEB

en-US

14

true

Token1

Product1

Tier1

Program1

ACTIVE

POINTS

3722494

91016

true

PR

Function.v1/3.5.0

200

true

en-US

false

Product3

Code1

Tier1

Program1

true

15

true

1M06093364

ACTIVE

Token1

POINTS

3722494

POINTS

CANCELLED

0000

SUCCESS

Tier First

This is an emulation that you can play with and compare with real data

| makeresults
| eval _raw = "{
  \"functionAddress\":\"Function.v1/3.5.0\",
  \"responseTime\":15,
  \"httpStatus\":200,
  \"additionalParameters\":{
    \"locale\":\"en-US\",
    \"channel\":\"WEB\",
    \"accountToken\":\"Token1\",
    \"productType\":\"Product1\",
    \"primaryAccountToken\":\"Token1\",
    \"programTier\":\"Tier1\",
    \"programType\":\"Program1\",
    \"accountBalance\":\"3722494\",
    \"accountStatus\":\"ACTIVE\",
    \"relationShipStatus\":\"ACTIVE\",
    \"numberOfLinkedAccounts\":14,
    \"basic\":true,
    \"primary\":true
  },
  \"response\":[
    {
      \"status\":{
        \"code\":\"0000\",
        \"message\":\"SUCCESS\"
      },
      \"accountToken\":\"Token1\",
      \"accountIdentifier\":\"1M06093364\",
      \"accountStatus\":\"ACTIVE\",
      \"programCode\":\"Code1\",
      \"programTier\":\"Tier1\",
      \"tierDescription\":\"Tier First\",
      \"programType\":\"Program1\",
      \"currentBalance\":{
        \"currencyType\":\"POINTS\",
        \"value\":3722494
      },
      \"accountBalance\":{
        \"availableBalance\":[
          {
            \"type\":\"POINTS\",
            \"name\":\"POINTS\",
            \"pointCount\":\"3722494\"
          }
        ]
      },
      \"enrollmentIndicator\":true,
      \"redemptionIndicator\":true,
      \"accountAuthorized\":true,
      \"featureSetIdentifier\":\"PR\",
      \"relationships\":[
        {
          \"accountToken\":\"Token2\",
          \"primary\":false,
          \"status\":\"ACTIVE\",
          \"displayAccountNumber\":\"91115\",
          \"productRelationshipType\":\"Product2\"
        },
        {
          \"accountToken\":\"Token3\",
          \"primary\":false,
          \"status\":\"CANCELLED\",
          \"displayAccountNumber\":\"91016\",
          \"productRelationshipType\":\"Product3\"
        }

      ],
      \"locale\":\"en-US\",
      \"isBasic\":true
    }
  ]
}"
| spath
``` data emulation above ```

ITWhisperer · ‎06-20-2023

This still doesn't appear to have a message field - unless this is the message field?

It is often more helpful to share the entire _raw field so we can see what you are dealing with.

siksaw33 · ‎06-20-2023

I can post another question if this reply is too complicated

siksaw33 · ‎06-19-2023

@ITWhispererI forgot to mention that the message is the base JSON

 message {
    "functionAddress": "ReadAccounts.v1/3.5.0",
    "responseTime": 15,
    "httpStatus": 200,
    "additionalParameters": {
        "locale": "en-US",
        "accountToken": "XYZ123ABC456",
        "productType": "CARD_ACCOUNT",
        "primaryAccountToken": "XYZ123ABC456",
        "accountBalance": "3722494",
        "accountStatus": "ACTIVE",
        "relationShipStatus": "ACTIVE",
        "numberOfLinkedCards": 14,
        "primary": true
    },
    "response": "[{\"status\":{\"code\":\"0000\",\"message\":\"SUCCESS\"},\"accountToken\":\"XYZ123ABC456\",\"accountIdentifier\":\"1M06093364\",\"accountStatus\":\"ACTIVE\",\"relationships\":[{\"accountToken\":\"XYZ789GHI012\",\"primary\":false,\"status\":\"ACTIVE\",\"displayAccountNumber\":\"91115\",\"productRelationshipType\":\"ACCOUNT\"},{\"accountToken\":\"JKL345MNO678\",\"primary\":false,\"status\":\"ACTIVE\",\"displayAccountNumber\":\"91107\",\"productRelationshipType\":\"ACCOUNT\"}]}]"
}

yuanliu · ‎06-20-2023

All the information is in messages.response. This is a field that Splunk should have already given you. (And Splunk cannot give you field "response" for use as input for spath.) The string value is itself an escaped JSON object. So,

| spath input=message.response path={}
| mvexpand {}
| spath input={}
| rename account* as response_account*, status.* as response_status.*
| spath input={} path=relationships{}
| mvexpand relationships{}
| spath input=relationships{}

Also, you missed quotation marks surrounding "message", and brackets surrounding the entire structure. The sample data give me

accountToken	displayAccountNumber	primary	productRelationshipType	response_accountIdentifier	response_accountStatus	response_accountToken	response_status.code	response_status.message	status
XYZ789GHI012	91115	false	ACCOUNT	1M06093364	ACTIVE	XYZ123ABC456	0000	SUCCESS	ACTIVE
JKL345MNO678	91107	false	ACCOUNT	1M06093364	ACTIVE	XYZ123ABC456	0000	SUCCESS	ACTIVE

Here is a data emulation that you can play with and compare with raw data.

| makeresults
| eval _raw = "{\"message\": {
    \"functionAddress\": \"ReadAccounts.v1/3.5.0\",
    \"responseTime\": 15,
    \"httpStatus\": 200,
    \"additionalParameters\": {
        \"locale\": \"en-US\",
        \"accountToken\": \"XYZ123ABC456\",
        \"productType\": \"CARD_ACCOUNT\",
        \"primaryAccountToken\": \"XYZ123ABC456\",
        \"accountBalance\": \"3722494\",
        \"accountStatus\": \"ACTIVE\",
        \"relationShipStatus\": \"ACTIVE\",
        \"numberOfLinkedCards\": 14,
        \"primary\": true
    },
    \"response\": \"[{\\\"status\\\":{\\\"code\\\":\\\"0000\\\",\\\"message\\\":\\\"SUCCESS\\\"},\\\"accountToken\\\":\\\"XYZ123ABC456\\\",\\\"accountIdentifier\\\":\\\"1M06093364\\\",\\\"accountStatus\\\":\\\"ACTIVE\\\",\\\"relationships\\\":[{\\\"accountToken\\\":\\\"XYZ789GHI012\\\",\\\"primary\\\":false,\\\"status\\\":\\\"ACTIVE\\\",\\\"displayAccountNumber\\\":\\\"91115\\\",\\\"productRelationshipType\\\":\\\"ACCOUNT\\\"},{\\\"accountToken\\\":\\\"JKL345MNO678\\\",\\\"primary\\\":false,\\\"status\\\":\\\"ACTIVE\\\",\\\"displayAccountNumber\\\":\\\"91107\\\",\\\"productRelationshipType\\\":\\\"ACCOUNT\\\"}]}]\"
}
}"
| spath
``` data emulation above ```

siksaw33 · ‎06-19-2023

@yuanliuand @gcusello if you can review

siksaw33 · ‎06-19-2023

nope still everything (the event) is ONLY appearing in 'message' and I get Field 'relationships' does not exist in the data.

ITWhisperer · ‎06-19-2023

| spath response
| spath input=response path={}.accountToken output=accountToken
| spath input=response path={}.accountIdentifier output=accountIdentifier
| spath input=response path={}.accountStatus output=accountStatus
| spath input=response path={}.relationships{} output=relationships
| mvexpand relationships
| rename relationships as _raw
| spath input=_raw path=accountToken output=relationship_accountToken
| spath input=_raw path=primary output=relationship_primary
| spath input=_raw path=status output=relationship_status
| spath input=_raw path=displayAccountNumber output=relationship_displayAccountNumber
| spath input=_raw path=productRelationshipType output=relationship_productRelationshipType
| table _time, message, accountToken, accountIdentifier, accountStatus, relationship_accountToken, relationship_primary, relationship_status, relationship_displayAccountNumber, relationship_productRelationshipType
| head 10

siksaw33 · ‎06-19-2023

@ITWhispereror if you can share the link for runanywhere too

ITWhisperer · ‎06-20-2023

This runanywhere example is based on the data you did share, which by the way doesn't show the message field. if you want a more cogent example, you will have to provide an more accurate representation of your actual events.

| makeresults
| eval _raw="    {
    \"functionAddress\": \"ReadAccounts.v1/3.5.0\",
    \"responseTime\": 15,
    \"httpStatus\": 200,
    \"additionalParameters\": {
        \"locale\": \"en-US\",
        \"accountToken\": \"XYZ123ABC456\",
        \"productType\": \"CARD_ACCOUNT\",
        \"primaryAccountToken\": \"XYZ123ABC456\",
        \"accountBalance\": \"3722494\",
        \"accountStatus\": \"ACTIVE\",
        \"relationShipStatus\": \"ACTIVE\",
        \"numberOfLinkedCards\": 14,
        \"primary\": true
    },
    \"response\": \"[{\\\"status\\\":{\\\"code\\\":\\\"0000\\\",\\\"message\\\":\\\"SUCCESS\\\"},\\\"accountToken\\\":\\\"XYZ123ABC456\\\",\\\"accountIdentifier\\\":\\\"1M06093364\\\",\\\"accountStatus\\\":\\\"ACTIVE\\\",\\\"relationships\\\":[{\\\"accountToken\\\":\\\"XYZ789GHI012\\\",\\\"primary\\\":false,\\\"status\\\":\\\"ACTIVE\\\",\\\"displayAccountNumber\\\":\\\"91115\\\",\\\"productRelationshipType\\\":\\\"ACCOUNT\\\"},{\\\"accountToken\\\":\\\"JKL345MNO678\\\",\\\"primary\\\":false,\\\"status\\\":\\\"ACTIVE\\\",\\\"displayAccountNumber\\\":\\\"91107\\\",\\\"productRelationshipType\\\":\\\"ACCOUNT\\\"}]}]\"
}"
| spath response
| spath input=response path={}.accountToken output=accountToken
| spath input=response path={}.accountIdentifier output=accountIdentifier
| spath input=response path={}.accountStatus output=accountStatus
| spath input=response path={}.relationships{} output=relationships
| mvexpand relationships
| rename relationships as _raw
| spath input=_raw path=accountToken output=relationship_accountToken
| spath input=_raw path=primary output=relationship_primary
| spath input=_raw path=status output=relationship_status
| spath input=_raw path=displayAccountNumber output=relationship_displayAccountNumber
| spath input=_raw path=productRelationshipType output=relationship_productRelationshipType
| table _time, message, accountToken, accountIdentifier, accountStatus, relationship_accountToken, relationship_primary, relationship_status, relationship_displayAccountNumber, relationship_productRelationshipType
| head 10

How to extract nested JSON fields and array from Splunk data using spath?

field extraction

Say goodbye to manually analyzing phishing and malware threats with Splunk Attack ...

AppDynamics is now part of Splunk Ideas

Advanced Splunk Data Management Strategies