topic Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold? in Alerting

How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

abajracharya — Thu, 29 Jan 2015 01:03:10 GMT

I want to create a splunk alert in such a way that it compares current data with yesterdays during the same time period. For e.g today at 1:00 PM will compare with yesterday 1:00 PM.
then I want to calculate the change percent of those two results. On basis of what we get want set the alert if change percent exceeds certain threshold (say X).

e.g. todays error count at x hour is 500, yesterday's error count at x hour was 300. change percent = 500-300=200X.01=2%
if threshold is set to 1%, alert is triggered.

Any help would be appreciated.

thanks

Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

lguinn2 — Thu, 29 Jan 2015 02:07:45 GMT

This should do it:

yoursearchhere -earliest=-25h@h -latest=@h
| eval WhichDay = case(_time>=relative_time(now(),"-1h@h"),"Today",
                       _time<=relative_time(now(),"-24h@h"),"Yesterday",
                       1==1,null())
| stats count(eval(WhichDay=="Today")) as Today count(eval(WhichDay=="Yesterday")) as Yesterday 
| eval ChangePercent = (Today - Yesterday) / 100

Here is an example of how the times work:

The search starts at 2:07 - this is the value of now()

@h = 2:00 - the end of today's hour

-1h@h = 1:00 - the beginning of today's hour

-24h@h = yesterday at 2:00 - the end of yesterday's hour

-25h@h = yesterday at 1:00 - the beginning of yesterday's hour

_time = the timestamp of the event

Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

abajracharya — Thu, 29 Jan 2015 23:50:36 GMT

Thank you for the response.

When i ran this query it just showed me data for today but nothing for yesterday.

thank you.

Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

lguinn2 — Fri, 06 Feb 2015 06:44:56 GMT

I just corrected a typo... sorry.

Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

twinspop — Mon, 09 Mar 2015 16:58:33 GMT

Couldn't this be more efficient with appendcols? I have a hard time believing the inefficiency of a sub-search will eclipse the inefficiency of returning 25 hours worth of records and only using 2 of them, especially if we're talking about a dataset with millions of events.

some search earliest=-1h@h latest=@h | 
stats count as Today | 
appendcols [search some search earliest=-25h@h latest=-24h@h | 
stats count as Yesterday ] | 
eval ChangePercent = (Today - Yesterday) / 100

Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

lguinn2 — Fri, 10 Apr 2015 19:02:26 GMT

I agree with that....

Re: How to compare current data with data from 24 hours ago, calculate the percentage change and alert if the change exceeds a threshold?

vaibhavvijay9 — Fri, 22 Feb 2019 11:26:46 GMT

Hi @twinspop , @lguinn2 ,

What I want is : "If the current pendingMsg count is greater than or equal to the last one hour count, then display the queueName with label - 'Queue with no processing since last one hour' "
(OR we can say the outTotalMsgs is same for now and last one hour event)

Example :
My basic search no dedup applied, but currently I have written only one queueName :

 ..... | xmlkv | table _time, qName, pendingMsgCount, inTotalMsgs, outTotalMsgs

Timestamp (last 60 minutes) - (22/02/2019 06:58:00.000 to 22/02/2019 07:58:13.000)

Results : only one queueName (124 events)

first two :

last two :

So, for this queueName, the pendingMsg count OR outMessges Count is same and hence it should be displayed in results for dashboard - 'Queue with no processing since last one hour'.

I am not able to achieve this, please help!

Thanks in advance!