Related Answers
Download topic as PDF
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
timeformatoption is used byctimeandmktimefunctions. 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
timeformatoption 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
timeformatoption 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.
|
PREVIOUS contingency |
NEXT correlate |
This documentation applies to the following versions of Splunk® Enterprise: 6.0, 6.0.1, 6.0.2, 6.0.3, 6.0.4, 6.0.5, 6.0.6, 6.0.7, 6.0.8, 6.0.9, 6.0.10, 6.0.11, 6.0.12, 6.0.13, 6.0.14, 6.0.15, 6.1, 6.1.1, 6.1.2, 6.1.3, 6.1.4, 6.1.5, 6.1.6, 6.1.7, 6.1.8, 6.1.9, 6.1.10, 6.1.11, 6.1.12, 6.1.13, 6.1.14, 6.2.0, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 6.2.9, 6.2.10, 6.2.11, 6.2.12, 6.2.13, 6.2.14, 6.2.15, 6.3.0, 6.3.1, 6.3.2, 6.3.3, 6.3.4, 6.3.8, 6.3.9, 6.3.5, 6.3.10, 6.3.11, 6.3.12, 6.3.13, 6.3.14, 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.8, 6.4.9, 6.4.10, 6.4.11, 6.5.0, 6.5.1, 6.5.1612 (Splunk Cloud only), 6.5.2, 6.5.3, 6.5.4, 6.5.5, 6.5.6, 6.5.7, 6.5.8, 6.5.9, 6.5.10, 6.6.0, 6.6.1, 6.6.2, 6.6.3, 6.6.4, 6.6.5, 6.6.6, 6.6.7, 6.6.8, 6.6.9, 6.6.10, 6.6.11, 6.6.12, 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, 6.3.6, 6.3.7
Comments
Greetings, I am looking to extract the latest exact value from a field which is also integer... e.g., chart count by latest(6). However, the integer in parenthesis is not being treated as a field. Any suggestions?
Woodcock
Thank you for the suggestion. I have updated the "See also" section.
IMHO, the "See Also" section should have "fieldformat", too.
Would like to see memk() handle Tb. Also, a conversion for TB and Tb, kB and kb, etc.
We have splunk spit out log statements like
latency=1,840
Splunk identitfies latency as Numeric but takes value only as 1 and truncates the other decimal values. And so the timechart over its average also gets affected. I was hoping to use "convert rmcomma" but that didn't help as the latency field has already been stripped of numbers and commas before supplying to convert rmcomma.
dur2sec is not working if more than 23 hours...<br />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.<br />Indeed some tools are reporting the duration using the same delimiters but stops at minutes or hours, and then dur2sec returns a blank answer...
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<br /><br />at least it helped me to know how this 2012-05-27 00:37:30.978 timeformat shoud be described:<br />%Y-%m-%d %H:%M:%S.%N
Cntorres - You can't use a function or time modifier in a BY clause. You could use the latest time modifier before the chart command. I would need further information, such as examples of data before the search and what you want in the output, to help you accomplish what you want.