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:
{
"functionAddres...
See more...
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.