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

[echojacques]

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

[rmdfrb]

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!

[echojacques]

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.

[MuS · Accepted solution]

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

[MuS]

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"

[echojacques]

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

[MuS]

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

[echojacques]

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

[MuS]

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

[echojacques]

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

[somesoni2]

Also, try moving the iplocation call after the stats.