Splunk Search
Highlighted

How do I compare my lookup table to multiple fields?

Builder

I have a lookup table with IP address indicators that I would like to be alerted on whether the IP address is the source or destination. Is there a way to compare my indicators against both source and destination IP addresses so that a match on either one counts?

If I had a single indicator, the search would look like |tstats count FROM datamodel=Network_Traffic.All_Traffic where

All_Traffic.src_ip=8.8.8.8 or All_Traffic.dest_ip=8.8.8.8

When I have a lookup, I know how to search vs just source or destination,

tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
GROUPBY All_Traffic.src_ip | search [inputlookup ipLookups.csv | fields +
ipAddress| rename ipAddress as All_Traffic.src_ip] |table All_Traffic.src_ip count

But is there a way for my ipAddress values to be compared against both source and destination so that a match on either one counts? As I think about it, I suppose that I could append an entire search but it feels like something that should be accomplishable in one pass

append version, showing an overly complex search for something simple:

tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
GROUPBY All_Traffic.src_ip | search [inputlookup ipLookups.csv 
| fields + ipAddress| rename ipAddress as All_Traffic.src_ip] 
| rename All_Traffic.src_ip as ip | table type ip count
|append [ tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
GROUPBY All_Traffic.dest_ip | search [inputlookup ipLookups.csv 
| fields + ipAddress| rename ipAddress as All_Traffic.dest_ip] 
| rename All_Traffic.dest_ip as ip | table type ip count ]
Highlighted

Re: How do I compare my lookup table to multiple fields?

SplunkTrust
SplunkTrust

Have you seen the lookup command? You can use it multiple times...

 |lookup ipLookups.csv ipAddress AS All_Traffic.src_ip OUTPUT otherFieldInLookup
 |lookup ipLookups.csv ipAddress AS All_Traffic.dst_ip OUTPUT otherFieldInLookup

http://docs.splunk.com/Documentation/Splunk/6.5.1/SearchReference/Lookup

otherFieldInLookup in my example can be any number of other space separated field names from the lookup.

0 Karma
Highlighted

Re: How do I compare my lookup table to multiple fields?

SplunkTrust
SplunkTrust

I don't have your data to test against, but something like this should work.

 tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic
 GROUPBY All_Traffic.src_ip All_Traffic.dest_ip 
  | lookup iplookups.csv All_Traffic.src_ip as ipAddress OutputNew ipAddress as FoundSrc
  | lookup iplookups.csv All_Traffic.dest_ip as ipAddress OutputNew ipAddress as FoundDest
  | eval DropThis = IF(coalesce(FoundSrc,FoundDest)."" = "", 1,0)
  | search DropThis=0

It's my guess that you'll want to see which IP tripped the flag, and whether it was a source or destination. That query should give you that data in a brief format.

View solution in original post

Highlighted

Re: How do I compare my lookup table to multiple fields?

Builder

thank you for explaining this approach. I compared this to the append approach and was surprised to find
Append: runtime=38s, size=0.17MB
two lookups: runtime=2m, size=31.48MB

I find the two lookup approach more readable, but I guess that I have to stick to the append approach for resource utilization.

0 Karma
Highlighted

Re: How do I compare my lookup table to multiple fields?

SplunkTrust
SplunkTrust

Ah. Your search times actually make perfect sense. If you are only getting the summary values, and only using the AllTraffic.srcip OR the AllTraffic.destip, then each pass at the indexes doesn't have to do any real work. On the other hand, grouping by BOTH those fields requires a crawl and some summarizing.

Try this and see if it cuts your time any.

 tstats summariesonly=t count 
 FROM datamodel=Network_Traffic.All_Traffic
 GROUPBY All_Traffic.src_ip 
| rename All_Traffic.src_ip as ip 
| eval direction="source"
| fields type direction ip count
| append 
    [ tstats summariesonly=t count 
     FROM datamodel=Network_Traffic.All_Traffic
     GROUPBY All_Traffic.dest_ip 
    | rename All_Traffic.dest_ip as ip 
    | eval direction="dest"
    | fields type direction ip count
    ]
| search 
    [inputlookup ipLookups.csv 
    | fields + ipAddress
    | rename ipAddress as ip
    ] 
| table type direction ip count
Highlighted

Re: How do I compare my lookup table to multiple fields?

Builder

Thank you! this approach is much cleaner.

0 Karma
Highlighted

Re: How do I compare my lookup table to multiple fields?

Esteemed Legend

Many people do not know that with the format command, you have complete control over how a subsearch builds a search. Try this:

| tstats summariesonly=t count FROM datamodel=Network_Traffic.All_Traffic GROUPBY All_Traffic.src_ip
| search [inputlookup ipLookups.csv | fields +  ipAddress| rename ipAddress as All_Traffic.src_ip
          | eval All_Traffic.dst_ip=All_Traffic.src_ip| format "(" "" "OR" "" "OR" ")"]
| table All_Traffic.src_ip count
Highlighted

Re: How do I compare my lookup table to multiple fields?

Builder

this is very interesting. I was not able to get it to work, but I will study it further.

0 Karma
Highlighted

Re: How do I compare my lookup table to multiple fields?

SplunkTrust
SplunkTrust

MonkeyK - If your question has been answered, please "accept" the best or most helpful answer. Doesn't matter which one.

0 Karma