Join data from 2 indexes

[sekhar463]

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

[PickleRick]

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.

[sekhar463]

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

[gcusello]

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

[sekhar463]

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

[gcusello]

Hi @sekhar463,

please try this regex:

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

Ciao.

Giuseppe

[gcusello]

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