Splunk Cloud Platform

Search Reference

convert

Description

The convert command converts field values in your search results 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 tonumber() to convert field values.

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 a wildcard ( * ) character to specify all fields.
ctime()
Syntax: ctime(<wc-field>)
Description: Convert a UNIX time to an ASCII human readable time. Use the timeformat option to specify the exact format to convert to. You can use a wildcard ( * ) character to specify all fields.
dur2sec()
Syntax: dur2sec(<wc-field>)
Description: Convert a duration format "[D+]HH:MM:SS" to seconds. You can use a wildcard ( * ) character to specify all fields.
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 a wildcard ( * ) character to specify all fields.
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 a wildcard ( * ) character to specify all fields.
mstime()
Syntax: mstime(<wc-field>)
Description: Convert a [MM:]SS.SSS format to seconds. You can use a wildcard ( * ) character to specify all fields.
none()
Syntax: none(<wc-field>)
Description: In the presence of other wildcards, indicates that the matching fields should not be converted. You can use a wildcard ( * ) character to specify all fields.
num()
Syntax: num(<wc-field>)
Description: Like auto(), except non-convertible values are removed. You can use a wildcard ( * ) character to specify all fields.
rmcomma()
Syntax: rmcomma(<wc-field>)
Description: Removes all commas from value, for example rmcomma(1,000,000.00) returns 1000000.00. You can use a wildcard ( * ) character to specify all fields.
rmunit()
Syntax: rmunit(<wc-field>)
Description: Looks for numbers at the beginning of the value and removes trailing text. You can use a wildcard ( * ) character to specify all fields.

Usage

The convert command is a distributable streaming command. See Command types.

Basic examples

1. Convert all field values to numeric values

Use the auto convert function to convert all field values to numeric values.

... | convert auto(*)

2. Convert field values except for values in specified fields

Convert every field value to a number value except for values in the field src_ip. Use the none convert function to specify fields to ignore.

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

3. Change the duration values to seconds for the specified fields

Change the duration values to seconds for the specified fields

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

4. Change the sendmail syslog duration format to seconds

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". This example uses the dur2sec convert function.

... | convert dur2sec(delay)

5. Convert field values that contain numeric and string values

Convert the values in the duration field, which contain numeric and string values, to numeric values by removing the string portion of the values. For example, if duration="212 sec", the resulting value is duration="212". This example uses the rmunit convert function.

... | convert rmunit(duration)

6. Change memory values to kilobytes

Change all memory values in the virt field to KBs. This example uses the memk convert function.

... | convert memk(virt)

Extended Examples

1. Convert a UNIX time to a more readable time format

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

source="all_month.csv" | convert timeformat="%H:%M:%S" ctime(_time) AS c_time | table _time, c_time

  • The ctime() function converts the _time value in the CSV file events to the format specified by the timeformat argument.
  • The timeformat="%H:%M:%S" argument tells the search to format the _time value as HH:MM:SS.
  • The converted time ctime field is renamed c_time.
  • The table command is used to show the original _time value and the ctime field.

The results appear on the Statistics tab and look something like this:

_time c_time
2018-03-27 17:20:14.839 17:20:14
2018-03-27 17:21:05.724 17:21:05
2018-03-27 17:27:03.790 17:27:03
2018-03-27 17:28:41.869 17:28:41
2018-03-27 17:34:40.900 17:34:40
2018-03-27 17:38:47.120 17:38:47
2018-03-27 17:40:10.345 17:40:10
2018-03-27 17:41:55.548 17:41:55

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.

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

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 field values from a minutes and seconds to just seconds.

The converted time field is renamed ms_time.

  • The table command is used to show the original _time value and the converted time.
_time ms_time
2018-03-27 17:20:14.839 1522196414.839
2018-03-27 17:21:05.724 1522196465.724
2018-03-27 17:27:03.790 1522196823.790
2018-03-27 17:28:41.869 1522196921.869
2018-03-27 17:34:40.900 1522197280.900
2018-03-27 17:38:47.120 1522197527.120
2018-03-27 17:40:10.345 1522197610.345
2018-03-27 17:41:55.548 1522197715.548

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

3. 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

See also

Commands
eval
fieldformat
Functions
tonumber
strptime
Last modified on 21 November, 2022
contingency   correlate

This documentation applies to the following versions of Splunk Cloud Platform: 9.2.2406, 8.2.2201, 8.2.2203, 8.2.2112, 8.2.2202, 9.0.2205, 9.0.2208, 9.0.2209, 9.0.2303, 9.0.2305, 9.1.2308, 9.1.2312, 9.2.2403 (latest FedRAMP release)


Was this topic useful?







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

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters