Splunk Search

Replace join with stats to merge events based on common field

eddiet
Explorer

My datasets are much larger but these represent the crux of my hurdle

sourcetype=sale_by
fields: sid, user

sourcetype=sale_made
fields: sid, amount

Where: sale_made.sid = sale_by.sid

I have this search that works:

sourcetype=sale_by  | join sid [ search sourcetype=sale_made ] | stats sum(amount)  by user

Can this be done more efficiently with stats?

1 Solution

MuS
Legend

Hi eddiet,

Sure, try this:

 sourcetype=sale_by OR sourcetype=sale_made 
| stats values(user) AS user sum(amount) AS amount by sid 
| stats values(amount) AS amount by user

This is not tested and also depends on your events and the expected result, but it should give you an idea how it can be done.
You can read this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to learn more about this topic.

Hope this helps ...

cheers, MuS

View solution in original post

woodcock
Esteemed Legend

Try this:

sourcetype=sale_by OR sourcetype=sale_made
| stats values(user) AS user sum(amount) as amount BY sid
| stats sum(amount) as amount BY user

MuS
Legend

Hi eddiet,

Sure, try this:

 sourcetype=sale_by OR sourcetype=sale_made 
| stats values(user) AS user sum(amount) AS amount by sid 
| stats values(amount) AS amount by user

This is not tested and also depends on your events and the expected result, but it should give you an idea how it can be done.
You can read this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to learn more about this topic.

Hope this helps ...

cheers, MuS

woodcock
Esteemed Legend

Wow, almost a jinx!

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

.conf25 Global Broadcast: Don’t Miss a Moment

Hello Splunkers, .conf25 is only a click away.  Not able to make it to .conf25 in person? No worries, you can ...

Observe and Secure All Apps with Splunk

 Join Us for Our Next Tech Talk: Observe and Secure All Apps with SplunkAs organizations continue to innovate ...

What's New in Splunk Observability - August 2025

What's New We are excited to announce the latest enhancements to Splunk Observability Cloud as well as what is ...