topic Re: How to search across three sourcetypes using stats, but without using join, append, or subsearch? in Splunk Search

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

chrisw3 — Fri, 09 Dec 2016 23:19:15 GMT

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?

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

MuS — Sat, 10 Dec 2016 08:42:09 GMT

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-join-append-or-use-of-subsearches.html to get more ideas how to solve this problem. This answer is expandable to more sources / sourcetypes / indexes 😉

Hope this helps ...

cheers, MuS

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

chrisw3 — Mon, 12 Dec 2016 16:47:37 GMT

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

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

somesoni2 — Mon, 12 Dec 2016 17:02:10 GMT

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

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

chrisw3 — Wed, 25 Oct 2017 21:27:05 GMT

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!