Alternates to join/append to avoid limitations

[kaeleyt]

Situation: I have 2 data sets:

Dataset 1 is a set of logs which includes IP addresses. When aggregated, there are 200,000+ IP addresses.

Dataset 2 is a dataset we are pulling in once a day which includes identifying information for those IP addresses including hostname for example. This dataset is even larger.

I'm wanting to map the hostname from Dataset 2 to the IP address in Dataset 1. I feel like I've tried everything (join, append + eventstats, subsearching) and unfortunately all have a limit which prevent me from getting the full set mapped.

Join limit: 50,000

Append limit: 10,000

Subsearch limit: 10,000

I've come across this same sort of issue before and have dropped projects because there doesn't seem to be an obvious way to get around these limits without increasing limits like the subsearch_maxout for example for our whole environment by at least 10x. I've started looking into the map command but the documentation seems extremely vague on the limits ("Zero ( 0 ) does not equate to unlimited searches.")

The only thing I've gotten to work is to essentially manually break the 2nd data source up into groups of  10000 or less rows and append + eventstats each group of 10,000 one by one by one which is a complete nightmare of a query if you can imagine that plus, additional appends need to be created anytime the 2nd data set changes or grows.

I'm growing tired of not having a good way of tackling this issue so I'm seeking any advice from any fellow Splunkers that have successfully "joined" larger datasets.

Some example searches to help with the situation:

Dataset 1 search:

index=my_logs
| stats count by ip

 Dataset 2 search:

index=my_hosts
| stats values(hostname) as hostname by ip

[livehybrid]

Hi @kaeleyt 

Use the Splunk lookup feature by saving Dataset 2 (ip-to-hostname mapping) as a CSV lookup file and then using the lookup command to enrich Dataset 1. This fully bypasses subsearch, join, and append limits.

Create CSV lookup table from Dataset 2

index=my_hosts
| stats values(hostname) as hostname by ip
| outputlookup ip_to_hostname.csv

Join Dataset 1 and lookup to enrich logs with hostnames

index=my_logs
| stats count by ip
| lookup ip_to_hostname.csv ip OUTPUT hostname
| table ip, count, hostname

The lookup command does not have the same limiting factors as join, append, or subsearch for reasonable file sizes, you could use either CSV or kvstore lookups.

If Dataset 2 changes regularly you could overwrite the lookup via a scheduled search.

For very large lookups, Splunk recommends KV store lookups for scale, but CSV lookups generally perform well up to 1M+ rows.

Confirm that the field names (ip, hostname) match exactly between lookup and base data.

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[MuS · Accepted solution]

Hi there,

Oldy but goldy 😉 

https://community.splunk.com/t5/Splunk-Search/How-to-compare-fields-over-multiple-sourcetypes-withou...

Hope this helps ...

Cheers, MuS

[kaeleyt]

@MuSThat got me part of the way there but I think I may have accidentally oversimplified my question a bit. I'll post another question to get the 2nd half answered. Thanks for the help!