Splunk Search

Optimize Search

nicxso
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=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:
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

nickhills
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"

If my comment helps, please give it a thumbs up!
0 Karma

nicxso
Engager

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

0 Karma

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

If my comment helps, please give it a thumbs up!
0 Karma

nicxso
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

nicxso
Engager

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

0 Karma

nicxso
Engager

yeah join is really not a good option here

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