How do I find the delta with the previous count value for each host?

[praspai]

Hi,

I have data which always gives me a cumulative count for each server with time as:

<search>| timechart span=4m values(value) as TotalCount by Host

Creates results as below

_time                 Host1        Host2
2015-08-13 09:04:00 3448034.0   3310489.0
2015-08-13 09:08:00 3448073.0   3310525.0
2015-08-13 09:12:00 3448106.0   3310561.0
2015-08-13 09:16:00 3448139.0   3310594.0

I want to find delta with previous value for each host and want in similar table format.

[FritzWittwer_ol]

A solution could be a scheduled search every 5 minutes

.... earliest=-5m@m latest=@m | max(value) as value | convert timeformat="%Y-%m-%d %H:%M" ctime(_time) AS this_time  | inputlookup history value this_time OUTPUNEW value as old_value, this_time | eval delta=value-old_value | outputlookup append=true history

would give you a lookup table with the deltas, but I guess there is room for improvement in this solution 😉

[tom_frotscher]

Hi,

you can use streamstats to solve this. Try to use something like this after the search that leads to the results you showed in your post:

| streamstats current=f last(Host1) as Host1_old last(Host2) as Host2_old | eval delta1=Host1 - Host1_old | eval delta2=Host2 - Host2_old

What happens here is:
1. The part | streamstats current=f last(Host1) as Host1_old gives you the previos event of the Host value
2. The part | eval delta1=Host1 - Host1_old calculates the delta from the current Host value and the previous Host value

Greetings

Tom

[praspai]

I can have any number of host in output so while executing query it should be generate difference between previous reading automatically

[tom_frotscher]

If all your hosts have a common prefix you can use something like | stats last(Host*) as Host*. Then you can use a foreach to calculate the delta of all Host fields.