topic Sum specific field value across all events in Dashboards & Visualizations

Sum specific field value across all events

vivekkumarkk — Mon, 15 Feb 2021 07:01:35 GMT

Hello,

I have a data stream getting populated every 5 minutes as below. There are 100s of features in the data.

Feature: Rectagle
Side: 4
User: A
Used:1
Time: 1/25/2021 5:00:00
Block:1

Feature: Rectagle
Side: 4
User: A
Used:1
Time: 1/25/2021 5:00:00
Block:2

Feature: square
Side: 4
User: B
Used:1
Time: 1/25/2021 5:05:00
Block:1

Feature: Square
Side: 4
User: B
Used:1
Time: 1/25/2021 5:05:00
Block:2

I need to sum the side for each side field along with the used column

Something as below.

Feature: Rectangle

Side: 8

used:2

Time: 1/25/2021 5:00:00

The problem I have is, I could not sum the side either it comes as total across all events (it can be 4 * number of times it access across the period I selected) or 4. I am searching for a period of last 24 hours.

I tried with this, but not giving right value.

| foreach feature*
[ eval subtotal = subtotal + 'side']
| stats max(subtotal) as TOTAL by _time

Re: Sum specific field value across all events

richgalloway — Mon, 15 Feb 2021 15:05:58 GMT

Have you tried this?

... | stats sum(Side) as Side, sum(Used) as Used, first(Time) as Time by Feature

Re: Sum specific field value across all events

vivekkumarkk — Mon, 15 Feb 2021 16:05:06 GMT

My problem is, there are 2 block fields. The sum of Side should be only from 2 blocks. With the above query, its sums up all the side field.

The query must select field side for rectangle from each block for any user within the selected time and sum only the sum of sides (here it is 8 ) always.

It should not add another 4 if user B uses from Block 1 only.

Thats why I tried with foreach.

Re: Sum specific field value across all events

richgalloway — Mon, 15 Feb 2021 16:33:43 GMT

If I understand correctly, you want different sums when the user changes. That can be done with this modification.

... | stats sum(Side) as Side, first(Time) as Time by Feature, User