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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...