compare current hour and previous hour value in search and find difference ?

[pgadhari]

I want to compare current top of an hour value with previous top of an hour value. For e.g. between 9 am to 10 am - get the value from exactly 10 am as curr_value and get the value from exactly 9 am as prev_value, find the difference and show the value. This will be applicable for next hour also :

I am using below query to get the earliest and latest value of the hour, but not sure on whether the events are returning proper :

index=dc sourcetype=total_energy earliest=-1h@h latest=@h | stats latest(value) as curr_value earliest(value) as hour_before by source,snmp_index

Please help ?

[TISKAR]

Hello,

I thinks your request is good, to verify you can run this request:

index=dc sourcetype=total_energy earliest=-1h@h latest=@h  | sort  - _time | table _time value source,snmp_index

And:

index=dc sourcetype=total_energy earliest=-1h@h latest=@h  | sort   _time | table _time value source,snmp_index

[to4kawa]

UPDATE2:

index=dc sourcetype=total_energy earliest=-1h@h latest=@h 
| stats last(_time) as curr_time last(value) as curr_value first(_time) as hour_beforetime first(value) as hour_before by source,snmp_index
| fieldformat curr_time=strftime(curr_time,"%c")
| fieldformat hour_beforetime=strftime(hour_beforetime,"%c")

how about this?

[pgadhari]

actually, the value field is not the timestamp field. It is just, some energy value. I think you are taking it as "epoch" value ? its not that.

What I want to know is - the latest (energy) value and earliest (energy) value, which I am getting in the value field is of the proper time of curr_hour and prev_hour ? How do I verify that ? Hope you got it ?

[to4kawa]

my answer is ammended. I'm misunderstand.

[pgadhari]

[pgadhari]

in the above image, how do I verify whether curr_value is of 1 PM and hour_before is of 2 PM ?

Query I am using is :

index=dc sourcetype=total_energy earliest=-1h@h latest=@h  | stats last(value) as curr_value first(value) as hour_before by source,snmp_index

[to4kawa]

@pgadhari
my answer is updated, please confirm.

[pgadhari]

ok. I will check and revert. Thanks.

[pgadhari]

latest and earliest will also do the same, but is it possible to check whether it is really taking the proper first and last value bu using _time ?