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 ]
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 - If your question has been answered, please "accept" the best or most helpful answer. Doesn't matter which one.
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
this is very interesting. I was not able to get it to work, but I will study it further.
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.
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.
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
Thank you! this approach is much cleaner.
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.