How to create a graph from sum differences?

Elastic Stack Kibana
1

How do I create a graph from calculations?

I have an index of documents containing the following information:

  • id
  • begin_event (keyword)
  • end_event (keyword)
  • duration (float)

And here's an example of some of those docs as comma-delimited lines:
101, CALC_FOO_BEGIN, CALC_FOO_END, 5.3
102, CALC_BAR_BEGIN, CALC_BAR_END, 2.7
103, CALC_FOO_BEGIN, CALC_FOO_END, 4.5
104, CALC_BAR_BEGIN, CALC_BAR_END, 3.2

Now what I'd like to do is create a chart of all the time spent with the following formula:
SUM(CALC_FOO_BEGIN/END event durations) - SUM(CALC_BAR_BEGIN/END event durations)

Although I've been successful creating charts for CALC_FOO_BEGIN/END duration sums and CALC_BAR_BEGIN/END duration sums, I don't know how to calculate the differences as shown above.

Any tips. Still a newbie but trying hard!

Thanks,
John.

1 Like
2

Hi John,

Can you share some screenshots of how you've created the duration sums as you have them currently? The more detail, the better, especially of the charts you've created and the settings for those charts.

What kind of chart are you looking to create for the difference-of-sums chart?

3

I haven't created a pie chart yet, but that's the intention. In general, I have lots of docs that provide the duration (time) between 2 events on a specific resource id. I want to create a pie chart showing how the total time is split among the various events. Getting the pie chart slice for a specific pair of events marked in a single document seems simple enough. However, one of the slices will be a time difference between two pairs of events.

Here are the ordering of original events in index events:

<timestamp>, id, PRE_CALC_BEGIN, ...
<timestamp>, id,PRE_CALC_END
<timestamp>, id, CALC_FOO_BEGIN
... Other events
<timestamp>, id ,CALC_BAR_BEGIN
<timestamp>, id, CALC_BAR_END
... other events
<timestamp>, id, CALC_FOO_END

And I have another index durations with those event pairs of interest and a duration between events:

id, start_event, end_event, duration

The TOTAL time spent on the activity is from PRE_CALC_BEGIN --> CALC_FOO_END

I want a pie chart with 3 slices totaling to 100%:

  1. Percent of time spent between PRE_CALC_BEGIN --> PRE_CALC_END
  2. Percent of time spent between CALC_BAR_BEGIN --> CALC_BAR_END
  3. Percent of time spent in all other activities calculated as the time (CALC_FOO_BEGIN/END time - CALC_BAR_BEGIN/END time

Does that make more sense @a5a?

4

You'd be best off doing this prior to indexing, but you have a look at something like How to calculate delta value

1 Like
5

I think you're suggesting that most calculation across fields or across documents is best done outside of Kibana. Is that a reasonable interpretation?

That's the purpose of the second index durations that contains the time deltas needed. And that would have been perfect except... now I realize that I need yet another field to calculate the difference between two event pairs for #3 above. In this case, I wish I understood aggregations and parent/child/sibling pipelines better. Those maybe could help?

6

It's expensive to do this particular calculation in Kibana (which is really happening in Elasticsearch), so if you can do it before sending it to Elasticsearch - that is, create a new field that has that value you can then graph - it's a lot more efficient.

Elasticsearch aggregations don't calculate things between documents.

2 Likes
7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.