Search query required to lookup a csv file

[ajayrejin]

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

[DMohn]

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.

[ajayrejin]

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

[DMohn]

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

[renjith_nair]

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