1

I have 2 lookup files as

  1. lookup1.csv and
  2. lookup2.csv

lookup1.csv has the data as below

name, designation, server, ipaddress, dept
tim, ceo, hostname.com, 1.2.3.5, alldept
jim, vp, myhost.com, 1.0.3.5, marketing
pim, staff, nohost.com, 4.0.4.8, hr

lookup2.csv has the data as below

cidr, location
1.2.3.0/24, dc
1.0.3.0/24, carolina
3.4.7.0/24, tx

I would like to lookup for the field ipaddress in lookup1.csv with the field cidr in lookup2.csv for the first 3 digits as in x.x.x and get the location field if they match. If the ipaddress doesn't match the first 3 digit of cidr , the location should be marked as "unknown".

Expected o/p

tim, ceo,1.2.4.5, dc
jim, vp, 1.0.3.5, carolina
pim, staff, 4.0.4.8, unkown

I am looking for the search command in splunk using the 2 lookup tables. Thanks in advance. My search so far has not yield any good results but I am still working on it.

Bond
  • 855
  • 3
  • 13
  • 24
  • What have you tried so far? – RichG Aug 12 '23 at 11:28
  • Have you enabled CIRDMATCH() on lookup2.csv in a [Lookup Definition](https://docs.splunk.com/Splexicon:Lookupdefinition)? Or via the [`cidrmatch`](https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/ConditionalFunctions#lookup.28.26lt.3Blookup_table.26gt.3B.2C.26lt.3Bjson_object.26gt.3B.2C.26lt.3Bjson_array.26gt.3B.29) command? – warren Aug 12 '23 at 13:07
  • 1
    Got help from another expert and using CIDR(cidr) in the lookup. This is working for me so far. Working on to display an "unknow" value when lookup doesn't exists. | inputlookup lookup1.csv | lookup lookup2.csv cidr AS ipaddress OUTPUT location | table name designation server ipaddress dept location – Bond Aug 12 '23 at 14:00

1 Answers1

1

Use the isnull() function to determine if the lookup was successful or not.

| inputlookup lookup1.csv 
| lookup lookup2.csv cidr AS ipaddress OUTPUT location 
| eval location=if(isnull(location), "unknown", location)
| table name designation server ipaddress dept location
RichG
  • 9,063
  • 2
  • 18
  • 29
  • 1
    Thanks @RichG. I was using fillNull but this looks more better. – Bond Aug 12 '23 at 22:01
  • @Bond - honestly ...`fillnull value="unknown"` is simpler to write. But RichG's suggestion to `eval if...` is more *obvious* in what it is doing :) – warren Aug 18 '23 at 18:29