Data Table - show number of events for each device, each day for last 7 days

[MichaelC]

I've been trying to recreate a report that shows the number of events occurring on specific devices, each day over seven days. The purpose of the report is to monitor the event number and make note of event counts that are much higher or lower than normal.

Creating a typical data table visualization has proved difficult due to having two separate fields to sort by, both sensor.name and timestamp. I want the report to look like this:

The best I've been able to do in Kibana looks like this, with separate columns for each day and the sensor name appearing each time, which is clunky.

I pivoted to the time series visual builder, which looked promising, but there are no controls for data offset when selecting data table. I thought creating 7 separate columns with a filter applied to each one with @timestamp:[now-2d TO now-1d], @timestamp:[now-3d TO now-2d], etc. would work. However the filter doesn't support that type of query.

Any suggestions? Thanks

[thomasneirynck]

@MichaelC

So these spreadsheet like tables are difficult to do in Kibana (for the reasons you describe).

I think one alternative to consider is a heatmap visualization and turn on labeling.

For the Y-axis, choose your term-agg on your sensor-field. And for the X-axis, choose a Date-histogram with day-intervals. For the metric, do a count.

See https://www.elastic.co/blog/awesome-new-kibana-visualizations-heatmap-and-point-series

The actual values will be in the tooltip when hovering over a cell.

[MichaelC]

Thank you Thomas, I'll be able to make the heatmap visualization work. I'll have to split the sensors into two visualizations, one with ascending count and the other descending, due to the series limit. However this works for me and from the sound of things this area might be improved upon in the future.