Splunk Search

Lookup csv file not producing correct results

Explorer

Hello,
I have a lookup file called fssrcmactg.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.

0 Karma

Explorer

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.

index=myindex | stats count as status by srcmac | inputlookup append=true srcmac.csv | stats max(status) as status by srcmac | fillnull value="notfound" | search status="not_found"

0 Karma

Esteemed Legend

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"
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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]
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

Yup, it would. I fixed the answer.

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Super Champion

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

0 Karma

Explorer

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 srcmac listed in the lookup, if a srcmac is not found show me the src_mac. Seemed simple. Thanks again for helping.

0 Karma