I have an index=logs that has an ip_address field like 22.214.171.124
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?
start end host site range1 range2
126.96.36.199 188.8.131.52 webexxpurts.com http://webexxpurts.com/ 184.108.40.206/22 220.127.116.11/24
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.
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 ? 🙂
CIDR matching is defined in
transforms.conf per lookup.
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)