topic Re: How do I compare my lookup table to multiple fields? in Splunk Search

How do I compare my lookup table to multiple fields?

MonkeyK — Mon, 23 Jan 2017 23:35:41 GMT

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 ]

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

jkat54 — Tue, 24 Jan 2017 00:02:43 GMT

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.

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

DalJeanis — Tue, 24 Jan 2017 00:13:36 GMT

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.

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

woodcock — Tue, 24 Jan 2017 05:59:02 GMT

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

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

DalJeanis — Wed, 25 Jan 2017 23:57:45 GMT

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

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

MonkeyK — Thu, 26 Jan 2017 00:00:11 GMT

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.

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

MonkeyK — Thu, 26 Jan 2017 00:01:53 GMT

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

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

DalJeanis — Tue, 29 Sep 2020 12:33:19 GMT

Ah. Your search times actually make perfect sense. If you are only getting the summary values, and only using the All_Traffic.src_ip OR the All_Traffic.dest_ip, 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

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

MonkeyK — Thu, 02 Feb 2017 23:41:09 GMT

Thank you! this approach is much cleaner.