Splunk Search

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

Splunk Employee
Splunk Employee

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?

1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

Contributor

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!

0 Karma

Splunk Employee
Splunk Employee

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....

0 Karma

Contributor

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 🙂

0 Karma

Contributor

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

0 Karma

SplunkTrust
SplunkTrust

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

Contributor

Actually I was trying things like that! 🙂

0 Karma

Splunk Employee
Splunk Employee

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.

0 Karma