Splunk calculate average of events

[sahil237888]

Hi All,

Can you please help.
I want to create a query whiich could :
Calculate average of current events on server.

[micahkemp]

It sounds like you want something like:

<search that finds the events you want to count>
| timechart span=15min count
| streamstats window=1 current=false last(count) AS last_15_min_count
| eval pct_of_last_15_min_count=(count/last_15_min_count)*100
| search pct_of_last_15_min_count<=60

This will have a result only for 15 minute spans that showed a drop to less than 60% of the previous 15 minute span (a 40% drop).

[sahil237888]

@micahkemp
With the above query(after excuding search part at the last), I am able to find the current count and last 15 minutes event count.
but its values keep changing after every refresh of the page so do average also changes.

Can you please help :
I want to get the alert if current / live traffic is less than 40% of past 15 minutes traffic.
Also if you could assist query that could show the difference ( current and past ) and average dropped.

[micahkemp]

The posted query assumes that you would run this for a 30-minute span, snapping at the half hour. So, 01:00-01:30. Over that window, the search would have a result if the 01:15-01:30 span had 40% fewer events than 01:00-01:15. If you run the search without snapping to a 15-minute end time then, yes, results will change over time.

I'm not entirely sure what you mean by current, but if you mean "right now, not over a 15 minute span" then things get trickier.

The below search assumes that you've searched for any 30-minute span, and creates two buckets of time ("fifteen minutes ago until now", and "everything else"). You could run this search every minute and you would get more new results each time, but they would be the most current results, and based entirely on the last 15 minutes compared to the 15 minutes before that.

<your base search>
| addinfo 
| eval fifteen_mins_ago=relative_time(info_max_time, "-15min") 
| eval _time=if(_time>=fifteen_mins_ago, fifteen_mins_ago, info_min_time) 
| stats count BY _time 
| streamstats window=1 current=false last(count) AS last_15_min_count 
| eval pct_of_last_15_min_count=(count/last_15_min_count)*100

[Richfez]

To clarify, @sahil237888, you would like a sort of "template search" that you could use for a couple of different purposes. The base template would be something akin to this behavior:

1) Count/measure/sum/add/whatever some metric over, say, 1 minute intervals for the past 15 minutes.

Let's pretend we are just doing a simple count of events, so we have let's say 15,16,14,17,15,16,15,16,14,15,16,14,17,5,2

2) Identify when the last minute's count/measurement is under some threshold, let's say it's half or less (50%).

In which case each of the last two minutes would have been flagged. Even though I'm too lazy to calculate the exact average of those numbers, it's probably about 15, but the last two are far below half of 15 so they should be flagged.

If that is an accurate assessment, here's one solution.

index=X sourcetype=Y earliest=-15m latest=@m 
| timechart span=1m count AS Count 
| eventstats avg(Count) as Average, latest(_time) as MostRecent
| eval PercentOfAverage = ( Count / Average ) * 100
| where _time = MostRecent

The base search returns some events. Line two summarizes the last 15 minutes into one minute increments. The third line uses eventstats to find an overall average of those 1 minute summaries. The fourth line uses eval to calculate a percentage, then the last line filters it down to just the most recent event.

Adjust whatever you need in there. You can leave the | where... off the end to see the individual numbers and things (in fact, you can build the search one line at a time - and I recommend doing so! - to a) understand it better and b) make it easier to adjust to your needs.

Once you have it working right and showing you the accurate statistics you need, then you could do all sorts of things with this search, but I think you'll want something like an alert that triggers when PercentOfAverage < 40 or something like that.

[sahil237888]

I have tried the above query, But the issue i am now facing is Data for last 15 minutes doesn't seem to be stable. It keeps changing.
After Some time (say 2hrs) it gets stable.

So if data is not stable for the last 15 minutes , it will be difficult to compare the live events and events happened in past 15 minutes. Hence will not be able to calculate percentage .

One more thing :
If i need to calculate average and counts of live events with past 15 minutes events. How can i do per minute wise.

For instance , If now the time is 2:15 pm , the. i want the query to compare the events happened at 2 pm. Then at 2:16 pm , it should compare with 2:01pm.

The average also crosses sometime above 100, logically it should not increase 100%.

[sahil237888]

@rich7177,
Thanks for your answer.

But let me summarize what i need here, may be then i can get exact query.

I am searching for a keyword in a log file.And calculating the average of the count.
Then that average is to be compared with the average of the last 15 minutes count average ( 15 minutes interval) and then if the average of current count (logs keep refreshing) is less than some percentage(say 40%) from past 15 minutes, then there is drop of hits by 40%

[sahil237888]

@rich7177

can you please suggest also.

[Richfez]

If you need a keyword that would be part of the base search. Just put it in there: index=X sourcetype=Y earliest=-15m latest=@m KEYWORD - and I think micahkemp's answer covers that specifically.

I can't see anything else in your list of requirements that isn't met by what either or or micahkemp had written, so please give them a try!

Happy Splunking,
Rich

[micahkemp]

Can you more specifically define what defines "current events"? Do your events consist of a start and end component, such that lacking an end means the event is still currently happening?

[sahil237888]

and the events is currently happening also.

Please see if this answer your question.

[sahil237888]

Hi,

Current events can be one of the below :
1. number of hits on a server.
2. Count of Traffic coming to server.
3. No. of requests handled by the server.

or

4 Count of Specific keyword found in the logs at a paticular time.