Filtering values within JSON searching

[Rodrigo_Larios]

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 😃

[bowesmana]

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

[Rodrigo_Larios]

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?

[bowesmana · Accepted solution]

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

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

[Rodrigo_Larios]

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!