Timeseries visualization for plotting no. of matching documents against time


We are creating a Dashboard to monitor issues from an external system.
We are collecting data of "issues" using Logstash from a different application and storing them in ElasticSearch.
As a crucial requirement, we want to plot No. of Open Issues against a timeline.
Consider a sample document:

  "_id": "978",
  "issue-id": "issue01",
  "status": "OPEN",
  "creation-date": "25-Oct-2020",
  "last-modified-date": "26-Oct-2020"

We have creation-date, last-modified-date, and @timestamp as date fields. Time is also stored with date, of course.

Now we want to create a timeseries to visualize number of open issues with time.
So, for example, if an issue closes, the graph should be updated (decrease). If a new issues opens, the line graph should go up, and so on..

I am trying to do this with Time Series Visual Builder (TSVB), but I am not sure on how to have it mathematically correct. I am trying to do a "Cumulative Sum" on Count Aggregation. I have also applied panel filter to only consider "OPEN" issues.
But this is not correct.
Is there a mathematically correct way I can find cumulative sum of open issues and plot it against a timeline?

Regarding index:
A new document is created in the index whenever an issue changes.
So, in essence, all changes of a particular issue are stored within the index.

For example, if status of the above issue (issue01) changes, a new document would be created with a new _id (the old document is still persisting in the index).

  "_id": "979",
  "issue-id": "issue01",
  "status": "CLOSED",
  "creation-date": "25-Oct-2020",
  "last-modified-date": "27-Oct-2020"

Should we think of a different way to index the issues also?

Looking for some guidance here.

Should we think of a different way to index the issues also?

Yes. You should only have a single document per issue and update the status of the document when closed. Then you could use lens to create a chart with time on the x-axis and counts on the y-axis. You can add a filter to only show documents where status is OPEN.

Thanks for the reply @Nathan_Reese.
If we keep only one document per issue, and update the status of the document when closed, how would time-series work?

We want to have a cumulative status of all open issues with time.
Not the count of open issues created on a particular day.
That's why I was trying out with cumulative sum of count.

Below is an example time series visualization showing 2 metrics on the y-axis. The second metric uses "cumulative sum" pipeline aggregation to track the cumulative sum of count.

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