topic Re: Splunk Lookup Search Not Working As Expected in Splunk Search

Splunk Lookup Search Not Working As Expected

DavidCollins65 — Wed, 18 Sep 2024 17:40:03 GMT

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?

Re: Splunk Lookup Search Not Working As Expected

ITWhisperer — Wed, 18 Sep 2024 16:00:11 GMT

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)?

Re: Splunk Lookup Search Not Working As Expected

DavidCollins65 — Wed, 18 Sep 2024 17:29:32 GMT

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)?

Re: Splunk Lookup Search Not Working As Expected

ITWhisperer — Wed, 18 Sep 2024 17:59:10 GMT

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.

Re: Splunk Lookup Search Not Working As Expected

DavidCollins65 — Wed, 18 Sep 2024 19:30:48 GMT

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

Re: Splunk Lookup Search Not Working As Expected

yuanliu — Thu, 19 Sep 2024 05:59:34 GMT

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

Re: Splunk Lookup Search Not Working As Expected

DavidCollins65 — Thu, 19 Sep 2024 07:46:59 GMT

Hi @yuanliu

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

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).

Re: Splunk Lookup Search Not Working As Expected

yuanliu — Fri, 20 Sep 2024 06:56:15 GMT

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

sourceip	sourceprovider	sourcearea	sourcezone	sourceregion	src_cidr	destip	destprovider	destarea	destzone	destregion	dest_cidr
1.1.1.116	Unit	1	Finance	2	1.1.1.1/24	10.5.5.5
10.0.0.5						2.2.2.3	Unit	2	HR	16	2.2.2.2/27
2.2.2.8	Unit	2	HR	16	2.2.2.2/27	1.1.1.90	Unit	1	Finance	2	1.1.1.1/24
192.168.8.1						10.6.0.10

Re: Splunk Lookup Search Not Working As Expected

DavidCollins65 — Sun, 22 Sep 2024 09:18:13 GMT

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.