Need help with a query

[satyaallaparthi]

How can I match the IPs from csv file with the CIDR ranges in another csv? If no CIDR matches, I want to return "NoMatch" and if proper IP and CIDR match then return the CIDR 

I tried the approach below, but I keep getting "No Match" for all entries, even though I have proper CIDR ranges:

"| inputlookup IP_add.csv
| rename "IP Address" as ip
| appendcols
[| inputlookup cidr.csv]
| foreach cidr
[ eval match=if(cidrmatch('<<FIELD>>', ip), cidr, "No Match")]"

Note: I can't use join as I don't have IP field or ips in the cidr csv

any help would be greatly appreciated. Thank you 🙂

[PickleRick]

Adding to already provided answer, your idea wouldn't work because appendcols adds fields from the appended dataset to the original results row-by-row (in a as-is order). So in your case a first row from the second lookup would "extend" first row of contents of the first lookup, second row would be glued to second row and so on.

Also your "foreach cidr", since you're only specifying a single field would yield the exactly same results as if you simply wrote your eval using "cidr" instead of "<<FIELD>>". And since most probably your cidr and ip fields didn't happen to "join" so that they landed in matching rows, your result was always a no-match.

I suppose you wanted to add a transposed contents of the second lookup to each result of your initial inputlookup search but it doesn't work that way.

[yuanliu]

As @JohnEGones suggested, cidrmatch is not the answer.  Set MATCH_TYPE(cidr) in cidr.csv following that document, then use lookup command.

| inputlookup IP_add.csv
| rename "IP Address" as ip
| lookup cidr.csv cidr as ip output cidr
| eval match=if(isnull(cidr), "No Match", cidr)

[satyaallaparthi]

that solution will work when we have a common field in both, but that's the case here

[yuanliu]

---

that solution will work when we have a common field in both, but that's the case here

---

What do you mean?  You don't need "common" field, if by that you mean identical entries.  Consider these two:

IP_add.csv

ip

10.110.1.152

10.16.8.11

10.16.8.240

cidr.csv

cidr

10.16.8.0/24

If cidr.csv is set up with MATCH_TYPE(cidr), the above search will give you

cidr	ip	match
	10.110.1.152	"No Match"
10.16.8.0/24	10.16.8.11	10.16.8.0/24
10.16.8.0/24	10.16.8.240	10.16.8.0/24

Have you tried?

[JohnEGones]

This may be useful to you:

https://docs.splunk.com/Documentation/Splunk/9.2.2/SearchReference/Lookup#2._IPv6_CIDR_match_in_Splu...