Splunk Search

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

echojacques
Builder

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
Tags (1)
1 Solution

MuS
SplunkTrust
SplunkTrust

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

View solution in original post

rmdfrb
Explorer

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
Builder

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.

0 Karma

MuS
SplunkTrust
SplunkTrust

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

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"

0 Karma

echojacques
Builder

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

echojacques
Builder

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

echojacques
Builder

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
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Also, try moving the iplocation call after the stats.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...