Formatting Values

Internally timestamps are stored as a epoch (number of seconds since 1st Jan 1970) at millisecond accuracy. The UI automatically displays the @timestamp and @ingesttimestamp as human readable dates.

For examples of formatting or extracting different time values, see Extracting Specific Time Values. For examples of more complex formatting of dates or times from a given time value, see Formatting Dates or Times.

Formatting Numerical Values

There are a number of functions available for formatting numbers:

  • The main function for formatting numbers if format(). This uses the same basic formatting system as used in Java or the C library printf(). For example, to format a floating point number:

    logscale
    format("%,.2f", field=avg, as=avg)

    Or to format a number with commas separating the 1,000s:

    logscale
    format("%,i", field=_count, as=_count)

  • format() can round floating point numbers, but to round numbers, especially if you need to round up or down, use the round():

    logscale
    round(bytes,how=floor)

  • If the number is large or is based on a byte-based value (i.e. megabytes or gigabytes), metric, or seconds, use unit:convert(), which allows for conversion up or down. For example, to convert a byte value into MB:

    logscale
    rate:=128*1024
| unit:convert(rate, as="rate", to="M")

Rotating (Transposing) Tables

Occasionally you may want to rotate a given table to filter or display the contents in a different orientation. For example, with a query like this which searches for PHP events and counts the hosts:

logscale
/php/
| groupBy([host],function=count(),limit=5)

The output might look like:

host_count
BACKUP012
DEV-DB012
MAIL014
NYC-SRV011
PRINT011

By swapping the columns for rows (and vice versa) creates a table with two rows (host and _count):

columnrow[1]row[2]row[3]row[4]row[5]
_count22411
hostBACKUP01DEV-DB01MAIL01NYC-SRV01PRINT01

This is achieved by adding the transpose():

logscale
/php/
| groupBy([host],function=count(),limit=5)