Getting Data In

How to exclude internal ips from a lookup file

webnair
Explorer

|inputlookup internal_ip.csv gives me list of all internal IP's. I need to exclude these IP's in my below search query:

index=test_web | rex field=targetfile "(/[^/]+){1}/(?P.*)" | where (sourcetype="access_combined" AND like(filename,"%tar") AND (method="GET" OR method="HEAD")) OR (sourcetype="owncloud") | eval username=ifnull(username,user,username) | rename filename as "TAR Name" | lookup owncloudpackages.csv "TAR Name" output "Product Name" | rename "Product Name" as productname | transaction clientip username maxevents=-1 maxpause=3600 | eval productnames=replace(productname,"SWname 7", "SW 7") | stats count(duration) as Downloads by productnames | where Downloads > 1 | sort -Downloads | rename Downloads as "Product Downloads" | rename productnames as "Product Names"

Tags (1)
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

You could do this:

index=test_web NOT [inputlookup internal_ip.csv | rename ip_field_from_lookup as clientip | fields clientip | dedup clientip] | ...

Different topic, the search seems convoluted in places...
Does that index only contain those two sourcetypes? If not, you should add sourcetype=access_combined OR sourcetype=owncloud to the base search to boost performance.
You can incorporate both rename calls into the lookup by using the as keyword.
You can also replace if(isnull(A),B,A) by coalesce(A,B) - this takes more than two fields as well, in case you have huge if(isnull(),,)-trees.

View solution in original post

martin_mueller
SplunkTrust
SplunkTrust

You could do this:

index=test_web NOT [inputlookup internal_ip.csv | rename ip_field_from_lookup as clientip | fields clientip | dedup clientip] | ...

Different topic, the search seems convoluted in places...
Does that index only contain those two sourcetypes? If not, you should add sourcetype=access_combined OR sourcetype=owncloud to the base search to boost performance.
You can incorporate both rename calls into the lookup by using the as keyword.
You can also replace if(isnull(A),B,A) by coalesce(A,B) - this takes more than two fields as well, in case you have huge if(isnull(),,)-trees.

martin_mueller
SplunkTrust
SplunkTrust

Great, don't forget to mark this as solved.

0 Karma

webnair
Explorer

Thanks a bunch.

0 Karma
Get Updates on the Splunk Community!

Exciting News: The AppDynamics Community Joins Splunk!

Hello Splunkers,   I’d like to introduce myself—I’m Ryan, the former AppDynamics Community Manager, and I’m ...

The All New Performance Insights for Splunk

Splunk gives you amazing tools to analyze system data and make business-critical decisions, react to issues, ...

Good Sourcetype Naming

When it comes to getting data in, one of the earliest decisions made is what to use as a sourcetype. Often, ...