Splunk Search
Highlighted

Optimize Search

Engager

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=caasoraclevirtualdirectory* sourcetype="oraclevirtualdirectory:" earliest=-7d latest=now
| stats count as eventlastweek by index, sourcetype
| join type=left sourcetype [
| search index=caasoraclevirtualdirectory
sourcetype="oraclevirtualdirectory:*" earliest=@d latest=now
| stats count as eventtoday by index, sourcetype
| fields sourcetype, event
today]
| eval half = (eventlastweek / 2)
| table index, sourcetype, eventlastweek, eventtoday, half
| rename index as "Index" sourcetype as "Source Type", event
today as "Event Today", half as "Threshhold"

Here a picture:
alt text

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 ?

0 Karma
Highlighted

Re: Optimize Search

Ultra Champion

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"

0 Karma
Highlighted

Re: Optimize Search

Engager

@nickhillscpl
This is my new search
index="caasoraclevirtualdirectory" sourcetype="oraclevirtualdirectory:" earliest=-7d latest=now
| stats count as eventlastweek by time, index, sourcetype
| join type=left sourcetype
[| search index=caas
oraclevirtualdirectory* sourcetype="oraclevirtualdirectory:*" earliest=@d latest=now
| stats count as event
today by time index, sourcetype
| fields sourcetype, event
today]
| eval half = (eventlastweek / 2)
| where half > eventtoday
| table _time, index, sourcetype, event
lastweek, eventtoday, 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.

0 Karma
Highlighted

Re: Optimize Search

Ultra Champion

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.

0 Karma
Highlighted

Re: Optimize Search

Engager

yeah join is really not a good option here

0 Karma
Highlighted

Re: Optimize Search

Engager

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

0 Karma
Highlighted

Re: Optimize Search

Engager

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

0 Karma