Splunk Search

Time value difference in duration: getting value as 0d

Chandras11
Communicator

HI All,

I am able to get the time value difference in epoch and able to convert it to string with the following command:-

eval LeadDays = ( Answer_Time - Bookingdate) |  eval LeadDays = tostring(LeadDays, "duration") |

Bookingdate             Answer_Time  LeadDays
1535635518.000000   1535708751.000000   20:20:33.000000
1535636031.000000   1536059535.000000   2+21:38:24.000000

The problem is in the first row: is there a way to convert it to 0+20:20:33.000000 instead of 20:20:33.000000

I tried to use string concat but it didnt work.

Also is there a way to convert 2+21:38:24 to only days as 2+21/24+38/3600= 2.88 days

0 Karma
1 Solution

aholzer
Motivator

try this:

| eval LeadDays = if(like(LeadDays,"%+%"), LeadDays, "0+".LeadDays)
| rex field=LeadDays "^(?<days>[^\+]+)\+(?<hours>[^:]+)\:(?<minutes>[^:]+)"
| eval new_LeadDays = round(days + hours/24 + minutes/3600, 2)
| fields - days hours minutes

Explanation:

  1. first preppend a "0+" if the LeadDays doesn't contain a "+" in it
  2. capture the days / hours / minutes into different fields
  3. use the fields captured in #2 to calculate a new field as per your requirements
  4. remove the unnecessary fields

Hope this helps

View solution in original post

aholzer
Motivator

try this:

| eval LeadDays = if(like(LeadDays,"%+%"), LeadDays, "0+".LeadDays)
| rex field=LeadDays "^(?<days>[^\+]+)\+(?<hours>[^:]+)\:(?<minutes>[^:]+)"
| eval new_LeadDays = round(days + hours/24 + minutes/3600, 2)
| fields - days hours minutes

Explanation:

  1. first preppend a "0+" if the LeadDays doesn't contain a "+" in it
  2. capture the days / hours / minutes into different fields
  3. use the fields captured in #2 to calculate a new field as per your requirements
  4. remove the unnecessary fields

Hope this helps

Chandras11
Communicator

THanks a lot for the answer.

0 Karma

Chandras11
Communicator

I was trying to use the match command in eval case and it was giving me issues. This one is working like a charm.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...