topic Re: Filtering values within JSON searching in Splunk Search

Filtering values within JSON searching

Rodrigo_Larios — Wed, 29 Jul 2020 17:19:49 GMT

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 😃

Re: Filtering values within JSON searching

bowesmana — Wed, 29 Jul 2020 22:40:19 GMT

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

Re: Filtering values within JSON searching

Rodrigo_Larios — Wed, 29 Jul 2020 23:26:43 GMT

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?

Re: Filtering values within JSON searching

bowesmana — Wed, 29 Jul 2020 23:32:20 GMT

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

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

Re: Filtering values within JSON searching

Rodrigo_Larios — Wed, 29 Jul 2020 23:54:50 GMT

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.

Thanks!