Formatting a date in an alert email

Elastic Stack Kibana
1

Hi,
I use Kibana 7.17.
In "Rules and Connectors", I created an alert generating an email.
In this email, I display a date (value of a field) like : 2023-04-28T17:23:23.943Z
The user would like a date like 28/04/2023 17 :23 :23
Is there a way to do it (a kind of string filter) ?
Thank you.

1 Like
2

Hi @ppic,

Where are you getting the date from? Is it the {{date}} value from the alert context?

3

Hi Carly,
The date comes from a database.
I display it in a loop like :

{{#context.hits}} - Application : {{_source.sql.metrics.string.errmsg_application}}   at : {{_source.sql.metrics.string.errmsg_date}}
{{/context.hits}}

(strange display if I use ``` instead of >, it doesn't like #)
That is the main point, but I am also interested in better readability of {{context.date}}.

Best rgds

4

Hi @ppic,

Thanks for confirming. How are you ingesting your data from the DB into Elasticsearch? If you're using Logstash I would say you could use something like a date filter. But I don't think it can be transformed in the message segment itself as that's just markdown.

5

Hi Carly,

I use the 'sql' module of 'metricbeat', without Logstash.
I posted another item that is similar: the text of a db field in the alert mail has no more new lines (and then, becames hard to read).
So, if there is no solution inside the mail connector, the only way is to transform the text upstream.

  • In Kibana ?
  • In the SQL query (in sql.yml) ? I am testing it....
  • In the monitored application, and ask the dev team to produce text field dedicated to this purpose, taking into account the makdown syntax. That is heavy !
    The second point looks promising.
    I'll tell you soon...
6

Hi @ppic ,

I would probably go with either in the query itself as a first option. Give it a try and let me know. You could also make use of Runtime fields as another option and translate it using Painless.

Let us know how you get on.

7

Hi Carly,

It works.
I modified the SQL query with :

FORMAT(Date,'dd/MM/yyyy hh:mm:ss ') as 'ErrMsg_Date',

For the new lines (add two spaces before the NL) :

REPLACE( Message, CHAR(13) + CHAR(10), '  ' + CHAR(13) + CHAR(10) )   'ErrMsg_Message'

because the server runs Windows.
I didn't understand the Runtime fields (I didn't spend time on it).
Do you think it can transform {{context.date}} ?
But by now, I'm satisfied with the SQL solution.
Best rgds.

1 Like
8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.