Follow-up: metrics of daily open count from open and closed date and create bar chart

[narschang]

I asked this question a few months ago, and I am asking the same question again because the old ticket is closed when I realized there was a response and possibly a solution. (Link to the old ticket 121737. Thank you, Timrose - who responded a few months ago.)

We have a DevOp ticket status dashboard to show the number of IT help desk tickets in the pipeline and the status of the ticket. I want to create a bar chart to show the number of open tickets daily and I am not sure how to do it.

DB configuration:
The index name is _index:devop_api_mdn-% and the document id (_id) is the helpdesk ticket ID. The data is updated hourly and the document of a specific ticket is updated every time when the status of the ticket changes. Since each ticket has an open date and close date (when it's closed), is there a way to get the number of open tickets daily based on the open and close dates? For example, if I have 10 tickets open and closed in January, can I get a date histogram chart to show the open ticket number? I created a chart to show the expected outcome.

I understand that this metrics can be achieved by configuring the index a different way, for example - differentiating the ingestion by having date stamp as part of the index name to keep a record of the daily open count. The issue here is that if we do it that way, we will end up have a lot of duplicated data in the system.

Based on the response, this use case is possibly achievable by using the Timelion plug-in. (Thanks again, Timrose.)

How can I create this chart using Timlion? Thank you very much.

Nars

[timroes]

Hi Nars,

thanks for following up and sorry - I totally forgot about responding to that when I came back from conference.

You can achieve something like that:

You can actually use timelion to do the following calculation:

.es(index=evop_api_mdn-*, timefield=open).cusum().subtract(.es(index=evop_api_mdn-*, timefield=closed).cusum())

That will basically cumulative sum up all opened issues, and subtract from that the cumulative summed closed issues.

There is one huge drawback with that method: it will always assume the open issue count was 0 at the beginning of the timerange you are watching, since cusum will only sum the values within the timerange.

I also haven't found a way to actually work around that issue. So the correct results would only be shown if you know that the amount of open issues are actually 0 at the beginning of the timerange you watch or if you always watch the whole timerange of all your data (in which case you know that there were 0 open tickets before that).

Unfortunately there is no way to solve this properly with any Kibana visualization in the way that the data is formatted for you.

The only solution I would know:

Write that required information to ES

You could setup a Cronjob on some system, that executed nightly, and will calculate each night, how many open issues there are at that point in time and how many closed and just subtract that from each other. The result would be the amount of open issues for that day, and you would need to write that information into a new document in another index, that would actually just have documents, that have the day as one property and the amount of open issues as another.

That way you have the required information in a format, that every visualization would understand, but of course you need a separate system, that calculate that periodically and writes it into ES.

Sorry that there is no better solution I could offer for you.

Cheers,
Tim

[narschang]

Thanks, Tim. Appreciate the solution a lot!!

The one using timeline cusum and subtract doesn't work on our case because the number was not zero at the beginning of the timerange. But I can see using this calculation in other use cases. Thanks for looking into it.

For now, I am going to do as you suggested - running a midnight cron job daily to write the data into a separate index to get those numbers.

Thanks again.