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 (5)
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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...