Counting instances per day

[cblackburn36 (Chris Blackburn)]

Hi All,

We have some documents where an id code is logged against a day. Something like:-

{
id: 12345,
date: 09-09-2019
}

Where 12345 changes and there are a bunch of documents each day.

We want to count the number of times an id code is present in the last X days, 7 for example. Ideally we would have a report where, for each day present in the date filter chosen, it would give a line showing how often each asset was seen in the last X days. That might not be the clearest of explanations so I've included a table showing what we'd like:-

	12345	23456	34567	45678
01-09-2019		1	1
02-09-2019	1	2	2
03-09-2019	2	3	3	1
04-09-2019	3	4	4	2
05-09-2019			5	3
06-09-2019	3		6	4

Basically there's two core things to achieve here.

1. Look back 7 days and see the number of times a document is present
2. Pivot the ID's up onto the columns instead of them being rows

Any ideas on how I can get Kibana to do this?

Thanks in advance

Chris

[wylie (Wylie Conlon)]

The closest feature to this is the Machine Learning data frame transforms which lets you create pivots. It might be possible to create a similar query using SQL in the Canvas tool, or by using an ETL tool