Lookup search from another index

[bimatomsoc]

There are some values of IP addresses from `cim_Authentication_indexes`.

This index is for look up.

I want to make if the IP addresses from `cim_Authentication_indexes` are in the second lookup index.

I tried making some query but it quite something wrong. 

(`cim_Authentication_indexes`) tag=authentication NOT (action=success user=*$)
| table dest, dst, Ip, source_ip, src_ip, src
| eval IP_Addr = coalesce(dest, dst, Ip, source_ip, src_ip, src)
| append [search index="tml_it-mandiant_ti" type=ipv4 | table value]
| stats count by IP_Addr, value
| where count >= 1

Please correct this and help me out. Thanks.

[richgalloway]

If I understand the use case, you can achieve the goal using a subsearch.

(`cim_Authentication_indexes`) tag=authentication NOT (action=success user=*$)
| table dest, dst, Ip, source_ip, src_ip, src
| eval IP_Addr = coalesce(dest, dst, Ip, source_ip, src_ip, src)
| search [search index="tml_it-mandiant_ti" type=ipv4 | return 10000 IP_Addr=value]
| stats count by IP_Addr
| where count >= 1

The subsearch will return a list of up 10,000 IP addresses in the form (IP_Addr=1.2.3.4 OR IP_Addr = 2.3.4.5 OR ...) which the search command will use to filter results from cim_Authentication_indexes.  The key thing is make sure the field name returned from the subsearch exists in the data from the main search (in the example, IP_Addr rather than value).

[bimatomsoc]

Thank you for your quick response. Your query makes sense for me.
But, it doesn't fully work. I think we should change the whole search. The function I used "coalesce" collects the first field value. If the dest value is not null, it only collects the dest, not collecting the rest of the values like src, src_ip.
As far as I researched, I think I should use lookup and OUTPUT function to matches all the IPs(src, dest,..) with lookup index's IPs.

[richgalloway]

Please explain what you mean by "it doesn't fully work"?  How does it fall short?

What exactly are you trying to do with the coalesce function?

Rather than ask how to use specific commands, I suggest you explain your inputs and desired outputs.  Then someone can recommend a query.

[bowesmana]

You can't use lookup on an index, only a lookup table.

I think this should work and won't have the limitations of the subsearch

((`cim_Authentication_indexes`) tag=authentication NOT (action=success user=*$))
OR (index="tml_it-mandiant_ti" type=ipv4)
| eval origin=if(index="tml_it-mandiant_ti", "mandiant", "auth")
| eval IP_Addr = coalesce(value, dest, dst, Ip, source_ip, src_ip, src)
| stats dc(origin) as origins by IP_Addr
| where origins=2

so, it sets a new field 'origin' to be where the IP address is coming from and if the event is from the tml_it-mandiant_ti index., IP_Addr will be the value, otherwise it will be the other IP address from your original coalesce.

Then just stats and count the number of origins you find. You need it to be 2, indicating the IP address is in both indexes.