How to create a report based on multiple copies of the same record in Kibana

[arunavab (Arunava Bhattacharya)]

We are new to Kibana and are Collecting Data from a tool in this format and storing it in a CSV file.

[ID | Title | Status | Priority | Created Date | Resolved Date]

Now one ID will be repeated multiple times as work goes on that item and it gets changed and the data gets appended to the file.

I have attached screenshot of how the Data is supposed to look after multiple appends.

ID	Summary	Status	Priority	Created	Resolved
KEY-1	Record 1	Open	High	2020-01-15
KEY-1	Record 1	In Progress	High	2020-01-15
KEY-1	Record 1	In Progress	Medium	2020-01-15
KEY-1	Record 1	Closed	Medium	2020-01-15	2020-01-19
KEY-2	Record 2	Open	Medium	2020-01-17
KEY-2	Record 2	In Progress	Medium	2020-01-17
KEY-2	Record 2	Closed	Medium	2020-01-17	2020-02-03
KEY-3	Record 3	Open	High	2020-02-01
KEY-3	Record 3	In Progress	High	2020-02-01
KEY-3	Record 3	Closed	High	2020-02-01	2020-02-15
KEY-4	Record 4	Open	High	2020-02-15
KEY-4	Record 4	In Progress	High	2020-02-15
KEY-4	Record 4	Closed	High	2020-02-15	2020-02-21
KEY-5	Record 5	Open	High	2020-02-15
KEY-5	Record 5	In Progress	High	2020-02-15
KEY-5	Record 5	Closed	High	2020-02-15	2020-03-01


I want to create a report that will report the number of records created vs. resolved in a dynamic time range in Kibana from this Data.

So it must run a query like this select DISTINCT count(ID) where created date >= input FROM date AND created date <= input TO date

For example in the attached image as you can see there are 2 records that are created and 1 that is resolved or closed if the time range is for January (From 1st Jan - 31st Jan).

How can I achieve this in Kibana? I have managed to import the data in Kibana via Logstash.

[Patrick_Mueller (Patrick Mueller (Kibana Alerting))]

I think I'd build this as a dashboard with a Lens visualization for each count, using a filter for each one, where one filters on status: "open", the other on status: "closed". That should give you the counts of opened and closed, for whatever time range is selected in the dashboard's time picker.

I just built an example of this (from some of my own data), that looks like this:

I think this would require you to have the index pattern for the index set to use the created date set to the document timestamp. I'll have to do some more investigation to see if this can be made to work if you can't set that field to be the document timestamp.