Splunk Search

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

tjosm
New Member

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

Code I have:
|tstats count where index=us-shops Shops IN (CK OG) Location=* by Shops,Location |sort - count| streamstats count as rank by Shops
| where rank < 9|stats sum(count) by Shops,Location | rename sum(count) as Total

Tags (1)
0 Karma

Sukisen1981
Champion

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

0 Karma

Sukisen1981
Champion

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.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...