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

[chrisw3]

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?

[somesoni2 · Accepted solution]

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]

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!

[chrisw3]

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

[MuS]

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