Optimize Search

[nicxso]

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 ?

[nickhills]

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"

[nicxso]

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

[nickhills]

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.

[nicxso]

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

[nicxso]

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

[nicxso]

yeah join is really not a good option here