Splunk Search

Join data from 2 indexes

sekhar463
Path Finder

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
Labels (3)
0 Karma

PickleRick
SplunkTrust
SplunkTrust

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.

 

 

0 Karma

sekhar463
Path Finder

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
 
 
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

sekhar463
Path Finder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @sekhar463,

please try this regex:

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

Ciao.

Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...