Is it possible to use a field's value as a field name for the join command?

[ITSX]

I've got an index full of events that have hostname, and some have macaddr. I'm trying to join it to another set of events that have hostname, and some have macaddr. Currently I have this. I'm basically trying to figure out how to use the value in searchfield as the field name for join. Is this possible?

...
|eval searchfield=if(isnull(macaddr),"hostname","macaddr")
|join type=left {searchfield} [...]

[javiergn · Accepted solution]

Coming from an SQL background I used to rely a lot on the join command but I wouldn't recommend using it as there are more efficient alternatives in Splunk. Also keep in mind join has limit and it will only process up to 10,000 events (if I remember correctly), which in some cases might lead to unexpected results.

You can use stats for instance. There are lots of alternatives. See this entry.
And if you run a quick search around you'll find lots of good examples. For instance:

https://answers.splunk.com/answers/221304/alternatives-to-join-with-two-matching-event-field.html
https://answers.splunk.com/answers/34107/joining-two-searches-with-and-without-stats-command.html
https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

Hope that helps,
J

[jplumsdaine22]

Also you may want to look at the docs for coalesce

+1 javiergn. After learning to use stats and chart correctly, I have never needed to use join.

Remember there are no tables involved, so you shouldn't need joins. Your initial search should already contain all the data you need (The union? A SQL expert may be able to describe this better).

If you don't know the full result set in advance (ie you're relying on field values) try a subsearch instead of join.

[stephanefotso]

Hello! Here is all what you must know about the join command: http://docs.splunk.com/Documentation/Splunk/6.3.1511/SearchReference/Join

Thanks