Splunk Search

lookup a csv if a field has certain value

mpasha
Path Finder

Good day,

I have a lookup file "Mainlookup.csv" that contains an IP address, Mac address and Host name of Clients machines that receive their IP address through DHCP. However, we have servers that are assigned static IP addresses and reside in a different subnet. I have collected IP address, host name and Mac addresses of these servers through Nmap and saved them in another CSV "Serverlookup.csv".

I have configured the Mainlookup as such so if it can not find a value for a field, it will return "No Match Found".

I was wondering if there is a way to use a condition that, if the field value is "No match found" from the first lookup, then the "serverlookup" is used to find the relevant info?

Thanks

0 Karma
1 Solution

mpasha
Path Finder

found my answer. a similar question was asked before and "elliotproebstel" answered it perfectly.
here is the answer:
base search:

index=security AND EventID=4768 
| lookup Ops_DHCP_IP2MAC Client_IP as Client_IP 

then when the results shows up and if there are Client_Name field with "NO Match Found!!" as their value we can use the following section to replace them with values coming from the second lookup

|eval Client_NMF=if (Client_Name=="No Match Found!!",Client_IP,NULL)
| lookup Ops_Servers Client_IP as Client_NMF output Client_Name,MAC_Address

View solution in original post

0 Karma

mpasha
Path Finder

found my answer. a similar question was asked before and "elliotproebstel" answered it perfectly.
here is the answer:
base search:

index=security AND EventID=4768 
| lookup Ops_DHCP_IP2MAC Client_IP as Client_IP 

then when the results shows up and if there are Client_Name field with "NO Match Found!!" as their value we can use the following section to replace them with values coming from the second lookup

|eval Client_NMF=if (Client_Name=="No Match Found!!",Client_IP,NULL)
| lookup Ops_Servers Client_IP as Client_NMF output Client_Name,MAC_Address
0 Karma

chrisyounger
SplunkTrust
SplunkTrust

Hi @mpasha

Yes you can do this. The best way is to make it so your first lookup does not actually set the "no match found" when it doesn't have a match. Just leave the default behaviour which will leave the output field as a null value. This way you can just have a second lookup table that will fill out the output field if it doesn't already have it. You can set the second lookup to set "no match found" if you want.

To get the lookups to run sequentially in the correct order, make sure the "name" of the automatic lookup definitions is in correct lexicographical (alphabetical) order.

All the best.

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