Match IP address with IP in Lookup table and alert

[neerajs_81]

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 

| makeresults count=2
| streamstats count
| eval src_ip = case(count=1,"1.2.3.4", count=2,"2.3.4.5")
| eval dst_ip = case(count=1 OR count=2, "1.234.65.61")
| lookup ip_reputation_list ip as dst_ip OUTPUT ip
| table _time ip

   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 ?

[ITWhisperer]

Does this work for the first part

| where isnotnull(ip)

[neerajs_81]

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 ?

[ITWhisperer · Accepted solution]

You could try 

| where ip!=""

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

[neerajs_81]

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

[ITWhisperer]

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