Compare two dates with ES datasource

I’m using Grafana v8.0.5.
I’m currently having troubles to compare two dates.
Indeed i’m using Elasticsearch datasource. I can get a SSL certificate expiration date in unix timestamp (so a future date) and I want to compare it with the actual date, in order to know how many days it left before certificate expiration.
I don’t know how to achieve that. Most of topics that answer this question use InfluxDB or Prometheus as datasource, but didn’t find any solution for Elasticsearch datasource.
Many thanks !

HI @Buto

So you are able to view your timestamps in grafana inside, say the table panel visualization?

Indeed, i first use a prometheus exporter in order to have the certificate EOL’s timestamp corresponding (like 1.727720043e+09), then registrer it in Elasticsearch, and use this ES as a datasource for my Grafana, so yes i could use the table panel like that :

But i also want to know how many days it left before certificate expiration, in order to create an alert on it and get notified if the duration is under 10 days (for exemple)

Sorry to “up” this topic, but i feel like i’m close to get the desired duration.
Does anyone know how can i substract a future date with the actual timestamp with Elasticsearch as a datasource ?
Many thanks

Don’t want to bother anyone, but if someone could help me with this request that would be awesome.
Thanks !

Hi @Buto

Sorry, this appears to be a tough one. Let’s see if we can dig into this and get you help.

So, looking at that screenshot: basically, you want to add a third column that a) calculates the difference between time and cert_not_after per row, and then b) evaluates if that calculated time difference is less than ten days. Is that correct?

I think we can get you there :sunglasses: :grafana:

Hi @mattabrams
Thank you very much for your answer and your help !!
Actually what i need is just the difference between actual time and cert_not_after.
If possible i would like to have it in a time series panel in order to be alerted when the difference is below X days (because if i’m right, only graph panel can provide alerting)
And if this difference is below 0, then just show 0 (i know i can use min value in order to don’t get negative values)
Still didn’t manage to get this difference in a time series panel :frowning:

Hi @mattabrams

Sorry to up you again :sweat_smile:
Do you have any idea concerning my issue ?

Hi again,
No idea from anyone ? :confused:

share your raw unformatted data for the community, and please refrain from continually bumping your post :+1:

Did anyone ever find a solution on this? I’m running into the exact same issue.