Splunk Search

How do I convert "3 days 5 hrs 40 min 11 sec" to seconds?

hylam
Contributor
3*86400+5*3600+40*60+11=279611

The seconds part is always there. The minutes part exists when duration is at least 60 seconds. The hours part exists when duration is at least 60 minutes. The days part exists when duration is at least 24 hours. Knowledge objects have to produce the duration_in_second field automatically when I search for "*".

Bonus: add months and years

EDIT1: test case list
3 days 5 hrs 40 min 11 sec
5 hrs 40 min 11 sec
40 min 11 sec
11 sec

1 Solution

kurdbahr
Path Finder

How about this?

* | head 1 
| eval str="3 days 5 hrs 40 min 11 sec" 
| eval duration_in_seconds=tonumber(replace(str, ".*?(\d+) sec", "\1")) 
+ 60 * coalesce(tonumber(replace(str, ".*?(\d+) min.*", "\1")), 0) 
+ 3600 * coalesce(tonumber(replace(str, ".*?(\d+) hrs.*", "\1")),0) 
+ 86400 * coalesce(tonumber(replace(str, ".*?(\d+) days.*", "\1")),0) 
| table str,duration_in_seconds

The eval duration_... could be used in the definition of a calculated field.

EDIT:
The solution above may not be optimal with regard to performance, because the string has to be parsed four times.
The idea behind this was to do it in a single eval expression.

If you don't mind to create additional fields, it may be better to do it in two steps:

1st step: field extraction:

... | rex field=str "^((?<days>\d+) days )?((?<hours>\d+) hrs )?((?<minutes>\d+) min )?(?<seconds>\d+) sec" 

2nd step: field calculation:

... | eval duration_in_second=if(isnull(days),0,86400*days) 
+ if(isnull(hours),0,3600*hours) 
+ if(isnull(minutes),0,60*minutes) 
+ seconds 

View solution in original post

woodcock
Esteemed Legend

Like this:

... | rex "(?:(?<numDays>\d+)\s+days\s+)?(?:(?<numHours>\d+)\s+hrs\s+)?(?:(?<numMins>\d+)\s+min\s+)?(?<numSecs>\d+)\s+sec" | eval duration_in_seconds = (((((numDays * 24) + numHours) * 60) + numMins) * 60) + numSecs

Given this, you should be able to figure out how to create the associated KOs but I will help a little on that. The eval part should be a calculated field; see here for all of what you need:

http://docs.splunk.com/Documentation/Splunk/6.3.1/admin/Propsconf

0 Karma

hylam
Contributor
| makeresults count=1 | eval x="3 days 5 hrs 40 min 11 sec" | rex field=x "(?:(?<numDays>\d+)\s+days\s+)?(?:(?<numHours>\d+)\s+hrs\s+)?(?:(?<numMins>\d+)\s+min\s+)?(?<numSecs>\d+)\s+sec" | eval duration_in_seconds = ((((numDays * 24) + numHours) * 60) + numMins) * 60) + numSecs

Error in 'eval' command: The expression is malformed.

| makeresults count=1 | eval x="5 hrs 40 min 11 sec" | rex field=x "(?:(?<numDays>\d+)\s+days\s+)?(?:(?<numHours>\d+)\s+hrs\s+)?(?:(?<numMins>\d+)\s+min\s+)?(?<numSecs>\d+)\s+sec"

numDays field is null

0 Karma

hylam
Contributor

btw how can i make syntax highlight?

0 Karma

woodcock
Esteemed Legend

I was missing a single open parenthesis. I re-edited the answer and it works. Try it again.

0 Karma

hylam
Contributor

Please handle the cases when days, hours or minutes are missing.

0 Karma

woodcock
Esteemed Legend

OK, try this:

... | rex "(?:(?<numDays>\d+)\s+days\s+)?(?:(?<numHours>\d+)\s+hrs\s+)?(?:(?<numMins>\d+)\s+min\s+)?(?<numSecs>\d+)\s+sec" | eval duration_in_seconds = (((((if(isnull(numDays),0,numDays) * 24) + if(isnull(numHours),0,numHours)) * 60) + if(isnull(numMins),0,numMins)) * 60) + numSecs
0 Karma

kurdbahr
Path Finder

How about this?

* | head 1 
| eval str="3 days 5 hrs 40 min 11 sec" 
| eval duration_in_seconds=tonumber(replace(str, ".*?(\d+) sec", "\1")) 
+ 60 * coalesce(tonumber(replace(str, ".*?(\d+) min.*", "\1")), 0) 
+ 3600 * coalesce(tonumber(replace(str, ".*?(\d+) hrs.*", "\1")),0) 
+ 86400 * coalesce(tonumber(replace(str, ".*?(\d+) days.*", "\1")),0) 
| table str,duration_in_seconds

The eval duration_... could be used in the definition of a calculated field.

EDIT:
The solution above may not be optimal with regard to performance, because the string has to be parsed four times.
The idea behind this was to do it in a single eval expression.

If you don't mind to create additional fields, it may be better to do it in two steps:

1st step: field extraction:

... | rex field=str "^((?<days>\d+) days )?((?<hours>\d+) hrs )?((?<minutes>\d+) min )?(?<seconds>\d+) sec" 

2nd step: field calculation:

... | eval duration_in_second=if(isnull(days),0,86400*days) 
+ if(isnull(hours),0,3600*hours) 
+ if(isnull(minutes),0,60*minutes) 
+ seconds 

sundareshr
Legend

This will give you that duration in seconds | gentimes start=-1 | eval d=3*86400+5*3600+40*60+11 | eval d=tostring(d, "duration"). You could set this up as a calculated field.

0 Karma

hylam
Contributor

This is NOT what I have asked for. Please convert "3 days 5 hrs 40 min 11 sec" to 279611.

0 Karma

sundareshr
Legend

Sorry mis-read your question 🙂

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 ...