Splunk Search

How to create a new field based on existing fields?

Edwin1471
Path Finder

Hi, how can I combine two fields (2.1 and 2.2) into one field (Main calculation)

I have a table : 

Edwin1471_0-1661600824776.png

 

I would like to convert it into something like this :   where START_TIME is the value of 2.1  and the FINISH_TIME is the value of 2.2 field. Completion_time is sum of both two fields  (0.35 + 60.53)

Edwin1471_1-1661600862421.png

 

SPL query: 

 | eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  

| eval Process=if(Process="013","2.1 Main calculation",Process)

| eval Process=if(Process="014","2.2 Main calculation",Process)

| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time

| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process

 

Labels (3)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @Edwin1471,

sorry I made an erro, it isn't a good idea to have spaces or special chars in field names:

| eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  
| eval Process=if(Process="013","2.1 Main calculation",Process)
| eval Process=if(Process="014","2.2 Main calculation",Process)
| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time
| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process
| rename 
   "2.1 Main calculation" AS 21_Main_Calculation 
   "2.2 Main calculation" AS 22_Main_Calculation 
| eval 2_Main_Calculation=case(Process="2.START_TIME",21_Main_Calculation, Process="3.FINISH_TIME", 22_Main_Calculation, Process="4.Completion_time",21_Main_Calculation+22_Main_Calculation)
| rename 2_Main_Calculation AS "2.Main Calculation"
| table Process "2.Main Calculation"

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @Edwin1471,

if the first part of the column headers is static (always 2.1. or 2.2) you can use eval at the end of your search:

| eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  
| eval Process=if(Process="013","2.1 Main calculation",Process)
| eval Process=if(Process="014","2.2 Main calculation",Process)
| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time
| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process
| eval "2.Main Calculation"=case(Process="2.START_TIME","2.1 Main calculation", Process="3.FINISH_TIME","2.2 Main calculation", Process="4.Completion_time","2.1 Main calculation"+"2.2 Main calculation")
| table Process "2.Main Calculation"

 Ciao.

Giuseppe

0 Karma

Edwin1471
Path Finder

I am getting this result

Edwin1471_0-1661611286448.png

 

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @Edwin1471,

sorry I made an erro, it isn't a good idea to have spaces or special chars in field names:

| eval finish_time_epoch = strftime(strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval start_time_epoch = strftime(strptime(START_TIME, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d %H:%M:%S")
| eval duration_s = strptime(FINISH_TIME, "%Y-%m-%d %H:%M:%S") - strptime(START_TIME, "%Y-%m-%d %H:%M:%S")
| eval duration_min = round(duration_s / 60, 2)
| rename duration_min AS Completion_time  
| eval Process=if(Process="013","2.1 Main calculation",Process)
| eval Process=if(Process="014","2.2 Main calculation",Process)
| table Process,  2.START_TIME, 3.FINISH_TIME , 4.Completion_time
| sort -START_TIME, -FINISH_TIME
| sort +Process
| transpose 0 header_field=Process column_name=Process
| dedup Process
| rename 
   "2.1 Main calculation" AS 21_Main_Calculation 
   "2.2 Main calculation" AS 22_Main_Calculation 
| eval 2_Main_Calculation=case(Process="2.START_TIME",21_Main_Calculation, Process="3.FINISH_TIME", 22_Main_Calculation, Process="4.Completion_time",21_Main_Calculation+22_Main_Calculation)
| rename 2_Main_Calculation AS "2.Main Calculation"
| table Process "2.Main Calculation"

Ciao.

Giuseppe

Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...