Calculate delta for corresponding fields base on time grouped events

[noveix]

Need ideas on how to do field calculations based on 2 sets of transactions. Data file is as follows :

Timestamp_1 field_1 field_2 field_3 field_4 id_1

Timestamp_1 field_1 field_2 field_3 field_4 id_2

Timestamp_1 field_1 field_2 field_3 field_4 id_3

Timestamp_1 field_1 field_2 field_3 field_4 id_4

Timestamp_2 field_1 field_2 field_3 field_4 id_1

Timestamp_2 field_1 field_2 field_3 field_4 id_2

Timestamp_2 field_1 field_2 field_3 field_4 id_3

Timestamp_2 field_1 field_2 field_3 field_4 id_4

Timestamp_3 field_1 field_2 field_3 field_4 id_1

Timestamp_3 field_1 field_2 field_3 field_4 id_2

Timestamp_3 field_1 field_2 field_3 field_4 id_3

Timestamp_3 field_1 field_2 field_3 field_4 id_4

Multiple events have same timestamp (Timestamp_1, Timestamp_2...) at regular intervals. Field_1 .. to Field_4 are cumulative from previous identical timestamped fields.

First part:

Need to calculate DELTA by comparing previous event for the same id_1, id_2 ..etc
Eg. need to calculate Delta as follows :

Timestamp2 Field_1 - Timestamp_1 field_1

Timestamp3 Field_1 - Timestamp_2 field_1

Second part:

Add the DELTAs for field_1, field_2... per time interval, so

Timestamp_2 field-delta_1 field-delta_2 field-delta_3 field_4 id_1

Timestamp_2 field-delta_1 field-delta_2 field-delta_3 field_4 id_2

Timestamp_2 field-delta_1 field-delta_2 field-delta_3 field_4 id_3

Timestamp_2 field-delta_1 field-delta_2 field-delta_3 field_4 id_4

Sum of field-delta_1 for all id_?s

Appreciate any help/pointers with this !!

[martin_mueller · Accepted solution]

For calculating deltas split by a field you can use streamstats:

... | streamstats current=f window=1 last(field) as last_field by split_field | eval delta_field = field - last_field

Not sure what you mean by the second part, could you elaborate?

[noveix]

great work ... works nicely !!

[martin_mueller]

Ah. Append this:

... | eventstats sum(delta_field_1) by _time

[noveix]

I want to sum up delta of field_1 for all events that have the same timestamp regardless of the id_#

[martin_mueller]

Do you want to sum up several fields in one event or sum up one field over several events - or both?

[noveix]

Thanks Martin, that works, for the second part .. I need to sum the deltas for all of the id_# for the same timestamp .. using my example data above, deltas for the 4 events which has identical timestamp.