What alternatives can I use for my search instead of a subsearch to avoid performance issues?

[kseidenschnur_s]

Hi,

I am facing a subsearch performance problem. My goal is to have Bluecoat events filtered only to specific IP's coming from my firewall and having as a result the URL accessed by each IP.

My search looks like this:

index = bluecoat  [search index=checkpoint rule=rule1 OR rule=rule2 AND rule_uid=id1 OR rule_uid=id2 OR rule_uid=id3 OR rule_uid=id4 |fields src_ip | lookup dnslookup clienthost as src_ip output clientip as src_ip | dedup src_ip | table src_ip] | stats values(URL) by src_ip

Now I found a couple of alternative suggestions to use eventstats or similar to prevent the subsearch, but wasn't able to create it by myself. Can anyone help to point me to the right direction?

[renjith_nair · Accepted solution]

Try something like,

index = bluecoat  OR (index=checkpoint rule=rule1 OR rule=rule2 AND rule_uid=id1 OR rule_uid=id2 OR rule_uid=id3 OR rule_uid=id4)
|lookup dnslookup clienthost as src_ip output clientip as src_ip
|eval flag=if(index=="checkpoint ",1,0)|dedup index,src_ip,flag|eventstats count by src_ip|where count >1|stats values(URL) by src_ip

• Search both indexes Lookup clienthost (assuming clienthost is only on checkpoint)
• Set a flag to distinguish between two indexes dedup based on index ip and flag(this will make sure that it deletes only duplicate ips from respective indexes only)
• eventstats will find duplicates ie ; common ips from both indexes
• Finally aggregate those values

You might need to adjust based on your events

[marina_rovira]

Hi! I was trying to reproduce your search in my Splunk system, but I don't understand what you want to do here:

| lookup dnslookup clienthost as src_ip output clientip as src_ip | dedup src_ip | table src_ip

Could you epxlain it please?
Thanks!

[kseidenschnur_s]

The checkpoint is only logging DNS names, therefore I am changing the DNS names into IP addresses and writing them into the same field. Since I can have multiple IP addresses I am doing a dedup and the use the result as the filter in the outer search.

To get this clear, the search is running fine! But since the result of the inner search has about 900 IP adresses the whole search takes ages. I am looking for a way to get this running more efficient....

[marina_rovira]

Maybe you can try to not doing a subsearch and just put thw two index with an OR condition

index = bluecoat OR index=checkpoint rule=rule1 OR rule=rule2 AND rule_uid=id1 OR rule_uid=id2 OR rule_uid=id3 OR rule_uid=id4 | lookup dnslookup clienthost as src_ip output clientip as src_ip | dedup src_ip | stats values(URL) by src_ip

I'm not sure about it, but just trying to give some useful ideas, maybe the inspiration will appear 🙂

[marina_rovira]

Wow, I didn't read your second comment, let me try it on my system, maybe I can find something

[MuS]

Sub searches are really expensive searches and they have limits; You can most likely replace it with a stats, streamstats or eventstats search. Read more here:

https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

cheers, MuS

[marina_rovira]

Actually I was trying things like that! 🙂

[kseidenschnur_s]

I also discussed the possibility to have the inner search written into a lookup table on a regular base and then using the lookup table for the outer search. Should be much faster... But I was wondering if there is a better solution.