topic Re: Need help with Lookup and Auto Lookup in Splunk Search

Need help with Lookup and Auto Lookup

lakromani — Mon, 28 Sep 2020 16:57:13 GMT

I do have a solution to get guest logged into our network. This gives nice logs that I get into Splunk. My goal is to have some idea on where they come from. The solution does use different sub-nets for different location

So I then tried to use Lookups to convert sub-nets to site name. Its very hard to find real good example on how to use this, and when I do find some, it does not show how to use it and how to get the result to the display.

Here is how the log looks like:

Jun 28 08:10:08 172.30.112.1 PORTAL: 172.30.60.49 redirected to portal "Default-SMS-Portal" with rule "rul.Orlando-S"
Jun 28 08:09:18 172.30.112.1 PORTAL: Login failed for 172.30.33.63 - account 45234345 is not valid from this location.
Jun 28 08:08:18 172.30.112.1 AAA: 172.30.36.57 logged in with username 004526243545

As you can see there is some naming, I can see Orlando in a rule, but I like a field list:

loc=Orlando client_ip=172.30.60.49 (client IP is extracted using Field Extractions)

Here is what I have done:

location.csv

Subnet,Site
172.30.33.0/24,Dallas
172.30.36.0/24,Washington
172.30.60.0/,Orlando

Settings -> Lookups -> Lookup table files:

New

Destination app: search

Upload a lookup file: location.csv

Destination filename: location.csv

Settings -> Lookups -> Lookup definitions:

New

Destination app: search

Name: site_lookup

Type: File-based

Lookup file: location.csv (Name I used in "Destination filename")

Settings -> Lookups -> Automatic lookups:

New

Destination app: search

Name:auto_site_lookup

Lookup table: site_lookup (the one created in "Lookup definitions"

Apply to:

sourcetype syslog

Lookup input fields: Subnet (same as left data from CSV file)

Lookup output fields: Site (same as right data from CSV file)

I am not sure "syslog" is the correct selection, yes its syslog, but not from Cisco and is it correct data type (date format etc). I could have used host "172.30.112.1" since all data coming from it.

Ok so know what???

I do not see any changes to my searches, no new fields, nothing converted. How does this "Lookup" know that it should use the second IP (one I extract with "Field Extractions") and not the host IP from the log.

I really like this to work.

Thanks, and sorry for the long post.

Re: Need help with Lookup and Auto Lookup

rsennett_splunk — Mon, 28 Sep 2020 16:57:26 GMT

When you fill in the form for Automatic lookup form, it asks you for both the lookup field and the event field.
(lookup field on the left, event field on the right)

You might want to look at item 6 in the doc section on the subject. Read the explanation below the image.
http://docs.splunk.com/Documentation/Splunk/6.1.1/SearchTutorial/Usefieldlookups#Make_the_lookup_automatic

In your case, you need one additional edit that cannot be done through the GUI
Depending on your permission settings, the matching transforms.conf will most likely be found in $SPLUNK_HOME/etc/users/yourusername/local/trasnforms.conf

Look for the definition of your lookup table "loopup"
You'll see:
[site_loopup] filename = location.csv

You must add the line:

match_type = CIDR(Subnet)

because your lookup table uses the CIDR. Otherwise it will compare the ip address to what is basically garbage and not a match. So, when you find the definition in transforms.conf and edit it, it will look like this:

[site_loopup] filename = location.csv match_type = CIDR(Subnet)

After you make the change, run the following search:

index=yourindexname sourcetype=syslog|extract reload=true

You will now see the Site field in your field list.

For an explanation on all the directives you can use regarding a lookup definition, check the transforms.conf.spec here

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

and take note of the following section:

match_type = * A comma and space-delimited list of () 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

Re: Need help with Lookup and Auto Lookup

lakromani — Sun, 29 Jun 2014 07:51:29 GMT

Hi, I have tried to fill inn only one side and also both side of the "Lookups field" with no changes as I can see. I am still not sure where to to see and how to see the lookups works. Do it aromatically changes the IP subnet in the search result to names, or do I see it as fields below the log line in search. PS I have read the tutorial you linked to, but it does only show how to set it up, not where to see it used and to see the result of the lookups 😞

Re: Need help with Lookup and Auto Lookup

rsennett_splunk — Sun, 29 Jun 2014 15:58:58 GMT

you are most likely missing the directive "match_type" in your transforms.conf.
You cannot add this via the GUI. See my addendum to the answer.

Re: Need help with Lookup and Auto Lookup

lakromani — Mon, 28 Sep 2020 16:58:18 GMT

Thanks, that was missing. I do added "match_type = CIDR(Subnet)", but still not working. I did take the name "Subnet" from first field in the CSV file. Still does not get any location searching for an IP within range. Eks: 172.30.33.24 should give Dallas. PS stupid that this is not in the gui to change. And when I did some change in GUI, it remove the "match_type"

Re: Need help with Lookup and Auto Lookup

lakromani — Mon, 28 Sep 2020 16:58:20 GMT

Update. I do see some post on splunk Answer tells me to update "$SPLUNK_HOME/etc/system/default/transforms.conf", by my file (that is created automatically when using GUI) is located in "$SPLUNK_HOME/etc/apps/search/transforms.conf". What to use? Copy my config to the one in System? Both? or Apps?

Re: Need help with Lookup and Auto Lookup

lakromani — Tue, 01 Jul 2014 11:33:09 GMT

Update2. Not helping to change any of the files. PS fixed small type in loopup, correct lookup. Also tried this: "host="172.30.112.1" | lookup site_lookup Subnet OUTPUT Site" found here: http://answers.splunk.com/answers/57094 Not helping