Question

When I run this query:

 _sourceCategory=sourceCategory
  | parse "] [*][*][*].[*]" as (user, datasource, session, command) 
  | count, min(_messageTime), max(_messageTime) by session

I get the following results:

 # session _count _min _max
 1 7oEmE+KLpk1nVYpF 22 1.35844e+12 1.35844e+12
 2 6uklr9UDkTOg79je 412 1.35844e+12 1.35844e+12
 3 q0K6ztX9IvpZWh1p 18 1.35844e+12 1.35844e+12

 

In the results, the _min and _max values are displayed as an epoch value. How can I format these values into a readable date and time?

Answer:

Sumo Logic has a few experimental functions that may help with this case, including formatDate,fromMillis and toLong.

  • formatDate formats a data value to a date.
  • fromMillis formats the epoch time string value to a data value that can be read by the formatDate function.

To convert the epoch time into a date formatted string, you can put the first two functions together, like this:

* | formatDate(fromMillis(_messagetime), "MM-dd-yyyy HH:mm:ss") as myDate 

 

However, in the case where you are using Min and Max to get the first and last values, you also need to convert the return value to a "long" value using the toLong function. This is because when you run the min and max functions, the return value gets reformatted as a double that the fromMillisfunction cannot read.

 * | count, min(_messagetime) as mindate | formatDate(fromMillis(toLong(mindate))

For the given example, the following query gets the proper date/time values in the results:

_sourceCategory=sourceCategory
  | parse "] [*][*][*].[*]" as (user, datasource, session, command)
  | count, min(_messagetime) as mindate, max(_messagetime) as maxdate by session
  | formatDate(fromMillis(toLong(mindate)),"MM-dd-yyyy HH:mm:ss:SSS") as mindate
  | formatDate(fromMillis(toLong(maxdate)),"MM-dd-yyyy HH:mm:ss:SSS") as maxdate