Splunk Search

Splunk Lookup Search Not Working As Expected

DavidCollins65
Engager

Hello - I am trying to construct a search whereby I can do a lookup of a single table, then rename the fields and change how they're displayed, however the lookup and eval commands don't seem to be working as I would like.  The main search I am performing is basic, using some source subnets and then trying to have the lookup reference what area of the business they belong to, below is the lookup portion of my search:

index="logs" sourceip="x.x.x.x" OR destip="x.x.x.x"
| lookup file.csv cidr AS sourceip OUTPUT field_a AS sourceprovider, field_b AS sourcearea, field_c AS sourcezone , field_d AS sourceregion, cidr AS src_cidr
| lookup file.csv cidr AS destip OUTPUT field_a AS destprovider, field_b AS destarea, field_c AS destzone, field_d AS destregion, cidr AS dest_cidr
| fillnull value="none"
| eval src_details_combined=sourceprovider."-".sourcearea."-".sourcezone ."-".sourceregion
| eval dest_details_combined=destprovider."-".destarea."-".destzone."-".destregion
| eval src_details_combined=IF(src_details_combined=="none-none-none-none","notfound",src_details_combined)
| eval dest_details_combined=IF(dest_details_combined=="none-none-none-none","notfound",dest_details_combined)
| stats count values(sourceip) as sourceip values(destip) as destip by src_details_combined, dest_details_combined, rule, dest_port, app
| table src_details_combined, dest_details_combined, app, count

 

When I run the search I do get some results but the  src_details_combined and dest_details_combined fields always return as "notfound" - even though I know the IPs should match in the lookup csv.  Can anyone see where I have gone wrong in my search?

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

It is difficult to tell without seeing your events and lookup file values but taking a clue from the field name "cidr", does your lookup file contain CIDR-style values, and if so, have you created a lookup definition with advanced setting for match type CIDR(cidr)?

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

It is difficult to tell without seeing your events and lookup file values but taking a clue from the field name "cidr", does your lookup file contain CIDR-style values, and if so, have you created a lookup definition with advanced setting for match type CIDR(cidr)?

DavidCollins65
Engager

Hi @ITWhisperer  - yes you are correct, that field is populated with subnet values,  the lookup file is like this:

cidr                              provider         area                                     zone                  region

1.1.1.1/24                 Unit 1              Finance                              2                              US

2.2.2.2/27                 Unit 2              HR                                        16                           UK

I am unsure of how to go about creating a lookup definition with advanced setting for match type CIDR(cidr)?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Under settings, there is an option to change Lookups, it is there that you will find Lookup definitions - add a new one specifying the csv lookup file you want to define.

ITWhisperer_0-1726682339743.png

 

DavidCollins65
Engager

Thanks for that info, I have added the lookup definitions but the results are still outputting the same. 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

When you define lookup, did you set match type to CIDR? This is in Advanced options.

cidr-match.png

0 Karma

DavidCollins65
Engager

Hi @yuanliu 

Yeah I have it set up in the same way you have shown - 

DavidCollins65_0-1726731731906.png


I do still get results but the first two field, which should provide details of where the subnets belong just come back as the "notfound" that I have added to the search when the subnets are not part of the lookup file (I am using a dummy subnet that is 100% present in the lookup file).

DavidCollins65_1-1726732006598.png

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Forget the rest of search.  What do you get from the following?

 

index="logs" sourceip="x.x.x.x" OR destip="x.x.x.x"
| lookup file.csv cidr AS sourceip OUTPUT provider AS sourceprovider, area AS sourcearea, zone AS sourcezone , region AS sourceregion, cidr AS src_cidr
| lookup file.csv cidr AS destip OUTPUT provider AS destprovider, area AS destarea, zone AS destzone, region AS destregion, cidr AS dest_cidr
| table sourceip sourceprovider sourcearea sourcezone sourceregion src_cidr
  destip destprovider destarea destzone destregion dest_cidr

 

Is the output correct?

Using your mock lookup data, I made the following emulation

 

| makeresults format=csv data="sourceip, destip
1.1.1.116,10.5.5.5
10.0.0.5,2.2.2.3
2.2.2.8, 1.1.1.90
192.168.8.1,10.6.0.10"
``` the above emulates
index="logs" sourceip="x.x.x.x" OR destip="x.x.x.x"
```
| lookup file.csv cidr AS sourceip OUTPUT provider AS sourceprovider, area AS sourcearea, zone AS sourcezone , region AS sourceregion, cidr AS src_cidr
| lookup file.csv cidr AS destip OUTPUT provider AS destprovider, area AS destarea, zone AS destzone, region AS destregion, cidr AS dest_cidr
| fields sourceip sourceprovider sourcearea sourcezone sourceregion src_cidr
  destip destprovider destarea destzone destregion dest_cidr

 

This is what I get, exactly as expected

sourceipsourceprovidersourceareasourcezonesourceregionsrc_cidrdestipdestproviderdestareadestzonedestregiondest_cidr
1.1.1.116Unit1Finance21.1.1.1/2410.5.5.5     
10.0.0.5     2.2.2.3Unit2HR162.2.2.2/27
2.2.2.8Unit2HR162.2.2.2/271.1.1.90Unit1Finance21.1.1.1/24
192.168.8.1     10.6.0.10     
0 Karma

DavidCollins65
Engager

Thank you all very much for the help, so the issue was related to the solution @ITWhisperer gave. In my search I was referencing the table for the lookup, that should have been the definition that I created.

 

Thanks again for all the help. 

0 Karma
Get Updates on the Splunk Community!

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...

Enterprise Security Content Update (ESCU) | New Releases

In October, the Splunk Threat Research Team had one release of new security content via the Enterprise ...

Monitoring MariaDB and MySQL

In a previous post, we explored monitoring PostgreSQL and general best practices around which metrics to ...