topic Re: Optimize Search in Splunk Search

Optimize Search

nicxso — Tue, 29 Sep 2020 23:46:30 GMT

I have to create an alert where as soon as the number of events at time X has changed. There are two following scenarios

That the number of events at time X has increased dramatically (eg compared to time X - doubled by 7 days)
That the number of events at time X has decreased dramatically (eg, compared to time X - halved 7 days)

I need to count the total events of each sourcetype

I have the following search for the first scenario

index=caas_oracle_virtual_directory_* sourcetype="oracle_virtual_directory:" earliest=-7d latest=now
| stats count as event_last_week by index, sourcetype
| join type=left sourcetype [
| search index=caas_oracle_virtual_directory_ sourcetype="oracle_virtual_directory:*" earliest=@d latest=now
| stats count as event_today by index, sourcetype
| fields sourcetype, event_today]
| eval half = (event_last_week / 2)
| table index, sourcetype, event_last_week, event_today, half
| rename index as "Index" sourcetype as "Source Type", event_today as "Event Today", half as "Threshhold"

Here a picture:

Any optimization that i can do to my search, there are over 100 Million events in total. So it takes awhile.
Can i make my search run faster ?

Re: Optimize Search

nickhills — Thu, 21 Mar 2019 10:00:49 GMT

Totally untested, and typed on a phone - could be typos!

index=caas_oracle_virtual_directory_ sourcetype="oracle_virtual_directory:" earliest=-7d latest=now | eventstats count as event_last_week by index, sourcetype |search * earliest=@d latest=now | eventstats count as event_today by index, sourcetype | eval half = (event_last_week / 2) | table index, sourcetype, event_last_week, event_today, half | rename index as "Index" sourcetype as "Source Type", event_today as "Event Today", half as "Threshhold"

Re: Optimize Search

nicxso — Tue, 29 Sep 2020 23:47:36 GMT

@nickhills
This is my new search
index="caas_oracle_virtual_directory_" sourcetype="oracle_virtual_directory:" earliest=-7d latest=now
| stats count as event_last_week by time, index, sourcetype
| join type=left sourcetype
[| search index=caas_oracle_virtual_directory* sourcetype="oracle_virtual_directory:*" earliest=@d latest=now
| stats count as event_today by _time index, sourcetype
| fields sourcetype, event_today]
| eval half = (event_last_week / 2)
| where half > event_today
| table _time, index, sourcetype, event_last_week, event_today, half
| rename index as "Index" sourcetype as "Source Type", event_today as "Event Today", half as "Threshhold"

What i want to add now is the time when the treshold has beenn passed.

Re: Optimize Search

nickhills — Thu, 21 Mar 2019 10:44:13 GMT

You want to try and avoid a 'join' they are horrifically poor performing, which is what I was proposing with 'eventstats' - did you get an opportunity to test my suggestion. I will be back at a PC shortly.

Re: Optimize Search

nicxso — Thu, 21 Mar 2019 10:49:39 GMT

yeah join is really not a good option here

Re: Optimize Search

nicxso — Thu, 21 Mar 2019 10:56:13 GMT

yeah i tried your suggestion, but it's also taking awhile. there are 3 differente index and souretype. what i want to do is count the totel events from today until now and from minus 7 days ago until now. And if the total events from today are more than the treshold, the alert should be triggered. And i also need add the time when it went over the treshhold

Re: Optimize Search

nicxso — Thu, 21 Mar 2019 12:08:45 GMT

@nickhillscpl
Could you tell how I could count all events from today until now and compare that minus 7 days ago ?