topic Re: Lookup csv file not producing correct results in Splunk Search

Lookup csv file not producing correct results

wtaylor149 — Wed, 30 Sep 2020 02:28:28 GMT

Hello,
I have a lookup file called fs_src_mac_tg.csv
has two columns:
src_mac and exists
src_mac = a list of mac addresses
exists = yes

Search:

index=myindex | stats count by src_mac signature | lookup fs_src_mac_tg.csv src_mac OUTPUT exists | fillnull value="no" exists  | search exists="no"

What I'm looking to get is if a mac in the lookup file has not been seen in my search, report that src_mac

Thanks in advance for the help.

Re: Lookup csv file not producing correct results

cmerriman — Thu, 03 Oct 2019 20:08:14 GMT

try appending the lookup instead:

index=myindex | stats count by src_mac signature | inputlookup append=t max=0 fs_src_mac_tg.csv | fillnull value="no" exists |eventstats values(exists) as exists by src_mac| search exists="no"

you might need to edit it a bit, but by appending it to the bottom, you'll get all results from the lookup, instead of joining the src_mac to the rows that exist from the search.

https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Inputlookup
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Lookup

Re: Lookup csv file not producing correct results

richgalloway — Thu, 03 Oct 2019 20:10:36 GMT

How about this? It looks in myindex with src_mac not in the lookup file.

index=myindex NOT [|inputlookup fs_src_mac_tg.csv | fields src_mac | format]

Re: Lookup csv file not producing correct results

woodcock — Fri, 04 Oct 2019 02:40:37 GMT

Try this:

index=myindex | stats count by src_mac signature
| eval which="data"
| inputlookup append=true fs_src_mac_tg.csv src_mac
| eval which=coalesce(which, "lookup")
| stats values(*) AS * dc(which) AS which_count BY src_mac
| where which_count==1 AND which="lookup"

Re: Lookup csv file not producing correct results

wtaylor149 — Fri, 04 Oct 2019 10:44:52 GMT

Thanks for the reply. Unfortunately this did not work. Even errored out on the "src_mac" after the lookup.

Re: Lookup csv file not producing correct results

wtaylor149 — Wed, 30 Sep 2020 02:28:44 GMT

Thanks for the reply but this also did not produce the results. This search seems like it should work. I want to search for only the src_mac listed in the lookup, if a src_mac is not found show me the src_mac. Seemed simple. Thanks again for helping.

Re: Lookup csv file not producing correct results

wtaylor149 — Fri, 04 Oct 2019 10:49:15 GMT

I really appreciate the help but this did not produce the results I was looking for, unfortunately.

Re: Lookup csv file not producing correct results

wtaylor149 — Wed, 30 Sep 2020 02:28:48 GMT

I think I was making this way more complicated than it had to be. The below search worked perfect. Thanks again for all the help. By far Splunk folks are the best and willing to help out.

Re: Lookup csv file not producing correct results

richgalloway — Fri, 04 Oct 2019 12:39:24 GMT

Yup, it would. I fixed the answer.