Splunk Search

Filtering on my table

Rodrigo_Larios
Explorer

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

Labels (1)
0 Karma
1 Solution

to4kawa
Ultra Champion

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

View solution in original post

thambisetty
SplunkTrust
SplunkTrust

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 

————————————
If this helps, give a like below.
0 Karma

Rodrigo_Larios
Explorer

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

 

0 Karma

to4kawa
Ultra Champion

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
Explorer

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}]
   }

   id870a974d-be0b-4598-b5cf-d8961ed73ed5
   messageCongo Inventory Snapshot
   severityINFO

}

 

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

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try changing

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

to

spath input=data.InventoryData{} output=root

 

Rodrigo_Larios
Explorer

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!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Splunk is Nurturing Tomorrow’s Cybersecurity Leaders Today

Meet Carol Wright. She leads the Splunk Academic Alliance program at Splunk. The Splunk Academic Alliance ...

Part 2: A Guide to Maximizing Splunk IT Service Intelligence

Welcome to the second segment of our guide. In Part 1, we covered the essentials of getting started with ITSI ...

Part 1: A Guide to Maximizing Splunk IT Service Intelligence

As modern IT environments continue to grow in complexity and speed, the ability to efficiently manage and ...