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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...