topic Re: Search query required to lookup a csv file in Splunk Search

Search query required to lookup a csv file

ajayrejin — Tue, 29 Sep 2020 23:02:43 GMT

Hi,

I need to check if the source address from the firewall logs is in private ip address range. How would i check using inputlookup and join commands.
Below is the query i am using , however no results come up

index=firewall log_subtype=vulnerability severity=informational | join src [ inputlookup PRIVATE_IP.csv ] | stats count by src

Re: Search query required to lookup a csv file

renjith_nair — Thu, 31 Jan 2019 13:40:18 GMT

@ajayrejin,

Try this and lets know if it works.

index=firewall log_subtype=vulnerability severity=informational |eval flag=0
|append [inputlookup PRIVATE_IP.csv |eval flag=1]
|stats count , max(flag) as flag  by src

If the count is > 1 and flag=1 then its a private IP which is part of your lookup file.

Re: Search query required to lookup a csv file

DMohn — Thu, 31 Jan 2019 13:52:02 GMT

Hi,

You don't have to use a join here, which is a very "costly" command. A simple subsearch does the trick as well:

  index=firewall log_subtype=vulnerability severity=informational | search [inputlookup PRIVATE_IP.csv]

Given that the lookup table contains only one field named "src" - otherwise you will have to restrict the return from the subsearch and / or rename the field.

Re: Search query required to lookup a csv file

ajayrejin — Tue, 29 Sep 2020 23:03:14 GMT

Hi,

Thank you for that query.
I am using the below query and results i see is only public IP addresses, however result should show only private IPs. Am i doin something wrong? Please do correct me

index=frewall log_subtype="vulnerability" severity="informational" | join type=left src_ip [ inputlookup PRIVATE_IP.csv ] | stats count by src_ip

Re: Search query required to lookup a csv file

DMohn — Fri, 01 Feb 2019 08:12:37 GMT

You don't have to use the join command. Have you tried using the query I stated above? If you add the count command, you should get exactly the result you want.

 index=firewall log_subtype="vulnerability" severity="informational" | search [ inputlookup PRIVATE_IP.csv ] | stats count by src_ip