Simple variation of counts - Serial differencing

[efftee]

Hi Community,

The most apparently simplest of things, that you can do in seconds with a pen and paper, but spent hours trying in Kibana and failed so far. It's driving me completely crazy !!
Try it and tell me

So, I have data that is shipped every 3 hours about individual entities.
The entities are identified by a unique keyword. Lets call it "id".
So I'm examining the number of entities that are shipping data.

For example I have:

• count of unique records of "id" at "timestamp": "now-30d/d" = 800
• count of unique records of "id" at "timestamp": "now-3h/h" = 900

This is easily shown as a simple line in a time-series graph, unique counts of "id" over time.
Dead simple.

In my example, we can all see that over 30 days there was an increase of +100 indiviual entities.

I want to visualize that metric. Just a simple panel with a number (not even in a time series).
And am miserably failing to do so...

Basically the serial difference formula as follows:
metric = (unique count of "id" at "timestamp": "now-3h/h") - (unique count of "id" at "timestamp": "now-30d/d")

I've tried lenses, tsvb, aggregations, TinyMaths, SQL, KQL, and never have I found the way to compare two counts from two different buckets.

I am so appalled at myself it's not funny...
Please help me avoid telling my boss that we are paying hundreds of dollars for a beautiful elastic cloud stack, but that it can't tell him how many additionnal entities we've registered (or lost) over the past 30 days...

[efftee]

Alternatively, could a runtime field script make counts of a specific field in specific time ranges ?
(or is it as I fear only within one document at a time)

[flash1293 (Joe Reuter)]

You are right, runtime fields only operate on a single document.

For now, you can do this in Canvas using SQL and expressions like this:

filters
| essql 
  query="SELECT COUNT(DISTINCT clientip) as total_count FROM \"kibana_sample_data_logs\""
| math {string "total_count - " {filters | essql query="SELECT COUNT(DISTINCT clientip) as last_week_count FROM \"kibana_sample_data_logs\" WHERE timestamp < (NOW() - INTERVAL 7 DAY)" | getCell}}
| metric "Unique count difference (last week)" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} metricFormat="0,0.[000]"
| render

For people stumbling over this in the future: In a future version (most likely 7.14) you will be able to do the same thing in Lens using "Lens formula" in a much simpler way: unique_count() - unique_count(kql="timestamp < now-1w").

[efftee]

Thank you Joe,
I will try this out in canvas (it's for reporting anyway), just a too bad we can't include it in a dashboard.. yet
Eager to see this new "Lens formula" feature appearing soon. Great job.
Thanks for your support !