how to retrieve the value from json input using splunk query

[splunk6]

Hi All,

I have the below json format.

REQUEST="{"body":{"customer":{"accountNumber":"DBC50012225699","lineNumber":"5000654224"},"equipment":{"serialNumber":"351643935649535","grade":"A"},"redemptionDetails":{"redemptionDate":"20240502","user":"BMashiana","storeNumber":"WCCA0105","dealerNumber":"GW_STORE"}},"headers":{"content-type":"application/json;charset=UTF-8","Accept":"application/json;charset=UTF-8","Channel":"6","Locale":"en-US","TransactionID":"E86B7D59-B3CC-401D-977F-65218248367E","ApplicationID":"00000411","Authorization":"Basic ZnJlZWRvbWNyZWF0ZTpDd0t4dGlmbGZ3ZnFaQVYydWhtUg=="}}", RESPONSE="{"body":{"model":{"isRedeemed":true,"transactionReferenceNumber":"6200754043","redeemType":"Original","redemptionFailureReasonType":null,"redemptionEquipmentMake":"Apple","redemptionEquipmentModel":"iPhone 14 Pro Max 128GB Deep Purple","redemptionEquipmentMemory":"128 GB","committedPrice":1,"additionalFees":0},"code":200,"messages":null,"isSuccess":true},"headers":{"connection":"close","content-type":"application/json;charset=utf-8","set-cookie":["AWSELB=B3A9CDE108B7A1C9F0AFA19D2F1D801BC5EA2DB758E049CA400C049FE7C310DF0BB906899F8C6DFC23D16712EBB4CB423C132BEE67F4F3CB94A24AC7D3196B970C175CF4E9;PATH=/","AWSELBCORS=B3A9CDE108B7A1C9F0AFA19D2F1D801BC5EA2DB758E049CA400C049FE7C310DF0BB906899F8C6DFC23D16712EBB4CB423C132BEE67F4F3CB94A24AC7D3196B970C175CF4E9;PATH=/;SECURE;SAMESITE=None","visid_incap_968152=mMXe9betSnmAGjb6EkS6d8pCNGYAAAAAQUIPAAAAAACzpzJ8pi0eFle6ni7emEj9; expires=Fri, 02 May 2025 07:32:03 GMT; HttpOnly; path=/; Domain=.likewize.com","nlbi_968152=pTYgM3uDpkZMpK2uILjsZwAAAABT3d67R/8WtJ556QqTUFQd; path=/; Domain=.likewize.com","incap_ses_677_968152=NKgET8f8eCtwLRsU8y9lCcpCNGYAAAAAghYI7GnE7TXEfi+SGl0EKw==; path=/; Domain=.likewize.com"],"content-length":"354","server":"Jetty(9.4.45.v20220203)"}}", RETRYNO="0", ENDPOINT="https://apptium.freedommobile.ca/Activation.TradeUp", OPERATION="/FPC/Redemption/Redeem", METHOD="POST", CONNECTORID="0748a993-4566-48ae-9885-2a4dce9de585", CONNECTORNAME="Likewize", CONNECTORTYPE="Application", CONNECTORSUBTYPE="REST", STARTTIME="1714700999019", ENDTIME="1714701003106", RESPONSETIME="4087", SUCCESS="1", CLIENT="eportal-services", CREATEDDATE="2024-05-03 01:50:03", USERNAME="BMashiana@FreedomMobile.ca", SESSIONID="_dd9ad114-bb2b-4c7f-a7aa-cfc3b929f674", ACTIONID="6e9c5f97-27bc-42fb-b1d3-61a701e4a708", TRACKID="3618c3e3-9bd1-4acc-af6a-f71f31b9092c"

How do I retrieve the account number, channel code, serialNumber from REQUEST and transactionReferenceNumber from RESPONSE using splunk query. I have tried using spath and its not working out for me and displays a blank result. Please help asap.

index="wireless_retail" source="create_freedom.transactionlog" OPERATION="/FPC/Redemption/Redeem" 
|spath input=REQUEST output=accountNumber path=body.customer{}.accountNumber
|mvexpand accountNumber
|table accountNumber

[splunk6]

is there any other way we can do it 

[ITWhisperer]

Assuming your events all follow the same pattern i.e. REQUEST followed by RESPONSE followed by RETRYNO, you could extract them prior to using spath

