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?
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
You might need to adjust based on your events
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
You might need to adjust based on your events
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!
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....
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 🙂
Wow, I didn't read your second comment, let me try it on my system, maybe I can find something
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:
cheers, MuS
Actually I was trying things like that! 🙂
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.