convert verbose human readble time to splunktime

[dominiquevocat]

Hi, i got some results that contain a arbitrary amount of time (from Jira) that has a human readable structure which i would however like to convert to a splunk timespan so i can sort results.

example strings would be:
6 hours, 17 minutes
20 minutes
1 day, 1 hour, 20 minutes
5 hours, 10 minutes
1 day, 1 hour, 19 minutes
6 hours, 30 minutes
7 hours, 31 minutes
6 hours, 2 minutes
5 hours, 55 minutes
45 minutes

could potentially contain weeks, months etc...

How would i best convert it so i can sort the value or chart results based on percentage of total of all values etc...

[martin_mueller · Accepted solution]

If you really have no way of getting a machine-readable representation of the age, you can roll your own parsing of this using regular expressions. Here's a start using minutes, hours, and days only:

| stats count | eval age = "1 day, 1 hour, 20 minutes" | eval minutes = if(match(age, "minute"), replace(age, ".*?(\d+)\sminute.*", "\1"), 0) | eval hours = if(match(age, "hour"), replace(age, ".*?(\d+)\shour.*", "\1"), 0) | eval days = if(match(age, "day"), replace(age, ".*?(\d+)\s*day.*", "\1"), 0) | eval age_seconds = minutes*60 + hours*3600 + days*86400

You can extend that to include any unit you need, and move some of that to macros (maybe even passing the unit as a parameter to a generic macro) to clean up the clutter.

[dominiquevocat]

ok, so (for now) i do this:

index="jira_summary" project="xxxxx" status!="Closed" aggregatetimespent=* | eval age=aggregatetimespent | eval minutes = if(match(age, "minute"), replace(age, ".?(\d+)\sminute.", "\1"), 0) | eval hours = if(match(age, "hour"), replace(age, ".?(\d+)\shour.", "\1"), 0) | eval days = if(match(age, "day"), replace(age, ".?(\d+)\s*day.", "\1"), 0) | eval age_seconds = minutes*60 + hours*3600 + days*86400 | sort age_seconds | eval timespent=tostring(age_seconds,"duration")| table title,assignee,timespent

the "timespent" is not sortable so i use the temp field.