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.
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 ...
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
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
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!