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

[sunitachan]

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!

[aalanisr26]

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

[damode]

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 .

[MuS]

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

cheers, MuS

[damode]

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

[MuS]

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

[damode]

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.

[MuS]

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

[sunitachan]

Thanks for the note!!

[harishbenne2]

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

[MuS]

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]

... coalesce() ... 😛

[martin_mueller]

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.

[sunitachan]

Thanks a lot!!

[MuS · Accepted solution]

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

[sunitachan]

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