Splunk Search

Time value difference in duration: getting value as 0d

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

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

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

Communicator

THanks a lot for the answer.

0 Karma

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!