topic Re: Join data from 2 indexes in Splunk Search

Join data from 2 indexes

sekhar463 — Mon, 06 Nov 2023 16:56:45 GMT

Hi All,

i have 2 indexes having below 2 queries

host,hostname are common for both, want to add sourceIp using 2nd search

How to join ?

query 1

index="index1" \ (puppet-agent OR puppet)) AND *Error* AND "/Stage[" | table host

query2;

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections | table hostname sourceIp | dedup hostname

Re: Join data from 2 indexes

gcusello — Mon, 06 Nov 2023 16:02:52 GMT

Hi @sekhar463 ,

let me understand: do you want only hosts present in both searches or what's the rule?

if present in both searches:

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections [ search index="index1" \ (puppet-agent OR puppet)) AND *Error* AND "/Stage[" | rename host AS hostname | fields hostname ] | table hostname sourceIp | dedup hostname

Ths search runs if results are less than 50,000, if they are more than 50,000 you need a different approach:

(index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections) OR (index="index1" \ (puppet-agent OR puppet)) AND *Error* AND "/Stage[") | eval hostname=coalesce(hostname,host) | stats values(sourceIp) AS sourceIp dc(index) AS index_count BY hostname | where index_count=2 | fields - index_count

Ciao.

Giuseppe

Re: Join data from 2 indexes

sekhar463 — Thu, 09 Nov 2023 10:04:24 GMT

getting error

Error in 'search' command: Unable to parse the search: unbalanced parentheses using below search

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections [ search index="INDEX1" \ (puppet-agent OR puppet)) AND *Error* AND "/Stage["
| rename host AS hostname | fields hostname ]
| table hostname sourceIp
| dedup hostname

Re: Join data from 2 indexes

gcusello — Thu, 09 Nov 2023 11:25:38 GMT

hi @sekhar463,

please try this:

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log" group=tcpin_connections [ search index="INDEX1" "\" (puppet-agent OR puppet) AND *Error* AND "/Stage[" | rename host AS hostname | fields hostname ] | table hostname sourceIp | dedup hostname

there was a wrong parenthesis.

Ciao.

Giuseppe

Re: Join data from 2 indexes

sekhar463 — Wed, 15 Nov 2023 10:08:59 GMT

individual search is working for below which extracts host_name field and joining with host_name field in search but getting error "

Error in 'rex' command: Invalid argument: '('

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log"
| rex field=hostname "(?<host_name>[^.]+)\."

but its giving less results when using below search but individual search has many

here is the full query

index=_internal sourcetype=splunkd source="/opt/splunk/var/log/splunk/metrics.log"
| rex field=hostname "(?<host_name>[^.]+)\."
[

| table host_name, sourceIp

Re: Join data from 2 indexes

PickleRick — Tue, 14 Nov 2023 12:56:22 GMT

1. This part

| table hostname sourceIp
| dedup hostname

You realize that you will lose additional IP addresses on multihomed hosts?

2. Depending on your data (number of results, size of raw events, time of each search execution) there could be different ways to do that.

There is a "join" command but its use is generally discouraged.

The typical way is to either append two result sets and do stats by the common field(s) or do a search across two sets, classify the fields into one of the sets (possibly rename fields) and then do the stats.

Re: Join data from 2 indexes

gcusello — Tue, 14 Nov 2023 13:55:42 GMT

Hi @sekhar463,

please try this regex:

| rex field=hostname "(?<host_name>[^\.]+)\."

Ciao.

Giuseppe