Timeseries visualization for plotting no. of matching documents against time

[sharmashrayansh (Shrayansh Sharma)]

Hello,

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.
Thanks.

[Nathan_Reese]

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.

[sharmashrayansh (Shrayansh Sharma)]

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.

[Nathan_Reese]

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.