Splunk Search

Filtering values within JSON searching

Rodrigo_Larios
Explorer

Hi, i'm trying to filter values greater than zero.

I have this search:

index="prod_super_cc" source=ETL_GRO_01ReadMessagesKafka| spath input=data.Orders | search "{}.LineRusherTransaction"="*" | stats values({}.LineRusherTransaction) as LRTransactions

it brings some results including zero values and greater than zero values

LRTransactions

0 48580100196 48580100231 48580100687 48580100744 48580100909 48580100910 48580101088 48580101119 48580101320 

But i want to remove zero values.

I've tried using: | search "{}.LineRusherTransaction">"0" | search "{}.LineRusherTransaction">0

also

| where LRTransactions>0 (No results)

I've tried with index="prod_super_cc" source=ETL_GRO_01ReadMessagesKafka| spath input=data.Orders | search "{}.LineRusherTransaction"="*" | table {}.LineRusherTransaction | where "{}.LineRusherTransaction" > 0

Message says: Error in 'where' command: Type checking failed. The '>' operator received different types. 😓

Without a expected result. I just want to filter values by removing zero values. Could you please help me please? Thank you 😃

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

bowesmana
SplunkTrust
SplunkTrust

eval statement need to have non standard field names surrounded with single quotes, so

| eval LR=mvfilter('{}.LineRusherTransaction'>0)

 

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

You can use mvfilter to remove those values you do not want from your multi value field. See this run anywhere example.

| makeresults
| eval _raw="LRTransactions
0 48580100196 48580100231 48580100687 48580100744 48580100909 48580100910 48580101088 48580101119 48580101320"
| multikv forceheader=1
| eval LRTransactions=split(LRTransactions," ")
| table LRTransactions
| eval LRTransactions=mvfilter(LRTransactions>0)

 The last line is what you're after

Hope this helps

0 Karma

Rodrigo_Larios
Explorer

HI @bowesmana , thanks for responding!

I just tried with this example but it still is not working.

I adapted it to my splunk query: 

index="prod_super_cc" source=ETL_GRO_01ReadMessagesKafka| spath input=data.Orders | search "{}.LineRusherTransaction"="*" | table {}.LineRusherTransaction | eval LR=mvfilter({}.LineRusherTransaction>0)

The message i'm getting is:

Error in 'eval' command: The expression is malformed. An unexpected character is reached at '{}.LineRusherTransaction>0)'.

I think the problem revolves around the {}.LineRusherTransaction which comes from a JSON property. Here you can see the example:

{"OrderId":34399561,"TC":"04012745288666084055","TicketTypeCode":"Return","LineRusherTransaction":0},{"OrderId":34411872,"TC":"086094460458888015219","TicketTypeCode":"Sale","LineRusherTransaction":0},{"OrderId":34396744,"TC":"396044065436486007448","TicketTypeCode":"Sale","LineRusherTransaction":48580196744},{"OrderId":34412000,"TC":"986034168431288388420","TicketTypeCode":"Sale","LineRusherTransaction":0},

So, i'm trying to get all records with LineRusherTransaction and then trying to remove zeros sending all values to a table and then removing with eval. 

But i don´t know how to include this reference ({}.LineRusherTransaction) into the eval command.

What do you think?

0 Karma

bowesmana
SplunkTrust
SplunkTrust

eval statement need to have non standard field names surrounded with single quotes, so

| eval LR=mvfilter('{}.LineRusherTransaction'>0)

 

0 Karma

Rodrigo_Larios
Explorer

GREAT! 

It works!

mvfilter is useful, i didn´t know about it, and single quotes is what i needed.

This is my final splunk query.

index="prod_super_cc" source=ETL_GRO_01ReadMessagesKafka| spath input=data.Orders | search "{}.LineRusherTransaction"="*" | table {}.LineRusherTransaction | eval LRTrans=mvfilter('{}.LineRusherTransaction'>0) | stats values(LRTrans)

Thanks!

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...