How to search with cidrmatch with multiple subnets?

[bosseres]

Hello everyone,

I got such table after search

ip	subnets
10.0.0.2	10.0.0.0/24
10.0.0.3	10.0.0.0/24 172.24.23.23/24

I want to compare if ip belongs to subnets, using next one comparison

| eval match=if(cidrmatch(subnets, ip), "match", "nomatch")

It works correct if there is one subnet, but if more - not, how can I correct my search query?

[ITWhisperer]

If subnets is a multi-value field, use mvexpand before the eval, otherwise use split to create a multi-value field and mvexpand.

[bosseres]

Yes, I have multivalue field, I did

| mvexpand subnets

but how to make comparison, if ip belongs TO ONE of this subnets - then alert?

because now it checks the compliance of each ip with each subnet, for my example table 

ip	subnets
10.0.0.2	10.0.0.0/24
10.0.0.3	10.0.0.0/24 172.24.23.23/24

search will find 10.0.0.3 which not matches 172.24.23.23/24, but I need make search where if 10.0.0.3 matches even one of subnets

[bosseres]

I mean I want to do, if ip matches at least one of subnets - then field match=match

[yuanliu]

This is confusing as @ITWhisperer already explained you could use mvexpand.  Can you explain why this does not give you what you need?

| mvexpand subnets
| where cidrmatch(subnets, ip)

Suppose there are multiple subnets in the original table and ip matches one of them.  Is there any use of the non-matching subnets?

If there is any such use, ITWhisperer's last response covers it.

[ITWhisperer · Accepted solution]

Try something like this

| mvexpand subnets
| stats values(eval(if(cidrmatch(subnets, ip), subnets, null()))) as matches by ip

[bosseres]

thank you, sir!