topic Re: Match IP address with IP in Lookup table and alert in Splunk Search

Match IP address with IP in Lookup table and alert

neerajs_81 — Wed, 27 Oct 2021 09:43:53 GMT

Hello, We are using ES and we have a lookup file downloaded which has a mix of standalone ip's and CIDRs/Subnets/. The CSV file has 3 columns : Description, ip, time

I want to match dest_ip from my search results to any of those IPs in the lookup table ( Column "ip") and if any matches, the results should be displayed in a table format

I am using this search

Is the above correct? The problem i am facing is even if no IPs match, the search results still shows me the _time column and ip column (as empty) . How to get the search NOT show any results if IPs don't match in the lookup , and secondly can i compare IP with a CIDR in the lookup ?

Re: Match IP address with IP in Lookup table and alert

ITWhisperer — Wed, 27 Oct 2021 09:52:28 GMT

Does this work for the first part

| where isnotnull(ip)

Re: Match IP address with IP in Lookup table and alert

neerajs_81 — Wed, 27 Oct 2021 10:04:48 GMT

If i add

| lookup ip_reputation_list ip as dst_ip OUTPUT ip | where isnotnull(ip) | table _time ip

then search results show "No results found" for both cases - ip's that match the Lookup file as well as for those that do no match in the lookup file. That is weird. Why would it show No results found even for those that match the data in the lookup ? Is it because of a string format issue that isnotnull doesn't recognize ?

Re: Match IP address with IP in Lookup table and alert

ITWhisperer — Wed, 27 Oct 2021 10:14:01 GMT

You could try

| where ip!=""

Alternatively, try removing the OUTPUT ip to see if you get any fields returned by the lookup?

Re: Match IP address with IP in Lookup table and alert

neerajs_81 — Wed, 27 Oct 2021 10:48:50 GMT

Thank you very much. It turns out that both isnotnull() and ip!="" are working, i didn't realize i had mistakenly omitted streamstats just after the |makeresults count=2 command.

| streamstats count

Bit confused as in why is streamstats so important along with makeresults.

Secondly would you know how to match an ip from the search results if its part of CIDR/Subnet in the lookup ? We are on splunk cloud and i don't have access to transform.conf

Re: Match IP address with IP in Lookup table and alert

ITWhisperer — Fri, 29 Oct 2021 11:18:58 GMT

| makeresults count=2

creates a pipeline with two events

| streamstats count

counts the events in the pipeline (stream) storing the current count in the count field

This allows the case statements to distinguish which event is which and assign different values depending on which event it is.