topic Re: How to count the delta of the first and the last event in Splunk Enterprise

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

spisiakmi — Thu, 02 Jul 2020 09:11:55 GMT

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

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

renjith_nair — Thu, 02 Jul 2020 09:58:53 GMT

@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

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

spisiakmi — Thu, 02 Jul 2020 11:17:06 GMT

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

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

gcusello — Thu, 02 Jul 2020 11:23:51 GMT

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

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

spisiakmi — Thu, 02 Jul 2020 11:30:47 GMT

Hi @gcusello

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

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

to4kawa — Thu, 02 Jul 2020 11:34:12 GMT

| 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()

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

spisiakmi — Thu, 02 Jul 2020 11:39:26 GMT

@to4kawa

ou yeah. very very elegant.

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

gcusello — Thu, 02 Jul 2020 11:46:59 GMT

Hi @spisiakmi ,

try:

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

Ciao.

Giuseppe