[SOLVED]Event time is not displayed in human readable format in ESA alerts

[XavierFerrier1]

Hello,

I have a very simple ESA alert like this :

@RSAAlert(oneInSeconds=0) 

SELECT  * FROM Event

(

device_type='winevent_nic'

AND user_dst.toLowerCase() IN (admin,administrator)

) ;

But when receiving ESA alert by mail, event_time is displayed in epoch time

Do you know a way to have the event time in human readable format instead of epoch time in mail alert ?

Thanks for your help ! 🙂

[XavierFerrier1 · Accepted solution]

Ok solved

Here is the solution :

@RSAAlert(oneInSeconds=0)

select ((event_time * 1000)).format() as event_time_readable,user_dst  from Event

(

device_type='winevent_nic'

AND user_dst.toLowerCase() IN (admin,administrator)

) ;

[JohnDoe1]

You can also use (event_time * 1000)).toDate()

Ping Jeff Shurtliff​, Xavier Ferrier​

Edit: Do not use  (event_time * 1000)).toDate()

The use of this code generates the following error in esa.log:

2016-05-30 14:41:20,964 [pipeline-sessions-0] ERROR freemarker.runtime - Template processing error: "Unknown date type: ?iso_utc_ms needs a date value where it's known if it's a date-only, time-only, or date+time value. Use ?time, ?date or ?datetime before ? iso_utc_ms to estabilish that."
Unknown date type: ?iso_utc_ms needs a date value where it's known if it's a date-only, time-only, or date+time value. Use ?time, ?date or ?datetime before ? iso_utc_ms to estabilish that.
The problematic instruction:
----------
==> ${meta?iso_utc_ms} [on line 25, column 17 in macros.ftl]
 in user-directive value_of [on line 2, column 198 in 572601f4e4b0807864d97dbb]
----------
Java backtrace for programmers:

It strange as a type checking is done in macros.ftl just before the use of iso_utc_ms :

                <#if meta?is_date>

                ${meta?iso_utc_ms}

                <#else>

                ${meta}

[jeffshurtliff]

Thanks Xavier for updating us with the solution you found!  And thanks John Doe​ for your response as well!

[XavierFerrier1]

YesJohn Doe​ , I made some test and .toDate() doesn't work. Event time is still displayed in epoch time.

[JohnDoe1]

But it works on  EsperTech Esper EPL Online

CREATE schema Event(event_time long);

@Name('Out') select *,(event_time * 1000).format() as event_time_format, (event_time * 1000).toDate () as event_time_toDate  from Event;

Event={event_time=1464621180}

At: 2001-01-01 08:00:00.000

• Statement: Out
  • Insert
    • Out-output={
      • event_time=1464621180,
      • event_time_format='5/30/16 3:13 PM',
      • event_time_toDate=Mon May 30 15:13:00 UTC 2016
    • }

What is the best way to manipulate the event_time with a nice custom human readable format  ?