Splunk Search

Lookup csv file not producing correct results

wtaylor149
Explorer

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.

0 Karma

wtaylor149
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 src_mac | inputlookup append=true src_mac.csv | stats max(status) as status by src_mac | fillnull value="not_found" | search status="not_found"

0 Karma

woodcock
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

wtaylor149
Explorer

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

0 Karma

richgalloway
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, Karma would be appreciated.
0 Karma

wtaylor149
Explorer

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Yup, it would. I fixed the answer.

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

cmerriman
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

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...