topic Re: Is there a way to "speed up" this search? in Splunk Search

Is there a way to "speed up" this search?

echojacques — Fri, 16 May 2014 15:47:53 GMT

Outside of creating an accelerated search or upgrading hardware, is there a way to speed up the search below? This search takes approximately an hour to run on my system and I'm wondering if it's because my search logic is inefficient and if my search syntax/logic can be improved somehow? The search detects denied/blocked outbound remote connections (FTP, SSH, RDP). Thanks!

(sourcetype="firewall" OR sourcetype="ips") (dest_port="21" OR dest_port="22" OR dest_port="3389") (action="blocked" OR action="denied") (dest_ip!="10.0.0.0/8" AND dest_ip!="172.16.0.0/12" AND dest_ip!="192.168.0.0/16") | iplocation dest_ip | stats dc(_raw) as Count by src_ip dest_ip Country Region City dest_port protocol sourcetype signature | sort -Count

Re: Is there a way to "speed up" this search?

MuS — Fri, 16 May 2014 17:48:53 GMT

Hi echojacques,

Maybe it helps if you use an index in your search, else the search will use all your defaults indexes.
Also, try to aviod != because this is not the same like NOT.

With != it is implied that the field exists, but does not have the specified value. If the field is not found at all in the event, the search will not match.
NOT field= will check if the field has the specified value and if it doesn't, it will match.

Hope this helps ....

cheers, MuS

Re: Is there a way to "speed up" this search?

somesoni2 — Fri, 16 May 2014 18:09:27 GMT

Also, try moving the iplocation call after the stats.

Re: Is there a way to "speed up" this search?

echojacques — Fri, 16 May 2014 18:50:22 GMT

Thanks, do you mean like this:

(sourcetype="firewall" OR sourcetype="ips") (dest_port="21" OR dest_port="22" OR dest_port="3389") (action="blocked" OR action="denied") NOT  (dest_ip="10.0.0.0/8" OR dest_ip="172.16.0.0/12" OR dest_ip="192.168.0.0/16") | iplocation dest_ip | stats dc(_raw) as Count by src_ip dest_ip Country Region City dest_port protocol sourcetype signature | sort -Count

Re: Is there a way to "speed up" this search?

MuS — Fri, 16 May 2014 18:57:23 GMT

Yes and add index=foo OR index=boo if possible

Re: Is there a way to "speed up" this search?

echojacques — Fri, 16 May 2014 19:13:10 GMT

Ok, did that and for whatever reason, I don't get any results when using NOT. When using != then I get some valid results...

Re: Is there a way to "speed up" this search?

MuS — Fri, 16 May 2014 19:21:47 GMT

just saw that you used in the first posted search (dest_ip!="10.0.0.0/8" AND dest_ip!="172.16.0.0/12" AND dest_ip!="192.168.0.0/16") and now it is (dest_ip="10.0.0.0/8" OR dest_ip="172.16.0.0/12" OR dest_ip="192.168.0.0/16")

The first search used AND, but now you use OR ...

Re: Is there a way to "speed up" this search?

rmdfrb — Mon, 28 Sep 2020 16:38:43 GMT

I just did something very similar to this for our firewall logs (I am doing almost the exact same thing as you here), I was able to speed up a search run time for a 30 day search from many many hours to a few seconds using an accelerated data model.

Create a new data model named "firewall_events", and Add Object -> Root Event named "firewall_events" with constraints (possibly also include indexes as MuS suggests):

(sourcetype="firewall" OR sourcetype="ips") (dest_port="21" OR dest_port="22" OR dest_port="3389") (action="blocked" OR action="denied") (dest_ip!="10.0.0.0/8" AND dest_ip!="172.16.0.0/12" AND dest_ip!="192.168.0.0/16")

Next, add the fields you want with "Add Attribute > Auto-extracted", and pick out the fields you need. Turn on acceleration over the time interval you want to search (30 days for me).

Last, search thusly:

| pivot firewall_events firewall_events count(firewall_events) AS "Count" SPLITROW src_ip AS "src_ip" SPLITROW dst_ip AS "dest_ip" SPLITROW sourcetype AS "sourcetype" SORT 0 src_ip | iplocation dest_ip | sort - Count

I haven't quite gotten the hang of the pivot command syntax yet, so I did that part in the pivot editor and then clicked "Open in Search" to finish the rest of the query.

You may have to wait a few hours for the acceleration to build before you see the full speedup.

Good luck!

Re: Is there a way to "speed up" this search?

echojacques — Fri, 16 May 2014 19:32:27 GMT

Correct, I tried OR since I got incorrect results with AND. AND produced lots of results with the dest_ip ranges that I was trying to exclude...

Re: Is there a way to "speed up" this search?

echojacques — Fri, 16 May 2014 19:34:57 GMT

rmdfrb,
Thanks, I will try to use an accelerated search using your example. I have tried to configure these before, but never got them to work, but I will try again since this looks like the best way to do this.

Re: Is there a way to "speed up" this search?

MuS — Mon, 28 Sep 2020 16:38:49 GMT

okay, back to field one...what happens if you search for :

(sourcetype="firewall" OR sourcetype="ips") (dest_port="21" OR dest_port="22" OR dest_port="3389") (action="blocked" OR action="denied") NOT dest_ip="10.0.0.0/8"