Multiline graph over time

[joconner (John O'Conner)]

I have the following pertinent document properties:

...
    "properties": {
        "timestamp": { "type": "date"},
        "event": { "type": "text"}, // various *_BEGIN/END events
        "resource_id": {"type": "text"},
        "elapsed_time": {"type": "float"}
    }
...

I'm able to generate a pie chart of average durations spent between multiple begin/end event pairs for a specific resource_id. Looks great and is averaged over whatever timespan I want to view. It shows me basically how much time (on average) is spent on various calculations for a particular resource_id

However, I'd now like to create a multi-line graph of these various duration categories over time. I want to see a single line for each category (Garnish, Overpayment, etc) aggregated daily. I can't quite figure out how to do this, but I think I need a Line graph with 14 different lines (I'll most likely reduce this to 3-5 key measures to be easier on the eyes), and I think I may need sibling or parent pipelines... but not sure.

Could you provide any tips for how I might create that line graph? What types of aggregations and parent/sibling pipelines, or "term" vs "filter" filters might I need?

Thanks much! Having a great time learning to use this tool.

[tsullivan (Tim Sullivan)]

Hi John, can you supply a screenshot of what the visualization builder looks like that created the pie chart, and also perhaps a few samples of documents that are in your index? I'd like to help but I think I'm stuck on what the event data looks like.

[joconner (John O'Conner)]

Thanks so much for your offer to help @tsullivan. Do these help?

The visualization of the pie chart:

Some events and the pertinent log lines:

I've picked a single resource_id and have shown all the events on that resource. Other resource's have the same events and calculations for elapsed time. I want to show how those elapsed_time averages change over the week using a line graph. For example, I'd be able to see how the system spends time (on average) for the CALC_WCI_* process across all tracked resources. It wouldn't even have to be an "average" time and could just be total time if that's easier. I just need a sense of how the time spent between paired events changes over time. Make sense?

[tsullivan (Tim Sullivan)]

@joconner

I just need a sense of how the time spent between paired events changes over time.

It looks like you really just need to look at the elapsed time field of the *_END fields for this - based on the observation that field is null for the other events, and your filters aggregation only uses the *_END fields.

The way you would do this in a Line Chart is very similar to what you did for the pie chart, except there is a Date Histogram aggregation that makes time buckets for a regular interval of dates.

Thanks to your mapping and the sample data, I made a date histogram line chart that I think does what you're looking for:

Sorry, the data I indexed is not very continuous so it segments all over the place. Hopefully this conveys the idea though.

[joconner (John O'Conner)]

Wow @tsullivan, you've really helped. AND I didn't realize that the "filters" needed to be in that syntax. Makes me think I need to redo the pie chart to use that syntax. I just used CALC_UNION_END instead of event:CALC_UNION_END for example in the filters.

[tsullivan (Tim Sullivan)]

When you don't specify a field name in the filter, Elasticsearch uses the _all field which I tend to avoid for performance reasons:

• https://www.elastic.co/guide/en/kibana/current/lucene-query.html
• https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-query-string-query.html

BTW I mixed up my data a bit more, and got my line chart working a little easier by splitting using a terms aggregation instead of a filters:

[joconner (John O'Conner)]

Can you share how terms vs filters works? How are they different? How does it affect the chart?

[tsullivan (Tim Sullivan)]

Terms aggregation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html

Filters aggregation: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filters-aggregation.html

In a terms agg, the buckets are built dynamically based on what terms are found, in this case for each time bucket provided by the date histogram aggregation. The line chart only renders data for the terms that are present for each time bucket, so it'll show gaps in the chart when there isn't a term for a particular time bucket.

In a filters agg, it looks for all the terms you give it in each time bucket. That's why it shows zeroes when a term doesn't have any data for a particular time bucket.

[joconner (John O'Conner)]

Thanks again @tsullivan. Last question on this:
I'd like to calculate the sum of the 2 averages on this graph. What would be the magic to do that? I've tried adding a Y-axis with a "Sum" calculation on the elapsed_time but that's just completely wrong. God, there are a lot of options. I know how I need to spend my weekend.

Here's the current visualization details:

[tsullivan (Tim Sullivan)]

I think you can use the Time Series Visual Builder here. I'm a little out of my depth here though because my test data I put in isn't very good and I wasn't able to get something working.

• in Panel Options, set your index and time field

• in the Data panel, make an average of elapsed_time, chain it to an Overall Sum

• the Group By is going to be your filters

There's some helpful guidance in the blog article written about TSVB: https://www.elastic.co/blog/master-time-with-kibanas-new-time-series-visual-builder