Usage of Splunk commands : CONVERT

Usage of Splunk commands  : CONVERT

Usage of Splunk commands : CONVERT is as follows:

• This command converts the field values to numerical values.
• If you don’t specify AS clause with then old value will be overwritten by new values.

Find below the skeleton of the usage of the command “convert” in SPLUNK :

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

There are several convert functions and arguments with convert command.

• timeformat – Specify output format of time fields. The time format option is used for ctime and mktime functions. By default value is  “%m/%d/%Y %H:%M:%S”.
• mktime – Convert  human readable time format epoch time format. You can specify the time format by timeformat argument. This is an alternative option of strptime() function in eval functions.
• ctime – Convert an epoch time format to human readable time format. You can specify the time format by timeformat argument. This is an alternative option of strftime() function in eval functions.

Below we have given an example of a custom “date time field” .

In the above query we have extracted time portion from every event as a ATIMESTAMP through interactive field extractor. 

Example 1:

index=_internal sourcetype=splunkd_ui_access
| stats latest(ATIMESTAMP) as LT 
| rex field=LT "(?<NEWTIME>.*?):.*" 
| convert timeformat="%d/%b/%Y" mktime(NEWTIME) as "ConvertedEpochTime"

In the above query ATIMESTAMP is an existing field name of  _internal index name and sourcetype name is splunkd_ui_access . By latest function with stats command we have taken the latest value of ATIMESTAMP. From the ATIMESTAMP field we have taken date,month and year portion by rex command and store the value in NEWTIME field . At last we have converted  NEWTIME field values to epoch time format values  by mktime function and store the value in a new field called “ConvertedEpochTime” . We have given time format as “%d/%b/%Y” to format by date/month/year . By default it takes “%m/%d/%Y %H:%M:%S”.

Example 2:

index=_internal sourcetype=splunkd_ui_access
| stats latest(_time) as LT 
| convert ctime(LT) as "ConvertedEpochTime"

Result:

Explanation: 

In the above query _time is an internal field name of  _internal index name and sourcetype name is splunkd_ui_access . Also _time represents the event time in Splunk. By latest function with stats command we have taken latest event time and store the value in LT field. Next we have converted epoch time to human readable time format  by ctime function with convert command and store the value in a new field called “ConvertedEpochTime”. We can directly convert from  epoch time format to human readable time format as _time field is in epoch time format by default. We don’t specify any timeformat with ctime function so by default  ctime function takes “%m/%d/%Y %H:%M:%S” as the timeformat. 

***************************************************************************

Example 3:

index=_internal sourcetype=splunkd_ui_access
| stats latest(_time) as LT 
| convert timeformat="%c" ctime(LT) as "ConvertedEpochTime"

Result :

Explanation :

In the above query _time is an internal field name of  _internal index name and sourcetype name is splunkd_ui_access . Also _time represents the event time in Splunk. By latest function with stats command we have taken latest event time and store the value in LT field. Next we have converted epoch time to human readable time format  by ctime function with convert command and store the value in a new field called “ConvertedEpochTime”.We can directly convert from  epoch time format to human readable time format as _time field is in epoch time format by default. We have used “%c” with timeformat argument to format the time field as current locale’s time format defined by systems operating system.

****************************************************************************

Example 4:

index=_internal sourcetype=splunkd_ui_access
| stats latest(_time) as LT
| convert timeformat="%m/%d/%Y %H:%M" mktime(LT)
| eval A=LT-1200
| convert timeformat="%m/%d/%Y %H:%M" ctime(A) as "20_min_before"
| convert timeformat="%m/%d/%Y %H:%M" ctime(LT) as Time
| fields Time,20_min_before

Result :

Explanation:

In the above query _time is an internal field name of  _internal index name and sourcetype name is splunkd_ui_access . Also _time represents the event time in Splunk. By latest function with stats command we have taken latest event time and store the value in LT field. Next we have converted human readable time format to epoch time by mktime function with convert command. Also we have taken upto minute value by timeformat argument as we have given “%m/%d/%Y %H:%M”. By default it takes “%m/%d/%Y %H:%M:%S” . By the eval command we have taken 20 mins earlier epoch time from LT . As we know that epoch time is represented in second . So for getting 20 mins earlier time we have subtracted 1200 second(20*60) from LT and store the value in A field . So now LT and A fields are in epoch time . Again we have converted epoch time format to human readable format by ctime function and store the values in new fields called “20_mins_before” and “Time”. At last by the fields command we have taken only “Time” and  “20_mins_before” fields.

Now you can effectively utilize “convert”  command in  your daily use to meet your requirement !!

Hope you are now comfortable in : Usage of Splunk commands  : CONVERT

Happy Splunking !!