topic Re: joining ips in splunk index against a csv file with multiple CIDR ranges in Splunk Search

joining ips in splunk index against a csv file with multiple CIDR ranges

spark2310 — Tue, 29 Sep 2020 16:52:09 GMT

I have an index=logs that has an ip_address field like 5.34.244.100
I want to look up these all ip_address filed against a csv file that has ips ranges and multiple CIDRs in more than one column. (I've added csv sample at the end)

How would you write a query to match ip_address field in index=logs against csv file when there is at least one match on range1, or range2 columns? I understand that cidr match, lookup and join functions may be required here but appreciate if someone can show how to write the query?

cidrs.csv:
start end host site range1 range2

5.34.240.0 5.34.244.255 webexxpurts.com http://webexxpurts.com/ 5.34.240.0/22 5.34.244.0/24

Re: joining ips in splunk index against a csv file with multiple CIDR ranges

micahkemp — Wed, 22 Nov 2017 15:20:13 GMT

You would have to do multiple lookups to accomplish this. The first would be against range1 | lookup cidrs.csv range1 AS ip_address then another to lookup for range2 | lookup cidrs.csv range2 AS ip_address.

Doing successive lookups may end up overwriting previously looked up fields, though, so you should look into the OUTPUT and OUTPUTNEW options and choose the one that best helps you solve your problem.

When using the lookup command, if an OUTPUT or OUTPUTNEW clause is not specified, all of the fields in the lookup table that are not the match field are used as output fields. If the OUTPUT clause is specified, the output lookup fields overwrite existing fields. If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist.

Re: joining ips in splunk index against a csv file with multiple CIDR ranges

spark2310 — Wed, 22 Nov 2017 15:42:05 GMT

How would I join ip_address field in index=logs before i do these two lookups? also how to use the cidr function?

Re: joining ips in splunk index against a csv file with multiple CIDR ranges

micahkemp — Wed, 22 Nov 2017 15:45:05 GMT

I'm not sure what you mean when you say 'join ip _address filed in index=logs'.

Re: joining ips in splunk index against a csv file with multiple CIDR ranges

spark2310 — Tue, 29 Sep 2020 16:52:28 GMT

This is what I ended up with from what you described (without getting into outputnew)
"index=log | lookup cidrs.csv range1 AS ip_address | lookup cidrs.csv range2 AS ip_address"
where can cidr function be used in ip_address field index=log to match in lookup?
Is join also needed or just csv lookup?
how would you write full query based on the sample csv ? 🙂

Re: joining ips in splunk index against a csv file with multiple CIDR ranges

micahkemp — Wed, 22 Nov 2017 18:38:57 GMT

CIDR matching is defined in transforms.conf per lookup.

http://docs.splunk.com/Documentation/Splunk/latest/Admin/Transformsconf

match_type = <string>
* A comma and space-delimited list of <match_type>(<field_name>)
   specification to allow for non-exact matching
* The available match_type values are WILDCARD, CIDR, and EXACT.  EXACT is
   the default and does not need to be specified.  Only fields that should
   use WILDCARD or CIDR matching should be specified in this list

In your case you probably want something like this in your transforms.conf:

[cidrs]
filename = cidrs.csv
match_type = CIDR(range1), CIDR(range2)