How to split the JSON array into multiple new events?

[jhcbazinga95]

Hey all,

Can someone help me out with a JSON related question! Many many thanks! 😄

I have a JSON arrays field in this format

results=<200 OK OK,
{
"tnPortingActivityInProgress" : "N",
"availableActions" :
[
{
"accountAction" : "Restart",
"actionAvailable" : "N",
"actionNotAvailableReason" : "Account is Active"
},
{
"accountAction" : "Multi-AP Salable",
"actionAvailable" : "Y"
},
{
"accountAction" : "Seasonal Suspend",
"actionAvailable" : "Y"
}
],
"transactionId" : "1234567"
}
,[]>

I would love to parse the json array into this format. 

transactionId	aaccountAction	actionAvailable	actionNotAvailableReason
1234567	Restart	N	Account is Active
1234567	Multi-AP Salable	Y
1234567	Seasonal Suspend	Y

I have tried a query like this. As you can see, the data is stacked in the same row right now, which is not working in my case  as I have no idea what actionAvailable & actionNotAvailableReason for what accountActions.  And also, the search is not working either if we do like this. 

[ITWhisperer]

| eval json=replace(replace(results,"<200 OK OK,",""),",{}>","")
| spath input=json availableActions{} output=availableActions
| mvexpand availableActions
| spath input=availableActions

[jhcbazinga95]

Heyyy! 

@ITWhisperer Thank you so much for the helpful reply!🙂 It does separate data into different rows. However, when I bring data from other service/actions,  data is not combined in a expected way. 

Since it is my second week using Splunk, please excuse my native & obvious questions.😅

Below are some detailed explanations & screenshots.

results (where accountAction actionAvailable actionNotAvailableReason are from) is from  action=getAccountAvailableActions

specialtyProduct is from action=getServiceabilityByAddressId_V3

customerType is from "isRepeatTroubleCallEligible" 

Below is the query I used to test. As you can see, either data source will be "null",  and when I search for both of them are not "null", it returns no result.

When I tried to aggregate data a little bit to ensure there are data showing up, it just goes back to the stacked result😢

Below is the final query I have came up with. What I eventually want to achieve is a result that I can filter on by playing with customerType, accountAction, smb_ind and actionAvailable to do some negative tests to monitor if the backend logics for different accountActions (such as Multi-AP Salable) are running correct or not. Due to the multiple returned values from JSON array, statistics table is not showing the correct result at this moment. 

For example, for the highlighted transaction_id, actionAvailable returns "Y" in the table, but when I dive into  event. it actually is N when accountAction is Multi-AP Salable. Please see the screenshot I attached in the message.

 

Thank you again for the responsive reply. Looking forward to hearing insights from you very soon!

Cheers!

[yuanliu]

@jhcbazinga95 First off, it's much easier to get help if you post sample code in text.

@ITWhisperer is just showing you how to extract fields.  You still need to do stats according to plan.

| eval json=replace(replace(results,"<200 OK OK,",""),",{}>","")
| spath input=json path=transactionId
| spath input=json path=availableActions{}
| mvexpand availableActions{}
| spath input=availableActions{}
| stats values(actionNotAvailableReason) as actionNotAvailableReason by transactionId accountAction actionAvailable

 Using your sample data, output is

transactionId	accountAction	actionAvailable	actionNotAvailableReason
1234567	Multi-AP Salable	Y
1234567	Restart	N	Account is Active
1234567	Seasonal Suspend	Y