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 byctime
andmktime
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 The |
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
Answers
Have questions? Visit Splunk Answers and see what questions and answers the Splunk community has using the convert command.
contingency | correlate |
This documentation applies to the following versions of Splunk® Enterprise: 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11, 7.0.13
Feedback submitted, thanks!