topic Re: How to have only common values for 2 fields in the same search? in Splunk Search

How to have only common values for 2 fields in the same search?

tjosm — Wed, 07 Aug 2019 16:02:06 GMT

I have a table like this:

Shops Location Total
1. CK SF 1000
2. CK LA 877
3. CK NY 543
4. CK BOS 436
5. OG NY 2112
6. OG DET 100
7. OG ATL 876
8. OG LA 421

I want a table/report like this:
Shops Location Total
1. CK LA 877
2. CK NY 543
3. OG NY 2112
4. OG LA 421

Re: How to have only common values for 2 fields in the same search?

Sukisen1981 — Wed, 30 Sep 2020 01:44:09 GMT

hi @tjosm
Your query is a bit confusing,given you requirements.
You need to have Total as a by clause in both stats and stream stats. You are loosing the field 'Total' and instead having sum of count as Total which will probably give you output as 1 /(just the unique count), be that as it may I kinda took your initial data in a csv and added this code.
| stats count by Shops,Location,Total
| sort - count| streamstats count as rank by Shops | where rank < 9|stats sum(count) by Shops,Location,Total | rename sum(count) as newtotal
| eventstats count as loc_count by Location
| where loc_count>1
| fields - newtotal, - loc_count

tstats has become stats since i took the first data in a csv

Re: How to have only common values for 2 fields in the same search?

Sukisen1981 — Mon, 19 Aug 2019 15:00:30 GMT

hi @tjosm

Please let us know if your issue has been resolved and accept the answer if it significantly helped your resolution. Do not forget to add additional resolution details for the benefit of other form members.