Splunk Search

Search incorporating inputlookup

balcv
Contributor

I have a list of source ip addresses in a csv file loaded into Splunk as a lookup file.  The file has a single field, src_ip, and about 4000 rows of unique ip address.

I want to take the contents of the lookup file and compare each entry to a search of filewall logs and report the number of times each entry in the lookup file is present in the firewall data.

I have this so far but the src_ip listed in the result is not always present in the lookup file.

 

index="firewall" src_ip!="192.168.0.0/16"
| fields src_ip
| append
  [ | inputlookup RYUK.csv
  | fields src_ip]
| stats count by src_ip

 

Any suggestions greatly appreciated.

Thanks
Leigh

Labels (1)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

That query takes all of the src_ip values found in the firewall index and adds to it all of the src_ip values from the RYUK lookup file.  The result is the union, rather than the intersection, of the two sets.

To get the intersection, use this query to tell Splunk to only pull events that contain a listed src_ip value.

 

index="firewall" src_ip!="192.168.0.0/16" [ | inputlookup RYUK.csv
  | return 1000 src_ip]
| stats count by src_ip

 

The subsearch reads the lookup file and reformats it into a query string of the form "(src_ip=1.2.3.4 OR src_ip=1.2.3.5 OR src_ip=1.3.4.5 ...)"

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

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

That query takes all of the src_ip values found in the firewall index and adds to it all of the src_ip values from the RYUK lookup file.  The result is the union, rather than the intersection, of the two sets.

To get the intersection, use this query to tell Splunk to only pull events that contain a listed src_ip value.

 

index="firewall" src_ip!="192.168.0.0/16" [ | inputlookup RYUK.csv
  | return 1000 src_ip]
| stats count by src_ip

 

The subsearch reads the lookup file and reformats it into a query string of the form "(src_ip=1.2.3.4 OR src_ip=1.2.3.5 OR src_ip=1.3.4.5 ...)"

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

balcv
Contributor

Hi @richgalloway .  Having had this running for a couple of days, it would appear that this search only takes the first address in the lookup file.  I tested this by manually adding addresses I knew were valid and each time it only ever returned a count for the first address in the file and a count of that address.

It did not move through the entire lookup file.

Cheers
Leigh

0 Karma

richgalloway
SplunkTrust
SplunkTrust

You're right.  I left out an argument to the return command.  See the revised answer.

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

balcv
Contributor

Thanks @richgalloway  That appears to have done the trick but I'll need to wait until I have data that matches, but I think it should be fine.

Thanks
Leigh

0 Karma

96nick
Communicator

Try this one out:

index=firewall src_ip!="192.168.0.0/16" 
| fields src_ip 
[| inputlookup RYUK.csv | stats values(src_ip) AS search | format ] 
| stats count by src_ip

Similar to the linked reply below. Does that get you what you need? Hope this helped!

Solved: Search for all events for IP address within a CSV ... - Splunk Community

0 Karma

balcv
Contributor

Hi @96nick . Thanks for your repsonse.

This it not giving me exactly what I wanted.  It appears that it lists all the addresses found in the firewall log with a count rather than using the addresses in the lookup file.

I tested this by running the search then manually looking then up in the file lookup file and most did not exist in the file. 

I need to take each address in the lookup file, compare to the firewall data and give a count for the number of matches against that file.

Cheers
Leigh

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