topic Re: Need help with a query in Splunk Search

Need help with a query

satyaallaparthi — Wed, 10 Jul 2024 17:06:53 GMT

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:

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 🙂

Re: Need help with a query

JohnEGones — Wed, 10 Jul 2024 16:37:40 GMT

This may be useful to you:

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

Re: Need help with a query

yuanliu — Wed, 10 Jul 2024 19:25:53 GMT

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)

Re: Need help with a query

PickleRick — Wed, 10 Jul 2024 19:55:59 GMT

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.

Re: Need help with a query

satyaallaparthi — Thu, 11 Jul 2024 00:57:07 GMT

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

Re: Need help with a query

yuanliu — Thu, 11 Jul 2024 05:33:03 GMT

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

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?