topic Re: Lookup search from another index in Splunk Search

Lookup search from another index

bimatomsoc — Sun, 17 Sep 2023 16:01:54 GMT

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.

Re: Lookup search from another index

richgalloway — Sun, 17 Sep 2023 18:26:50 GMT

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).

Re: Lookup search from another index

bimatomsoc — Sun, 17 Sep 2023 18:41:13 GMT

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.

Re: Lookup search from another index

bowesmana — Sun, 17 Sep 2023 23:59:02 GMT

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.

Re: Lookup search from another index

richgalloway — Mon, 18 Sep 2023 12:12:26 GMT

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.