topic Re: How to write a search to correlate data from multiple indexes? in Splunk Search

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

sunitachan — Wed, 04 Feb 2015 19:14:17 GMT

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!

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

MuS — Wed, 04 Feb 2015 19:30:29 GMT

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-join-append-or-use-of-subsearches.html to get an idea hoe this can be done.

Happy splunking ...

cheers, MuS

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

sunitachan — Wed, 04 Feb 2015 21:56:13 GMT

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

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

martin_mueller — Wed, 04 Feb 2015 23:47:02 GMT

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.

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

aalanisr26 — Thu, 05 Feb 2015 01:25:58 GMT

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

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

sunitachan — Fri, 20 Feb 2015 17:44:45 GMT

Thanks a lot!!

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

sunitachan — Fri, 20 Feb 2015 17:44:57 GMT

Thanks for the note!!

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

damode — Thu, 14 Dec 2017 21:21:34 GMT

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 .

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

MuS — Thu, 14 Dec 2017 21:25:29 GMT

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

cheers, MuS

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

damode — Thu, 14 Dec 2017 22:09:26 GMT

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

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

MuS — Fri, 15 Dec 2017 01:00:03 GMT

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

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

damode — Fri, 15 Dec 2017 02:08:38 GMT

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.

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

MuS — Fri, 15 Dec 2017 02:51:03 GMT

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

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

harishbenne2 — Sat, 11 Aug 2018 17:41:28 GMT

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

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

MuS — Sat, 11 Aug 2018 20:59:04 GMT

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

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

martin_mueller — Sun, 12 Aug 2018 04:38:52 GMT

... coalesce() ... 😛