Splunk Search

JSON extraction

psamuel69
Explorer

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 .

 totalFailedTransactionstotalSuccessfulTransactionstotalTransactions
Purchase10212
Sell123
Cancel022

 

Thanks in advance!
Sam

 

Labels (6)
Tags (1)
0 Karma
1 Solution

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

View solution in original post

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

psamuel69
Explorer

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

Spoiler
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 . 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

psamuel69
Explorer

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

Thanks a lot for your time and expertise !

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...