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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...