Help please. How can I write this using a lookup for the IPs? thank you

[GIA]

I have tried using search but can't seem to get it right. Any guidance is appreciated

This alert detects any traffic to an IP on the IOC list or from an IP on the   IOC list where the traffic has been specifically allowed.

| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype

from datamodel=Network_Traffic.All_Traffic

where NOT All_Traffic.src_ip IN (10.0.0.0/8, 10.0.0.0/8, 10.0.0.0/8) AND All_Traffic.action=allow*

by _time All_Traffic.src_ip All_Traffic.dest_ip

| `drop_dm_object_name(All_Traffic)`

| lookup ip_iocs  ioc as src_ip OUTPUTNEW last_seen

| append

[| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype

from datamodel=Network_Traffic.All_Traffic

where All_Traffic.src_ip IN (10.0.0.0/8, 10.0.0.0/8) AND NOT All_Traffic.dest_ip IN (10.0.0.0/8, 10.0.0.0/8) AND NOT All_Traffic.protocol=icmp

by _time All_Traffic.src_ip All_Traffic.dest_ip

| `drop_dm_object_name(All_Traffic)`

| lookup ip_iocs ioc as dest_ip OUTPUTNEW last_seen]

| where isnotnull(last_seen)

| head 51

[inventsekar]

Hi @GIA ... Lets troubleshoot bit by bit... 

pls try these.. i feel something wrong with >>> [|inputlookup internal_ranges.csv |] <<<

| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
from datamodel=Network_Traffic.All_Traffic
|search NOT (All_Traffic.src_ip [|inputlookup internal_ranges.csv |])

or 

| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
from datamodel=Network_Traffic.All_Traffic
|search NOT (All_Traffic.src_ip [|inputlookup internal_ranges.csv ])

[GIA]

| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
from datamodel=Network_Traffic.All_Traffic
|search NOT (All_Traffic.src_ip [|inputlookup internal_ranges.csv ])

 Thank you so much!! Your second option yielded the correct results. Do you mind helping me with the other portion please? also, if you know where I can find documentation that can better help me understand lookups for these kinds of searches, that would be appreciated. I am new with Splunk and as much as I like it, I find it challenging at times. 

[inventsekar]

Hi @GIA Please check this search query (basically I have edited the 4 places(removing the "|"))

| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
    from datamodel=Network_Traffic.All_Traffic 
| search NOT (All_Traffic.src_ip 
    [| inputlookup internal_ranges.csv 
     ]) AND (All_Traffic.dest_ip 
    [| inputlookup internal_ranges.csv 
     ]) AND (All_Traffic.action="allow*")
    by _time All_Traffic.src_ip All_Traffic.dest_ip 
| `drop_dm_object_name(All_Traffic)` 
| lookup ip_iocs.csv ioc as src_ip OUTPUTNEW last_seen 
| append 
    [| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
        from datamodel=Network_Traffic.All_Traffic
        where (All_Traffic.src_ip IN 
        [| inputlookup internal_ranges.csv 
         ]) AND NOT (All_Traffic.dest_ip IN 
        [| inputlookup internal_ranges.csv 
         ]) AND NOT (All_Traffic.protocol=icmp)
        by _time All_Traffic.src_ip All_Traffic.dest_ip 
    | `drop_dm_object_name(All_Traffic)` 
    | lookup ip_iocs.csv ioc as dest_ip OUTPUTNEW last_seen] 
| where isnotnull(last_seen) 
| head 51

to learn lookup commands, pls check https://docs.splunk.com/Documentation/Splunk/9.1.2/SearchReference/lookup

the lookup, subsearch, append, tstats and datamodels... bit complex topics and it may take a long time for you to understand. pls dont loose hope. keep on learning, daily, bit by bit. hope you got it, thanks. 

[GIA]

thank you so much for your help and for sharing the resources, I will go through them.

I ran the search  but I am getting the following error message:

"Right hand side of IN must be a collection of literals. '(range = "10.0.0.0/8")' is not a literal
The search job has failed due to an error..."

I got this error before, I assumed that when using lookups the WHERE IN clause needs to be changed for something else maybe? not sure =/  thanks in advanced!

[PickleRick]

If you're using the IN clause you need to provide, as the message says, a list of literals. So you should do

something IN ("val1", "val2", "val3)

You can't give a list of conditions as you apparently tried to do

[GIA]

I don't have a preference on which to use, I just need to be able to use the lookup efficiently for this search. It seems that WHERE and IN are not the correct clauses to use when using lookups? I am not sure

[richgalloway]

Subsearches produces results like (field1=foo OR field2=bar), which not fit the syntax of the IN operator.  However, since IN maps to a sequence of ORs under the coverts, you can use the subsearch without using IN.

| append 
    [| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
        from datamodel=Network_Traffic.All_Traffic
        where (All_Traffic.src_ip 
        [| inputlookup internal_ranges.csv 
         ]) AND NOT (All_Traffic.dest_ip 
        [| inputlookup internal_ranges.csv 
         ]) AND NOT (All_Traffic.protocol=icmp)
        by _time All_Traffic.src_ip All_Traffic.dest_ip 
    | `drop_dm_object_name(All_Traffic)` 
    | lookup ip_iocs.csv ioc as dest_ip OUTPUTNEW last_seen] 

[PickleRick]

Aha. Right. I looked back into @inventsekar 's response and it's a bit wrong. A subsearch returns sets of conditions which obviously cannot be used with the IN clause. They should be used "directly" as conditions (possibly negated with the NOT keyword). Sadly I'm at my phone at the moment so I'm not in a position to write a solution.

[GIA]

What I need help with is how to use lookups tables for the IPs in this search. I have several rules similar to this one but I can't add any IPs inline, I have to use lookups for those. 

Below is how I am writing it but it's obviously wrong. Thanks

| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
from datamodel=Network_Traffic.All_Traffic
|search NOT (All_Traffic.src_ip [|inputlookup internal_ranges.csv |]) AND (All_Traffic.dest_ip [|inputlookup internal_ranges.csv |]) AND (All_Traffic.action="allow*")
by _time All_Traffic.src_ip All_Traffic.dest_ip
| `drop_dm_object_name(All_Traffic)`
| lookup ip_iocs.csv ioc as src_ip OUTPUTNEW last_seen
| append
[| tstats summariesonly=true allow_old_summaries=true values(All_Traffic.dest_port) as dest_port values(All_Traffic.protocol) as protocol values(All_Traffic.action) as action values(sourcetype) as sourcetype
from datamodel=Network_Traffic.All_Traffic
where (All_Traffic.src_ip IN [|inputlookup internal_ranges.csv |]) AND NOT (All_Traffic.dest_ip IN [|inputlookup internal_ranges.csv|]) AND NOT (All_Traffic.protocol=icmp)
by _time All_Traffic.src_ip All_Traffic.dest_ip
| `drop_dm_object_name(All_Traffic)`
| lookup ip_iocs.csv ioc as dest_ip OUTPUTNEW last_seen]
| where isnotnull(last_seen)
| head 51

[richgalloway]

The error message is complaining about the trailing | in the subsearches.

[|inputlookup internal_ranges.csv |]) AND (All_Traffic.dest_ip [|inputlookup internal_ranges.csv |]

[richgalloway]

How does the current search fail to meet expectations?  What are those expectations?

I'm not sure CIDRs are supported in the tstats command.

[PickleRick]

Yes, they are