| rex "REQUEST=\"(?<REQUEST>.+)\", RESPONSE=\"(?<RESPONSE>.+)\", RETRYNO"
| spath input=REQUEST
| spath input=RESPONSE

[splunk6]

thanks for the response. But i need to extract only the accountNumber, channel,serialnumber from REQUEST and transactionReferenceNumber from RESPONSE and display in table format

[ITWhisperer]

OK but you already know how to extract these elements as you have shown this in your question

[splunk6]

index="wireless_retail" source="create_freedom.transactionlog" OPERATION="/FPC/Redemption/Redeem" 
|spath input=REQUEST output=accountNumber path=body.customer{}.accountNumber
|mvexpand accountNumber
|table accountNumber

this queryis not displaying any results for me if i run the results are blank 

[ITWhisperer]

Why have you just ignored my suggestion and not included the rex line I suggested?

[splunk6]

Sorry for the confusion. I tried with your query but am able to retrive only the accountnumber , but channel, serial number , memory (from response) is not retrieving. could you please check my updated query

index="wireless_retail" source="CREATE_FREEDOM.transactionlog" OPERATION="/FPC/Redemption/Redeem"
| rex "REQUEST=\"(?<REQUEST>.+)\", RESPONSE=\"(?<RESPONSE>.+)\", RETRYNO"
| spath input=REQUEST
|spath input=REQUEST output=accountNumber path=body.customer{}.accountNumber
|mvexpand accountNumber
|spath input=REQUEST output=serialNumber path=body.customer{}.serialNumber
|mvexpand serialNumber
|spath input=REQUEST output=Channel path=body.customer{}.Channel
|mvexpand Channel
|spath input=RESPONSE
|spath input=RESPONSE output=redemptionEquipmentMemory path=body.customer{}.redemptionEquipmentMemory
|mvexpand redemptionEquipmentMemory
|table accountNumber serialNumber Channel redemptionEquipmentMemory

[ITWhisperer]

Have REQUEST and RESPONSE been already extracted successfully?

Btw, your event isn't (completely) JSON; it does contain some JSON elements, but unless these have been extracted, you won't be able to use spath on them.

[splunk6]

Hi,

index="wireless_retail" source="CREATE_FREEDOM.transactionlog" OPERATION="/FPC/Redemption/Redeem"
| rex "REQUEST=\"(?<REQUEST>.+)\", RESPONSE=\"(?<RESPONSE>.+)\", RETRYNO"
| spath input=REQUEST
|spath input=REQUEST output=accountNumber path=body.customer{}.accountNumber
|mvexpand accountNumber
|spath input=REQUEST output=serialNumber path=body.equipment{}.serialNumber
|mvexpand serialNumber
|spath input=REQUEST output=Channel path=body{}.headers{}.Channel
|mvexpand Channel
|spath input=RESPONSE
|spath input=RESPONSE output=redemptionEquipmentMemory path=body.model{}.redemptionEquipmentMemory
|mvexpand redemptionEquipmentMemory
|spath input=RESPONSE output=transactionReferenceNumber path=body.model{}.transactionReferenceNumber
|mvexpand transactionReferenceNumber
|table accountNumber serialNumber Channel redemptionEquipmentMemory transactionReferenceNumber

with this query i was able to retrieve the following as you suggested

but the channel alone is missing. It s inside the "body", ->"headers"->"Channel". could you please let me know that alone how to retrive the path so that it wld be helpful for me to complete my work. Thanks in advance

[ITWhisperer]

Try using the correct path

|spath input=REQUEST output=Channel path=headers{}.Channel

[splunk6]

Hi 

Just to add on this existing query, I need to get the memory details from REQUEST alone. My raw data is like the below and this memory is also not available in all the events. So i need to fetch a report with the events that are only having "memory" in the REQUEST.(Not all events have this "memory" in the REQUEST). Please help asap.

[ITWhisperer]

What is the path to this element?

[splunk6]

Hi,

This is the path to the element:

|spath input=REQUEST output=Memory path=body.equipment{}.memory

also this memory is not available in all the REQUEST.So i want only the events with whichever is having the "memory" in the REQUEST. I dont want to display the events without memory

[ITWhisperer · Accepted solution]

OK so extract it then filter the events

| where isnotnull(Memory)

[splunk6]

yea it works now thanks for your response