Search Reference

 


convert

NOTE - Splunk version 4.x reached its End of Life on October 1, 2013. Please see the migration information.

convert

The convert command converts field values into numerical values. Alternatively, you can use functions of the eval command such as strftime(), strptime(), or tostring().

Synopsis

Converts field values into numerical values.

Syntax

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

Required arguments

<convert-function>
Syntax: auto() | ctime() | dur2sec() | memk() | mktime() | mstime() | none() | num() | rmcomma() | rmunit()
Description: Functions for convert.

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, refer to the topic "Common time format variables". Defaults to %m/%d/%Y %H:%M:%S. Note that this default does not conform to the locale settings.
<new_fieldname>
Syntax: <string>
Description: Rename function to a new field.

Convert functions

auto()
Syntax: auto(<wc-field>)
Description: Automatically convert the field(s) 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 in done for that field.
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.
dur2sec()
Syntax: dur2sec(<wc-field>)
Description: Convert a duration format "[D+]HH:MM:SS" to seconds.
memk()
Syntax: memk(<wc-field>)
Description: Accepts a positive number (integer or float) followed by an optional "k", "m", or "g". No letter or k indicates kilobytes, m indicates megabytes, and g indicates gigabytes. The output field is a number expressing quantity of kilobytes. Negative values will cause data incoherency.
mktime()
Syntax: mktime(<wc-field>)
Description: Convert an human readable time string to an epoch time. Use timeformat option to specify exact format to convert from.
mstime()
Syntax: mstime(<wc-field>)
Description: Convert a [MM:]SS.SSS format to seconds.
none()
Syntax: none(<wc-field>)
Description: In the presence of other wildcards, indicates that the matching fields should not be converted.
num()
Syntax: num(<wc-field>)
Description: Like auto(), except non-convertible values are removed.
rmcomma()
Syntax: rmcomma(<wc-field>)
Description: Removes all commas from value, for example rmcomma(1,000,000.00) returns 1000000.00.
rmunit()
Syntax: rmunit(<wc-field>)
Description: Looks for numbers at the beginning of the value and removes trailing text.

Description

Converts the values of fields into numerical values. When renaming a field using AS, the original field is left intact.

Examples

Example 1

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 will look after you use the fields sidebar to add the fields to your events:

ConvertEx1.png

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

Example 2

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 Splunk 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:

ConvertEx2.png

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.


Example 3

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:

ConvertEx3.png

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


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 will be "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 will be "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

Answers

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

This documentation applies to the following versions of Splunk: 4.1 , 4.1.1 , 4.1.2 , 4.1.3 , 4.1.4 , 4.1.5 , 4.1.6 , 4.1.7 , 4.1.8 , 4.2 , 4.2.1 , 4.2.2 , 4.2.3 , 4.2.4 , 4.2.5 , 4.3 , 4.3.1 , 4.3.2 , 4.3.3 , 4.3.4 , 4.3.5 , 4.3.6 , 4.3.7 , 5.0 , 5.0.1 , 5.0.2 , 5.0.3 , 5.0.4 , 5.0.5 , 5.0.6 , 5.0.7 , 5.0.8 , 5.0.9 , 5.0.10 , 6.0 , 6.0.1 , 6.0.2 , 6.0.3 , 6.0.4 , 6.0.5 , 6.0.6 , 6.1 , 6.1.1 , 6.1.2 , 6.1.3 , 6.1.4 , 6.2.0 View the Article History for its revisions.


Comments

dur2sec is not working if more than 23 hours...
it would be good to allow more flexibility: a number is just the number of seconds, mins:secs where mins could be over 60, same for hours at the next round.
Indeed some tools are reporting the duration using the same delimiters but stops at minutes or hours, and then dur2sec returns a blank answer...

Jleduc
June 3, 2013

It would be useful to have timeformats table here, like one in the end of this reference: http://docs.splunk.com/images/1/17/4.2.x_search_language_refcard.pdf

at least it helped me to know how this 2012-05-27 00:37:30.978 timeformat shoud be described:
%Y-%m-%d %H:%M:%S.%N

IKate
October 2, 2012

You must be logged into splunk.com in order to post comments. Log in now.

Was this documentation topic helpful?

If you'd like to hear back from us, please provide your email address:

We'd love to hear what you think about this topic or the documentation as a whole. Feedback you enter here will be delivered to the documentation team.

Feedback submitted, thanks!