Splunk Search

Search in multiple indexes

rafamss
Contributor

Hi guys,

I need some help.

I have 2 index, and in both there are the field "ip", How can I create a search that find only results which exists the in both ? Like a join in SQL.

Like this: index1= indexX AND index2=indexY | stats count by ip

Tags (3)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try this

index=indexX |stats count by ip | join ip [search index=indexY | stats count by ip]

OR

index=indexX | table ip | join ip [search index=indexY |table ip] | stats count by ip

View solution in original post

Kishorebk
New Member

Hi Mark

I tried, it gives results, but just for one index at time.
Here is the query 0

index=XXXX OR XXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR | rex"(?[\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}][^,]+)" | rename XX_1 as IP | rename XX_2 as IP | rename XX_3 as IP | rex "\W+\s+(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}[^s+\W+\s+])" | rex "coming\s+from\s+(?\d*\D*\w*)" | rex "XXX\s+XXX\s+(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})" | rex "\W(?\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}[^,])" | rename IPAddress as IP | rename XX_4 as IP | iplocation IP | stats count values(index) by Country

0 Karma

markthompson
Builder

@rafamss @somesoni2 @Kishorebk - I'd recommend you try the OR Statement, You were close on the question, but instead of AND, you can use the OR.

e.g.

index=index1 OR index=index2 OR index=index3

etc.

0 Karma

Kishorebk
New Member

Hi Mark

I tried, it gives results, but just for one index at time.
Here is the query 0

index=XXXX OR XXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR XXXXX OR | rex"(?[\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3}][^,]+)" | rename XX_1 as IP | rename XX_2 as IP | rename XX_3 as IP | rex "W+s+(?d{1,3}.d{1,3}.d{1,3}.d{1,3}[^s+W+s+])" | rex "comings+froms+(?d*D*w*)" | rex "XXXs+XXXs+(?d{1,3}.d{1,3}.d{1,3}.d{1,3})" | rex "W(?d{1,3}.d{1,3}.d{1,3}.d{1,3}[^,])" | rename IPAddress as IP | rename XX_4 as IP | iplocation IP | stats count values(index) by Country

0 Karma

rafamss
Contributor

Thanks somesoni2, it's worked perfectly.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this

index=indexX |stats count by ip | join ip [search index=indexY | stats count by ip]

OR

index=indexX | table ip | join ip [search index=indexY |table ip] | stats count by ip

isaacyeo
Engager

Any chance that this can only be done in later versions of Splunk? I am currently on 4.3.3 using Enterprise Security on 2.0.2. When I search for this: index=indexa sourcetype=sourcea [search index=indexb sourcetype=sourceb] The search is forever ongoing even though I am only searching for the past 5 minutes.

When I search for this: index=indexa OR index=indexb source=sourceb It works but it will not return the results I require.

0 Karma

rafamss
Contributor

Thanks somesoni2, I will test and reply in soon.

0 Karma

Kishorebk
New Member

Hi

How would the query look if i have more that 10 index's to search for?

Kishore

0 Karma