How to count the delta of the first and the last event

[spisiakmi]

Hi, can you help me to solve this problem, please?

I have index=index1

In a specified time range, e.g. 3 hours, I have these events. Time is a regular time point, where the electric power has been measured. ID is the name of the electrical counter, which counts the electrical measurements. Value is the measured electrical power [kW]. 

Time	ID	Value
02.07.2020 06:00:00	counter1	1000
02.07.2020 06:00:00	counter2	2000
02.07.2020 06:00:00	counter3	3000
02.07.2020 07:00:00	counter1	2000
02.07.2020 07:00:00	counter2	3000
02.07.2020 07:00:00	counter3	4000
02.07.2020 08:00:00	counter1	3000
02.07.2020 08:00:00	counter2	4000
02.07.2020 08:00:00	counter3	5000

How can I count the consumption of each counter in this time range?

I need this output

ID	consumption
counter1	2000
counter2	2000
counter3	2000

Thank you

[gcusello]

Hi @spisiakmi ,

can we say the the max value for each ID is the result you want?

if yes, try something like this:

index=index1
| stats max(value) AS value BY ID

Ciao.

Giuseppe

[spisiakmi]

Hi @gcusello

no, you need to do max-min. The counter always increase.

[gcusello]

Hi @spisiakmi ,

try:

index=index1
| stats max(Value) as max min(Value) as min by ID
| eval delta=max-min

Ciao.

Giuseppe

[to4kawa]

| makeresults 
| eval _raw="Time	ID	Value
02.07.2020 06:00:00	counter1	1000
02.07.2020 06:00:00	counter2	2000
02.07.2020 06:00:00	counter3	3000
02.07.2020 07:00:00	counter1	2000
02.07.2020 07:00:00	counter2	3000
02.07.2020 07:00:00	counter3	4000
02.07.2020 08:00:00	counter1	3000
02.07.2020 08:00:00	counter2	4000
02.07.2020 08:00:00	counter3	5000"
| multikv forceheader=1
| stats range(Value) by ID

try | stats range()

[spisiakmi]

@to4kawa

ou yeah. very very elegant. 

[renjith_nair · Accepted solution]

@spisiakmi,

Assuming that the counter always increases and does not reset , try

"your search"|stats max(Value) as high,min(Value) as low by ID
|eval consumption=high-low

[spisiakmi]

@enjith_nair you have absolutely right. It was so easy and I made it already so many times. Thank you very much.