Kibana - Per-date function

Elastic Stack Kibana
1

Hi, I have an interesting situation and I would like to know if there is anything I can do about it.
We are trying to build a dashboard that will visualize our open issues in our issue-handling system (JIRA etc...)
Now I have an index in Elastic that more-or-less is a full copy of the issues that we have on the system.
Each such issue has a creation date and a resolution date, and I would like to create a graph that shows an accumulated number of issues
So for example - On Monday we had no issues in the system (Yeah right :wink: ) then on Tuesday 3 issues were opened, then on Wednesday 4 issues were opened but 2 were resolved, and then on Thursday another issue was opened and 5 were resolved.
So what I want to see is
Monday - 0, Tuesday -3, Wednsday - 5, Thursday - 1
But I don't know how to do that...
Today I can filter out resolved issues entirely, but then I will not see them at all, and I am not sure how I can see them on specific dates but not on other dates. if I had a formula that allows me to know which date it is on, I could do that, but I don't think there is
Any idea?

2

I don't have Jira data, but I have github data and I think it's probably the same as far as having separate documents ingested into Elasticsearch for the open and close actions.

(you could alternatively ingest docs using the issue number as the doc id and in this case docs in Elasticsearch would be updated to show the current state)

In this example, I'm looking at a single issue. The first event is the opened action and the last one on the top is the closed action.

image
image3204×1368 420 KB

For the purposes of seeing how many open issues there are over time, I think we can exclude all the other actions and so I'll filter to only the opened and closed actions.

image
image3192×830 238 KB

Lens has a cumulative sum aggregation and I think that would work well if you have a field which has a value of 1 for the opened action and a value of -1 for the closed action (and zero for any other action).

We should be able to do this with either a scripted field, or a runtime field with a script something like this;

if (doc.containsKey('action.keyword')) {
    if (doc['action.keyword'] == null) emit(0);
    if (doc['action.keyword'].size() == 0) emit(0);
    
    if (doc['action.keyword'].value == "opened") {
        emit(1);
    } else if (doc['action.keyword'].value == "closed") {
        emit(-1);
    } else emit(0);
}

Unfortunately I'm getting shard errors with that runtime field. I'll update this post once I resolve that.

Regards,
Lee

3

Thanks! but then how would you use that to show change over time?

4

OK. So my mistake was thinking that emit instantly returned. But it doesn't. So my triple check here for containsKey and not empty and not size == 0 is a bit overkill, but it works if I use them correctly. So with this I have a runtime field of type Long (integer) which is +1 when an issue is opened, -1 when an issue is closed, and 0 for any other action.

if (doc.containsKey('action.keyword')) {
    if (!(doc['action.keyword'].empty)) {
        if (!(doc['action.keyword'].size() == 0)) { 
            def action = doc['action.keyword'].value;
            if (action == "opened") {
                emit(1);
            } else if (action == "closed") {
                emit(-1);
            } else emit(0);
        }
    }
}

Then I create a new Lens visualization and use Cumulative sum aggregation on that runtime field over time;

image
image1920×895 105 KB

I'm still trying to figure out why I have multiple closed action docs for some issues. Until I resolve that this data is invalid for me. But hopefully on your Jira data you can isolate single opened and closed events per issue.

I can use a filter to only get docs where the action.keyword is opened or closed but it doesn't change the chart any since I'm setting the runtime field to 0 for anything else.

This simple visualization isn't matching the issue numbers. So the results are really aggregate data of how many were opened vs how many closed since the start of the time span. You could, for example, pick a start date after a bunch of issues were opened and before they were closed and get a negative cumulative sum. If you set your start time back before your very first issue was opened, the final cumulative sum should end with the number of issues currently open.

Some other visualization types besides Lens might also support the cumulative sum.

Regards,
Lee

5

Hi @LeeDr, thanks for the detailed explanation! so at the end of the day, you are using multiple documents, each one for any status change. that is what I was trying to avoid having, but could not figure out any way of doing so, so I ended up doing something similar. we now have the same issue once if it is open, and twice if it was closed, and we have a similar field that has either +1 or -1 depending if it was opened or closed, and aggregate it.
it does mean that in all my other statistics, I need to filter out all the copies and keep only the last copy of the issue

Thanks Again!

6

I think I solved in my github data. I discovered that there is also a reopened action in github. I changed my runtime field to return 1 for both opened and reopened.

7

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