topic Re: JSON extraction in Splunk Search

JSON extraction

psamuel69 — Mon, 25 Mar 2024 09:56:56 GMT

Hello Expert Splunk Community ,

I am struggling with a JSON extraction .
Need help/advice on how to do this operation

Data Sample :

[ { "orderTypesTotal": [ { "orderType": "Purchase", "totalFailedTransactions": 0, "totalSuccessfulTransactions": 0, "totalTransactions": 0 }, { "orderType": "Sell", "totalFailedTransactions": 0, "totalSuccessfulTransactions": 0, "totalTransactions": 0 }, { "orderType": "Cancel", "totalFailedTransactions": 0, "totalSuccessfulTransactions": 1, "totalTransactions": 1 } ], "totalTransactions": [ { "totalFailedTransactions": 0, "totalSuccessfulTransactions": 1, "totalTransactions": 1 } ] } ]

[ { "orderTypesTotal": [ { "orderType": "Purchase", "totalFailedTransactions": 10, "totalSuccessfulTransactions": 2, "totalTransactions": 12 }, { "orderType": "Sell", "totalFailedTransactions": 1, "totalSuccessfulTransactions": 2, "totalTransactions": 3 }, { "orderType": "Cancel", "totalFailedTransactions": 0, "totalSuccessfulTransactions": 1, "totalTransactions": 1 } ], "totalTransactions": [ { "totalFailedTransactions": 11, "totalSuccessfulTransactions": 5, "totalTransactions": 16 } ] } ]

I have the above event coming inside a field in _raw events . using json(field) i have validated that the above is a valid json .

UseCase :

I need to have the total of all the different ordertypes using totalFailedTransactions": ,
"totalSuccessfulTransactions": ,
"totalTransactions": numbers into a table .

	totalFailedTransactions	totalSuccessfulTransactions	totalTransactions
Purchase	10	2	12
Sell	1	2	3
Cancel	0	2	2

Thanks in advance!
Sam

Re: JSON extraction

ITWhisperer — Mon, 25 Mar 2024 09:58:39 GMT

| spath {}.orderTypesTotal{} output=orderTypesTotal | mvexpand orderTypesTotal | spath input=orderTypesTotal | stats sum(totalFailedTransactions) as totalFailedTransaction sum(totalSuccessfulTransactions) as totalSuccessfulTransactions sum(totalTransactions) as totalTransactions by orderType

Re: JSON extraction

psamuel69 — Mon, 25 Mar 2024 11:34:32 GMT

Thanks for your quick response .

I tried using spath aswell .
But it seems that the field is not getting extracted in between , as the error suggests ..

Field 'orderTypesTotal' does not exist in the data.

Do you think an extracted json would have an issue where as a raw json would work with spath ?

As my json payload is created only after adding extraction via a regex on a raw event .

Re: JSON extraction

ITWhisperer — Mon, 25 Mar 2024 11:40:37 GMT

That is difficult to determine since you haven't shared your raw event nor how you have extracted the JSON part.

Re: JSON extraction

psamuel69 — Mon, 25 Mar 2024 12:28:08 GMT

eval'ed my extracted payload to _raw and voila , it works !!!

Thanks a lot for your time and expertise !

Re: JSON extraction

ITWhisperer — Mon, 25 Mar 2024 12:54:28 GMT

If your JSON was already in a field, you could have used the input parameter to spath (this defaults to _raw)

| spath input=<your field> ...