Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

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

siksaw33
Path Finder
‎06-19-2023 10:06 AM
 

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.

Labels (1)
Labels
0 Karma
Reply

siksaw33
Path Finder
‎06-20-2023 04:48 PM

@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
    }
  ]
}
0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎06-21-2023 12:14 AM

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

accountTokenadditionalParameters.accountBalanceadditionalParameters.accountStatusadditionalParameters.accountTokenadditionalParameters.basicadditionalParameters.channeladditionalParameters.localeadditionalParameters.numberOfLinkedAccountsadditionalParameters.primaryadditionalParameters.primaryAccountTokenadditionalParameters.productTypeadditionalParameters.programTieradditionalParameters.programTypeadditionalParameters.relationShipStatuscurrentBalance.currencyTypecurrentBalance.valuedisplayAccountNumberenrollmentIndicatorfeatureSetIdentifierfunctionAddresshttpStatusisBasiclocaleprimaryproductRelationshipTypeprogramCodeprogramTierprogramTyperedemptionIndicatorresponseTimeresponse_accountAuthorizedresponse_accountIdentifierresponse_accountStatusresponse_accountTokenresponse_availableBalance_nameresponse_availableBalance_pointCountresponse_availableBalance_typestatusstatus.codestatus.messagetierDescription
Token23722494ACTIVEToken1trueWEBen-US14trueToken1Product1Tier1Program1ACTIVEPOINTS372249491115truePRFunction.v1/3.5.0200trueen-USfalseProduct2Code1Tier1Program1true15true1M06093364ACTIVEToken1POINTS3722494POINTSACTIVE0000SUCCESSTier First
Token33722494ACTIVEToken1trueWEBen-US14trueToken1Product1Tier1Program1ACTIVEPOINTS372249491016truePRFunction.v1/3.5.0200trueen-USfalseProduct3Code1Tier1Program1true15true1M06093364ACTIVEToken1POINTS3722494POINTSCANCELLED0000SUCCESSTier 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 ```

 

Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎06-20-2023 11:26 PM

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.

0 Karma
Reply

siksaw33
Path Finder
‎06-20-2023 05:01 PM

I can post another question if this reply is too complicated

0 Karma
Reply

siksaw33
Path Finder
‎06-19-2023 02:09 PM

@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\"}]}]"
}
0 Karma
Reply

yuanliu
SplunkTrust
SplunkTrust
‎06-20-2023 12:52 AM

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

accountTokendisplayAccountNumberprimaryproductRelationshipTyperesponse_accountIdentifierresponse_accountStatusresponse_accountTokenresponse_status.coderesponse_status.messagestatus
XYZ789GHI01291115falseACCOUNT1M06093364ACTIVEXYZ123ABC4560000SUCCESSACTIVE
JKL345MNO67891107falseACCOUNT1M06093364ACTIVEXYZ123ABC4560000SUCCESSACTIVE

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

 

 

0 Karma
Reply

siksaw33
Path Finder
‎06-19-2023 01:15 PM

@yuanliuand @gcusello if you can review

0 Karma
Reply

siksaw33
Path Finder
‎06-19-2023 11:10 AM

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

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎06-19-2023 10:22 AM 
| 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
0 Karma
Reply

siksaw33
Path Finder
‎06-19-2023 01:17 PM

@ITWhispereror if you can share the link for runanywhere too

0 Karma
Reply

ITWhisperer
SplunkTrust
SplunkTrust
‎06-20-2023 01:12 AM

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
0 Karma
Reply
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...