convert

Description

The convert command converts field values into numerical values. Unless you use the AS clause, the original values are replaced by the new values.

Alternatively, you can use evaluation functions such as strftime(), strptime(), or tostring().

Syntax

convert [timeformat=string] (<convert-function> [AS <field>] )...

Required arguments

<convert-function>

Syntax: auto() | ctime() | dur2sec() | memk() | mktime() | mstime() | none() | num() | rmcomma() | rmunit()

Description: Functions to use for the conversion.

Optional arguments

timeformat

Syntax: timeformat=<string>

Description: Specify the output format for the converted time field. The timeformat option is used by ctime and mktime functions. For a list and descriptions of format options, see Common time format variables in the Search Reference.

Default: %m/%d/%Y %H:%M:%S. Note that this default does not conform to the locale settings.

<field>

Syntax: <string>

Description: Creates a new field with the name you specify to place the converted values into. The original field and values remain intact.

Convert functions

auto()

Syntax: auto(<wc-field>)

Description: Automatically convert the fields to a number using the best conversion. Note that if not all values of a particular field can be converted using a known conversion type, the field is left untouched and no conversion at all is done for that field. You can use wild card characters in the field name.

ctime()

Syntax: ctime(<wc-field>)

Description: Convert an epoch time to an ascii human readable time. Use the timeformat option to specify exact format to convert to. You can use wild card characters in the field name.

dur2sec()

Syntax: dur2sec(<wc-field>)

Description: Convert a duration format "[D+]HH:MM:SS" to seconds. You can use wild card characters in the field name.

memk()

Syntax: memk(<wc-field>)

Description: Accepts a positive number (integer or float) followed by an optional "k", "m", or "g". The letter k indicates kilobytes, m indicates megabytes, and g indicates gigabytes. If no letter is specified, kilobytes is assumed. The output field is a number expressing quantity of kilobytes. Negative values cause data incoherency. You can use wild card characters in the field name.

mktime()

Syntax: mktime(<wc-field>)

Description: Convert a human readable time string to an epoch time. Use timeformat option to specify exact format to convert from. You can use wild card characters in the field name.

mstime()

Syntax: mstime(<wc-field>)

Description: Convert a [MM:]SS.SSS format to seconds. You can use wild card characters in the field name.

none()

Syntax: none(<wc-field>)

Description: In the presence of other wildcards, indicates that the matching fields should not be converted. You can use wild card characters in the field name.

num()

Syntax: num(<wc-field>)

Description: Like auto(), except non-convertible values are removed. You can use wild card characters in the field name.

rmcomma()

Syntax: rmcomma(<wc-field>)

Description: Removes all commas from value, for example rmcomma(1,000,000.00) returns 1000000.00. You can use wild card characters in the field name.

rmunit()

Syntax: rmunit(<wc-field>)

Description: Looks for numbers at the beginning of the value and removes trailing text. You can use wild card characters in the field name.

Examples

1. Convert sendmail duration fields to seconds

This example uses sendmail email server logs and refers to the logs with sourcetype=sendmail. The sendmail logs have two duration fields, delay and xdelay. The delay is the total amount of time a message took to deliver or bounce. The delay is expressed as "D+HH:MM:SS", which indicates the time it took in hours (HH), minutes (MM), and seconds (SS) to handle delivery or rejection of the message. If the delay exceeds 24 hours, the time expression is prefixed with the number of days and a plus character (D+). The xdelay is the total amount of time the message took to be transmitted during final delivery, and its time is expressed as "HH:MM:SS".

Change the sendmail duration format of delay and xdelay to seconds.

sourcetype=sendmail | convert dur2sec(delay) dur2sec(xdelay)

This search pipes all the sendmail events into the convert command and uses the dur2sec() function to convert the duration times of the fields, delay and xdelay, into seconds.

Here is how your search results look after you use the fields sidebar to add the fields to your events:

You can compare the converted field values to the original field values in the events list.

2. Convert a UNIX epoch time to a more readable time format

This example uses syslog data.

Convert a UNIX epoch time to a more readable time formatted to show hours, minutes, and seconds.

sourcetype=syslog | convert timeformat="%H:%M:%S" ctime(_time) AS c_time | table _time, c_time

The ctime() function converts the _time value of syslog (sourcetype=syslog) events to the format specified by the timeformat argument. The timeformat="%H:%M:%S" arguments tells the search to format the _time value as HH:MM:SS.

Here, the table command is used to show the original _time value and the converted time, which is renamed c_time:

The ctime() function changes the timestamp to a non-numerical value. This is useful for display in a report or for readability in your events list.

3. Convert a time in MM:SS.SSS to a number in seconds

This example uses syslog data.

Convert a time in MM:SS.SSS (minutes, seconds, and subseconds) to a number in seconds.

sourcetype=syslog | convert mstime(_time) AS ms_time | table _time, ms_time

The mstime() function converts the _time value of syslog (sourcetype=syslog) events from a minutes and seconds to just seconds.

Here, the table command is used to show the original _time value and the converted time, which is renamed ms_time:

The mstime() function changes the timestamp to a numerical value. This is useful if you want to use it for more calculations.

4. Convert a string time in HH:MM:SS into a number

Convert a string field time_elapsed that contains times in the format HH:MM:SS into a number. Sum the time_elapsed by the user_id field. This example uses the eval command to convert the converted results from seconds into minutes.

...| convert num(time_elapsed) | stats sum(eval(time_elapsed/60)) AS Minutes BY user_id

More examples

Example 1: Convert values of the "duration" field into number value by removing string values in the field value. For example, if "duration="212 sec"", the resulting value is "duration="212"".

... | convert rmunit(duration)

Example 2: Change the sendmail syslog duration format (D+HH:MM:SS) to seconds. For example, if "delay="00:10:15"", the resulting value is "delay="615"".

... | convert dur2sec(delay)

Example 3: Change all memory values in the "virt" field to Kilobytes.

... | convert memk(virt)

Example 4: Convert every field value to a number value except for values in the field "foo" Use the "none" argument to specify fields to ignore.

... | convert auto(*) none(foo)

Example 5: Example usage

... | convert dur2sec(xdelay) dur2sec(delay)

Example 6: Example usage

... | convert auto(*)

See also

eval
fieldformat

Answers

Have questions? Visit Splunk Answers and see what questions and answers the Splunk community has using the convert command.