Filtering on my table

[Rodrigo_Larios]

Hi

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

22 22 12 4 23 83 24 12 12 15 4 0 54 47 27 2 9 16 4 4 13 22 10 23 13 7 26 29 6 65 30 25 21 20 12 37 30 16 7 70 17 23 24 13 5 6 31 18 30 53 14 9 5 20 12 71 4 4 10 51 6 15 7 17 24 3 18 7 11 5 118 4 4 4 207 15 59

3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 3814 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

[thambisetty]

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 

[Rodrigo_Larios]

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

23 17 34 110 49 16 153 30 183 8 8 0 54 42 11 12 25 98 14 5 37 52 29 39 133 10 82 31 0 92 11 120 4 15 80 185 19 30 102 44 12 66 106 38 8 71 490 161 184 96 55 11 6 0 153 50 12 92 73 16 12 12 6 27 33 51 43 13 113 63 13 14 161 81 224 53 143

5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817 5817

[to4kawa · Accepted solution]

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

[Rodrigo_Larios]

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

[ITWhisperer]

Try changing

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

to

spath input=data.InventoryData{} output=root

[Rodrigo_Larios]

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!

[ITWhisperer]

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.

[ITWhisperer]

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