topic Re: Filtering on my table in Splunk Search

Filtering on my table

Rodrigo_Larios — Tue, 08 Sep 2020 18:01:38 GMT

I got following values as a result of a splunk table:

842034200103

8200000005

780432017187

841011300007

841011300001

750105364925

750105364926

750635181101

780432075008

750105364021

501032775501

750103501520

500026702400

74460700795

318537000033

501032700017

750302357834

318537000039

74460700355

5019691

842034200201

750104881020

500019600377

841041536072

500026701420

800057043540

750103501055

780432006301

764017566005

750104881001

750103501301

750103504501

841011300752

74460700805

779154012710

74460700807

8051613514

740100500451

780432016969

74460700806

74460700803

731204001701

779154012716

750103501348

841002604740

541031695052

750100866020

750100866021

841010602325

750103504316

843701160176

500026711720

841022111015

843601425251

74978750013

5019638

324599096941

501101310015

501010380031

750100561751

841011300708

750103501312

304961000410

8218409047

8043240039

500029102070

500028105626

5019613

780432042235

750103501080

750302357821

500028105624

750104881030

475002100015

750103501203

841059100127

750103501202

118

207

3814

This is my splunk query

index="prod_super_cc" source="InventorySnapshot" | spath input=data.InventoryData| search "{}.UPC"="*" | table {}.UPC,{}.AvailableToSellQty,{}.NodeId | rename "{}.UPC" as "UPC", "{}.AvailableToSellQty" as "Stock", "{}.NodeId" as Store | where Stock=0

I need to filter some specific values from this table, such as "0" for example.

750103501520 - 0 - 3814

I've tried with where sentence but i can´t get the desired result.

Thanks for your help

Re: Filtering on my table

ITWhisperer — Tue, 08 Sep 2020 18:29:28 GMT

Do the rows that you want to find exist in your data? It isn't easy to find something that doesn't exist!

Re: Filtering on my table

thambisetty — Tue, 08 Sep 2020 18:41:03 GMT

index="prod_super_cc" source="InventorySnapshot" | spath input=data.InventoryData| search "{}.UPC"="*" "{}.AvailableToSellQty"=0

| table {}.UPC,{}.AvailableToSellQty,{}.NodeId | rename "{}.UPC" as "UPC", "{}.AvailableToSellQty" as "Stock", "{}.NodeId" as Store

Re: Filtering on my table

Rodrigo_Larios — Tue, 08 Sep 2020 20:19:05 GMT

Hi,

I'm getting same block of results,

