Latest timestamp in lens table?

[David_Hodgson]

I'm trying to create a Lens table in a Dashboard summarising heartbeats, with columns:

• monitor.name,
• monitor.status,
• last_value(@timestamp, kql='monitor.status:"up"'),
• last_value(@timestamp, kql='monitor.status:"down"')
  ...

and Lens is complaining that @timestamp is of the wrong type. I think it will only work with a numeric field.

Is there a workaround?

[stephenb (Stephen Brown)]

@David_Hodgson Welcome to the Community and Nice Use Case! Seems like a useful Table!

@David_Hodgson

Is there a workaround?

Yes! I got one for you ... and @ghudgins I think I perhaps found a bug... (I will put that at the bottom)

Here are the Macro Steps

1. Create a Runtime field for the timestamp that is type keyword
2. Then use Last Value Function with the timestamp_keyword and filter on monitor.status

Create a timestamp_keyword field

Here is the code

emit(doc['@timestamp'].value.toString())

Now create the table (I am using url.domain because I only have a single monitor but multiple domain but same principle)

Whalluh! Now do the same for monitor.status : "down"

Now @ghudgins Here is think is the bug
If I try to do the same thing as a formula... which I think should work

last_value(timestamp_keyword, kql='monitor.status : "up" ')

But it says there is an error return type invalid I would think it should work with a keyword ... but as the OP observed it looks like it only supports numbers but when I do it through the quick function + filter it works

Note Only Numerics .... no keywords

[ghudgins (Graham Hudgins)]

i'm not sure if this is a bug. formula can only work when the resulting field is a number guarenteed...so last value on a keyword field would blow up if it's not a number. it works with a quick function becuase those work on non-number fields (no pesky math, just display it!)

[stephenb (Stephen Brown)]

Yeah but why does the lastvalue formula only work on a number? It should just be whatever the last value in that field is based on the timestamp ... number keyword. Who cares? It's just the last value based on the timestamp... The math is on the timestamp, not on the field value itself.

[ghudgins (Graham Hudgins)]

8.2 adds date support for last value [Lens] unable to add last value of date/string field · Issue #104787 · elastic/kibana · GitHub (in the UI)

[stephenb (Stephen Brown)]

@ghudgins Yup that works via Quick Functions and Formula in 8.2 for both timestamps and keywords .. Thanks!

So @David_Hodgson You can use my workaround or upgrade to version 8.2 and last_value you will work as you expect.