How to find Ip's from one index has communication with other Ip's in different index?

[innoce]

I have 3 indexes containing events with IP addresses, index1, index2, and index3. My goal is to return a list of all IP addresses that are present in index1 and see if those had matches with  IP's in index 2 and index 3

3 different indexes with 3 different IP field names:
index1 , src_ip
index2, ipaddr
index3, ip

Any help would be appreciated, thank you.

[gcusello]

Hi @innoce,

you could run something like this:

index1 OR index2 OR index3
| eval IP=coalesce(src_ip,ipaddr,ip)
| stats dc(index) AS dc_index values(src_ip) AS src_ip values(ipaddr) AS ipaddr values(ip) AS ip BY IP
| eval status=case(dc_index=1 AND isnotnull(src_ip), "Present only in index1", dc_index=2 AND isnotnull(src_ip) AND isnotnull(ipaddr), "Present in index1 and index2", dc_index=2 AND isnotnull(src_ip) AND isnotnull(ip), "Present in index1 and index3", dc_index=2 AND isnotnull(ipaddr) AND isnotnull(ip), "Present in index2 and index3",dc_index=3, "Present in all indexes")
| table IP status

or more simply

index1 OR index2 OR index3
| eval IP=coalesce(src_ip,ipaddr,ip)
| stats dc(index) AS dc_index values(index) AS index BY IP
| table IP index

Ciao.

Giuseppe