The search is working since i'm getting those blocks of data which contain 0 values, but when sending to splunk table, i'm getting all rows (I'm searching within a big json of data)

This is one of the multiple blocks i'm getting.

What i want is just to recover records with "0" values, from splunk final table.

UPC Stock Store

842034200103

8200000005

780432017187

841011300007

841011300001

750105364925

750105364926

750635181101

780432075008

501032775501

750105364021

750103501520

500026702400

74460700795

318537000033

318537000039

501032700017

750302357834

74460700355

5019691

842034200201

750104881020

841041536072

500019600377

500026701420

800057043540

750103501055

780432006301

764017566005

750104881001

750103501301

750103504501

841011300752

74460700805

779154012710

8051613514

740100500451

74460700807

780432016969

74460700806

74460700803

731204001701

779154012716

750103501348

841002604740

541031695052

750100866020

750100866021

841010602325

750103504316

841022111015

500026711720

843701160176

843601425251

74978750013

5019638

324599096941

501101310015

501010380031

750100561751

750103501312

841011300708

304961000410

8218409047

8043240039

500029102070

500028105626

750103501080

5019613

780432042235

750302357821

500028105624

750104881030

475002100015

750103501203

750103501202

841059100127

110

153

183

133

120

185

102

106

490

161

184

153

113

161

224

143

5817

Re: Filtering on my table

ITWhisperer — Tue, 08 Sep 2020 20:28:46 GMT

Do you have a sample of the beginning of the JSON block as it sounds like you need to break out the records from within the block (using spath?) into a multi-value field, mvexpand into multiple events, then search those events for the records you want.

Re: Filtering on my table

to4kawa — Wed, 09 Sep 2020 20:07:07 GMT

index="prod_super_cc" source="InventorySnapshot" | spath input=data.InventoryData{} output=root |table root |mvexpand root | spath input=root|table UPC,AvailableToSellQty,NodeId | rename AvailableToSellQty as "Stock", NodeId as Store | where Stock=0

Re: Filtering on my table

Rodrigo_Larios — Wed, 09 Sep 2020 14:44:17 GMT

Hi, thanks for your response.

Until this query i got 131 events:

index="prod_super_cc" source="InventorySnapshot" | spath input=data.InventoryData| search "{}.UPC"="*"

This is one event example of 131 events

08/09/2020
08:53:46.213

{ [-]
   data: { [-]
     InventoryData: [{"UPC":"842034200103","AvailableToSellQty":5,"NodeId":3480},{"UPC":"8200000005","AvailableToSellQty":29,"NodeId":3480},{"UPC":"780432017187","AvailableToSellQty":46,"NodeId":3480},{"UPC":"841011300007","AvailableToSellQty":26,"NodeId":3480},{"UPC":"841011300001","AvailableToSellQty":36,"NodeId":3480},{"UPC":"750105364925","AvailableToSellQty":87,"NodeId":3480},{"UPC":"750105364926","AvailableToSellQty":63,"NodeId":3480},{"UPC":"750635181101","AvailableToSellQty":49,"NodeId":3480},{"UPC":"780432075008","AvailableToSellQty":129,"NodeId":3480},{"UPC":"750105364021","AvailableToSellQty":47,"NodeId":3480},{"UPC":"501032775501","AvailableToSellQty":6,"NodeId":3480},{"UPC":"750103501520","AvailableToSellQty":12,"NodeId":3480},{"UPC":"500026702400","AvailableToSellQty":56,"NodeId":3480},{"UPC":"74460700795","AvailableToSellQty":22,"NodeId":3480},{"UPC":"318537000033","AvailableToSellQty":17,"NodeId":3480},{"UPC":"501032700017","AvailableToSellQty":16,"NodeId":3480},{"UPC":"750302357834","AvailableToSellQty":408,"NodeId":3480},{"UPC":"318537000039","AvailableToSellQty":6,"NodeId":3480},{"UPC":"74460700355","AvailableToSellQty":42,"NodeId":3480},{"UPC":"5019691","AvailableToSellQty":7,"NodeId":3480},{"UPC":"842034200201","AvailableToSellQty":30,"NodeId":3480},{"UPC":"750104881020","AvailableToSellQty":44,"NodeId":3480},{"UPC":"841041536072","AvailableToSellQty":106,"NodeId":3480},{"UPC":"500019600377","AvailableToSellQty":35,"NodeId":3480},{"UPC":"500026701420","AvailableToSellQty":161,"NodeId":3480},{"UPC":"800057043540","AvailableToSellQty":22,"NodeId":3480},{"UPC":"750103501055","AvailableToSellQty":117,"NodeId":3480},{"UPC":"780432006301","AvailableToSellQty":104,"NodeId":3480},{"UPC":"764017566005","AvailableToSellQty":18,"NodeId":3480},{"UPC":"750104881001","AvailableToSellQty":28,"NodeId":3480},{"UPC":"750103504501","AvailableToSellQty":26,"NodeId":3480},{"UPC":"750103501301","AvailableToSellQty":25,"NodeId":3480},{"UPC":"841011300752","AvailableToSellQty":5,"NodeId":3480},{"UPC":"74460700805","AvailableToSellQty":19,"NodeId":3480},{"UPC":"779154012710","AvailableToSellQty":85,"NodeId":3480},{"UPC":"74460700807","AvailableToSellQty":24,"NodeId":3480},{"UPC":"8051613514","AvailableToSellQty":197,"NodeId":3480},{"UPC":"740100500451","AvailableToSellQty":15,"NodeId":3480},{"UPC":"74460700806","AvailableToSellQty":13,"NodeId":3480},{"UPC":"780432016969","AvailableToSellQty":30,"NodeId":3480},{"UPC":"74460700803","AvailableToSellQty":15,"NodeId":3480},{"UPC":"731204001701","AvailableToSellQty":58,"NodeId":3480},{"UPC":"779154012716","AvailableToSellQty":145,"NodeId":3480},{"UPC":"750103501348","AvailableToSellQty":36,"NodeId":3480},{"UPC":"841002604740","AvailableToSellQty":61,"NodeId":3480},{"UPC":"541031695052","AvailableToSellQty":25,"NodeId":3480},{"UPC":"750100866020","AvailableToSellQty":80,"NodeId":3480},{"UPC":"750100866021","AvailableToSellQty":99,"NodeId":3480},{"UPC":"750103504316","AvailableToSellQty":77,"NodeId":3480},{"UPC":"841010602325","AvailableToSellQty":34,"NodeId":3480},{"UPC":"843701160176","AvailableToSellQty":2,"NodeId":3480},{"UPC":"841022111015","AvailableToSellQty":18,"NodeId":3480},{"UPC":"500026711720","AvailableToSellQty":24,"NodeId":3480},{"UPC":"843601425251","AvailableToSellQty":24,"NodeId":3480},{"UPC":"74978750013","AvailableToSellQty":46,"NodeId":3480},{"UPC":"5019638","AvailableToSellQty":50,"NodeId":3480},{"UPC":"324599096941","AvailableToSellQty":5,"NodeId":3480},{"UPC":"501101310015","AvailableToSellQty":55,"NodeId":3480},{"UPC":"501010380031","AvailableToSellQty":55,"NodeId":3480},{"UPC":"750100561751","AvailableToSellQty":18,"NodeId":3480},{"UPC":"841011300708","AvailableToSellQty":24,"NodeId":3480},{"UPC":"750103501312","AvailableToSellQty":14,"NodeId":3480},{"UPC":"304961000410","AvailableToSellQty":9,"NodeId":3480},{"UPC":"8218409047","AvailableToSellQty":83,"NodeId":3480},{"UPC":"8043240039","AvailableToSellQty":18,"NodeId":3480},{"UPC":"500029102070","AvailableToSellQty":45,"NodeId":3480},{"UPC":"750103501080","AvailableToSellQty":8,"NodeId":3480},{"UPC":"5019613","AvailableToSellQty":83,"NodeId":3480},{"UPC":"780432042235","AvailableToSellQty":43,"NodeId":3480},{"UPC":"500028105626","AvailableToSellQty":80,"NodeId":3480},{"UPC":"750302357821","AvailableToSellQty":27,"NodeId":3480},{"UPC":"500028105624","AvailableToSellQty":41,"NodeId":3480},{"UPC":"750104881030","AvailableToSellQty":39,"NodeId":3480},{"UPC":"475002100015","AvailableToSellQty":55,"NodeId":3480},{"UPC":"750103501203","AvailableToSellQty":31,"NodeId":3480},{"UPC":"841059100127","AvailableToSellQty":245,"NodeId":3480},{"UPC":"750103501202","AvailableToSellQty":60,"NodeId":3480}]
   }
   id: 870a974d-be0b-4598-b5cf-d8961ed73ed5
   message: Congo Inventory Snapshot
   severity: INFO
}

When i put complete query

index="prod_super_cc" source="InventorySnapshot" | spath input=data.InventoryData| search "{}.UPC"="*" | spath {} output=root |table root |mvexpand root | spath input=root|table UPC,AvailableToSellQty,NodeId | rename AvailableToSellQty as "Stock", NodeId as Store | where Stock=0

I'm not getting results, even removing where clause.

I hope above example may help us.

Thanks

Re: Filtering on my table

ITWhisperer — Wed, 09 Sep 2020 15:22:11 GMT

Try changing

spath input=data.InventoryData | search "{}.UPC"="*" | spath {} output=root

spath input=data.InventoryData{} output=root

Re: Filtering on my table

Rodrigo_Larios — Wed, 09 Sep 2020 16:04:49 GMT

Hi, finally it's working:

index="prod_super_cc" source="InventorySnapshot" | spath input=data.InventoryData output=root path={} | table root | mvexpand root | spath input=root | table AvailableToSellQty,NodeId,UPC | rename AvailableToSellQty as Stock, NodeId as Store | where Stock=24

Now i can use where command!

Thank you ALL!