Splunk Search

How to write a search to correlate data from multiple indexes?

sunitachan
New Member

Hello All,
I am very new to Splunk.
Can someone help me with this use case please:

I have to create a search which should take an IP coming from a data source A and take that IP go to a file grab some info against that IP (like host name/location) sitting in index B. So being newbie I think I can do a search for IP
index=A IP=xxx.xxx.xx.xxx
what should be the second part of the search?
Any help is appreciated!

Tags (2)
0 Karma
1 Solution

MuS
SplunkTrust
SplunkTrust

Hi sunitachan,

This is maybe difficult to understand at first, but take a look at this answer http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi... to get an idea hoe this can be done.

Happy splunking ...

cheers, MuS

View solution in original post

0 Karma

aalanisr26
Path Finder

index A
ip=1.1.1.1 myfield=x
ip=1.1.1.2 myfield=y

index B
ip=1.1.1.1 name=Adrian
ip=1.1.1.2 name=Alanis

index =A OR index= B |transaction ip | table ip, myfield, name
1.1.1.1, x, Adrian
1.1.1.2, y, Alanis

0 Karma

damode
Motivator

How would you write a query if ip is named as client_ip in index B ? basically if same field value has different field name in another index .

0 Karma

MuS
SplunkTrust
SplunkTrust
 ... | eval correlation_field=case(isnotnull(ip), ip, isnotnull(client_ip), client_ip, 1=1, "unknown")
 | stats values(*) AS * by correlation_field

cheers, MuS

0 Karma

damode
Motivator

Hi MuS,

In my two indexes,

index=a
host=system
action=deleted
userid
ip

index=b
client_ip
sender

I am trying to figure out a query that will match ip from index A with client_ip of index B and merge results giving userid, ip sender and action as tables.

I have tried below query, but it only gave me results from index a

index=a host=system action=deleted OR index=b |transaction ip |table userid, ip, action, sender | eval correlation_field=case(isnotnull(ip), ip, isnotnull(client_ip), client_ip, 1=1, "unknown")
  | stats values(*) AS * by correlation_field
0 Karma

MuS
SplunkTrust
SplunkTrust

Just try:

( index=a host=system action=deleted ip=* ) OR ( index=b client_ip=* sender=* )
| fields userid, ip, action, sender, client_ip
| eval correlation_field=case(isnotnull(ip), ip, isnotnull(client_ip), client_ip, 1=1, "unknown")
| stats values(*) AS * by correlation_field

cheers, MuS

0 Karma

damode
Motivator

I am getting expected results along with results specific to each index with no IP matching.

one result from just index A is,

action, ip, userid

next is both results merged with IP matching (expected result)

action, ip, client_ip , sender, userid

another with results from just index B

action, client_ip , sender.

and I noticed Index B also has same field "action" like index A but with different values.

0 Karma

MuS
SplunkTrust
SplunkTrust

Look, I gave you an example how it can be done and you have the data available. All you need to do is adapt the search and try adding or removing fields before and after the stats to get the expected result.

cheers, MuS

0 Karma

sunitachan
New Member

Thanks for the note!!

0 Karma

harishbenne2
Explorer

But how do we do if the field names are different in both indexes?

Example:
If Index A lists ip address as IP and Index B lists it as IPaddr

0 Karma

MuS
SplunkTrust
SplunkTrust

hmm, exactly as already posted and described below ....

 ... | eval correlation_field=case(isnotnull(IP), IP, isnotnull(IPaddr), IPaddr, 1=1, "unknown")
 | stats values(*) AS * by correlation_field

cheers, MuS

martin_mueller
SplunkTrust
SplunkTrust

... coalesce() ... 😛

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

Alternatively to @MuS's approach of joining data, for using info from one search to find things in another search you can use this pattern:

index=B [search index=A identifying things in index A | dedup IP | fields IP] | ...

That'll search index A for events containing your IP value and then use the values returned to search index B.

0 Karma

sunitachan
New Member

Thanks a lot!!

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi sunitachan,

This is maybe difficult to understand at first, but take a look at this answer http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi... to get an idea hoe this can be done.

Happy splunking ...

cheers, MuS

View solution in original post

0 Karma

sunitachan
New Member

Thank you MuS, I will read thru this and let you know if it works.

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!