Splunk Search

How do I compare my lookup table to multiple fields?

MonkeyK
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 ]
1 Solution

DalJeanis
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

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

woodcock
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

MonkeyK
Builder

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

0 Karma

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

MonkeyK
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

DalJeanis
SplunkTrust
SplunkTrust

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

MonkeyK
Builder

Thank you! this approach is much cleaner.

0 Karma

jkat54
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...