Splunk Search

How to search across three sourcetypes using stats, but without using join, append, or subsearch?

chrisw3
Explorer

I'm currently working on 3 separate data sourcetypes that have similar information

Sourcetype 1 - Fields X,Y,Z
Sourcetype 2 - Fields A,Z
Sourcetype 3 - Fields A,B,C

I'd like to search across these 3 sourcetypes and collect stats information for things like Field X by Field B or C, but I'm struggling with how to complete this search without defaulting back to using joins, appends, subsearches, or some other suboptimal method.

I was trying the following search but kept running into the issue that only sourcetype 2 had both fields A & Z and the other sourcetypes would be dropped and my interesting fields with it:

(sourcetype=1) OR (sourcetype=2) OR (sourcetype=3) 
| stats values(*) as * by A,Z

I also attempted to add a fillnull command, but was still met with a lack of interesting fields correlating correctly:

(sourcetype=1) OR (sourcetype=2) OR (sourcetype=3) 
| fillnull A,Z
| stats values(*) as * by A,Z

The ultimate Goal is to have a table that lists a single row with X,Y,Z,A,B,C. From there I can begin to manipulate into other relevant stats, but I just can't figure out how to make that happen without using a join.

Any suggestions?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Assuming, field A and Z have one to one mapping, give this a try

(sourcetype=1) OR (sourcetype=2) OR (sourcetype=3) 
| eventstats values(A) as A_common by Z | eval A=coalesce(A,A_common) 
| eventstats values(Z) as Z_common by A | eval Z=coalesce(Z,Z_common)
| stats values(*) as by A,Z

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Assuming, field A and Z have one to one mapping, give this a try

(sourcetype=1) OR (sourcetype=2) OR (sourcetype=3) 
| eventstats values(A) as A_common by Z | eval A=coalesce(A,A_common) 
| eventstats values(Z) as Z_common by A | eval Z=coalesce(Z,Z_common)
| stats values(*) as by A,Z

chrisw3
Explorer

The fields were one to one mapped and this answer did actually work for me.

Coming back to mark as the answer and say thanks!

0 Karma

chrisw3
Explorer

Clarifying the stats command done in the search since it looks like it didn't paste properly...

(sourcetype=1) OR (sourcetype=2) OR (sourcetype=3)
| stats values(*) as * by A,Z

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi chrisw3,

the easiest way to get what you want is to use the max() option with stats but since you did not provide enough information on your actual use case, I don't know if this will work for you or not. But you can try this run everywhere search to get an idea who it works:

index=_internal sourcetype=splunkd OR sourcetype=splunkd_access OR sourcetype=splunk_ui_access | stats max(*) AS *

This will list you all max values for all fields from all source types on one row.

Maybe it's worth to check this answer as well https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... to get more ideas how to solve this problem. This answer is expandable to more sources / sourcetypes / indexes 😉

Hope this helps ...

cheers, MuS

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...