How to get Delta for specified time range

[xvxt006]

Hi,

we are monitoring some of the counts and we would like to get the delta from last hour to this hour.This job run every 10 mins. Say the count is 10 in the previous hour and if the count is 15 in the current hour, i would like to see the result as 5 (as a single number).

So i tried the below. But i am missing something and i am not getting the right result. I looked at the delta function but that gives just the delta between current event and last event.

Can some one help?

... earliest=-1h@h latest=now | stats last(Order) as Count | eval Delta=Count-Count1 | table Delta | append [search sourcetype="xxxxxx" earliest=-2h@h latest=-1h@h | stats last(Order) as Count1]

[lguinn2]

Try this

yoursearchhere earliest=-1h@h latest=now 
| bucket _time span=1h
| stats last(Order) as Count by _time
| eval Count = if(_time = relative_time(now(),"-1h@h"),-Count,Count)
| addcoltotals Count

If you want only one result, do this

yoursearchhere earliest=-1h@h latest=now 
| bucket _time span=1h
| stats last(Order) as Count by _time
| eval Count = if(_time = relative_time(now(),"-1h@h"),-Count,Count)
| stats sum(Count) as OverallCount

HTH

[lguinn2]

Yes, see updated answer!

[xvxt006]

Actually both the solutions gave me answer.But i could select only one as acceptable answer. I am looking for a single number. Is it possible to get a single number?

[xvxt006]

Hi, Thank you i got the Count as 0 but i am seeing count and - count also. Can i just final results? i want to use gauge chart. So i just need one value.

[anssntaco · Accepted solution]

For starters, you're using Count1 before it's been defined (which happens later in the search). If anything, you'd want to put that eval after the append. But, that subsearch might not even be needed. Might want to try something like this:

... earliest=-2h@h | eval hour=strftime(_time,"%H") | stats last(Order) as Count by hour | delta Count as Delta

[xvxt006]

Thank you so much for the detailed explanation. I am planning to use this delta in a guage chart. So can i just get the final delta value?

[anssntaco]

If you were to extend your search to more than the last 2 hours, you'd have as many rows as there were hours. And for each hour, the value of Delta would be the difference between Count for that hour and that from the previous hour.

[anssntaco]

it's not so much converting time as it is creating a new field called hour. The reason for doing that is that, using the stats function, you can then calculate last(Order), which is then saved as Count, for each of the previous 2 hours. The delta function then computes the difference between Count for a given hour to that from the previous hour, which it then saves as Delta. You then end up with a results table that has 3 columns: hour, Count, Delta. Since you're only looking over the last 2 hours, the table would have 2 rows.

[xvxt006]

Hi, Thank you for your answer. I am wondering why do we even need to convert time to %H? if i have earliest and latest in hour format (snap)? just to be clear, if the current hour is 9 PM, then i would like to get the count from 7 to 8 PM and get the count for 8 pm to 9 pm and get the delta. Does your answer provide that?