Splunk Search

How would I be able to improve the following joined query?


Learning about joins and sub searches. What's the following query executing and would there be a way to make it more efficient?

index=old_indexstats count values(d) as d by username | join type=inner username [search index=new_index | stats count by username ]

I believe it starts by searching and counting usernames in the new index however, am getting mixed up after that.

Labels (3)
0 Karma


The first step in the query is to count all events and list the unique values of field 'd' for all usernames in old_index.  The second step is to count all events for each username in new_index.  Finally, the two sets of results are merged based on common username values.  If there is no match, the count from new_index is retained.

Here's one alternative query that should be more efficient.

| stats count as old_count values(d) as d by username 
| append [search index=new_index | stats count as new_count by username ]
| stats values(*) as * by username
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Dashboard Studio Challenge - Learn New Tricks, Showcase Your Skills, and Win Prizes!

Reimagine what you can do with your dashboards. Dashboard Studio is Splunk’s newest dashboard builder to ...

Introducing Edge Processor: Next Gen Data Transformation

We get it - not only can it take a lot of time, money and resources to get data into Splunk, but it also takes ...

Take the 2021 Splunk Career Survey for $50 in Amazon Cash

Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey. Last year’s ...