Splunk Search

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

brc55
Explorer

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

richgalloway
SplunkTrust
SplunkTrust

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.

index=old_index
| 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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...