Why is the conversion of duration from seconds to hours in a search automatically adding 18 hours to the value?

[Splunkster45]

I'm looking to change the format of the useful duration tool from seconds to hours. I found out how to do this via someone else's question: http://answers.splunk.com/answers/72687/time-convertion-of-big-amount-of-seconds.html

When I run the command:

... | convert timeformat="%H:%M:%S" ctime("duration")

the duration value of 1,10,102 and 11107 are changed to 18:00:01, 18:00:10, 18:01:42, 21:05:07, respectively. The differences between the time expressed in the HH:MM:SS format and 18:00:00 gives the correct individual duration value, however this duration value is being added to 18:00:00. How can I changed the converted time to start at 00:00:000 instead of 18:00:00?

Thanks!

[acharlieh · Accepted solution]

So I think the issue is that convert is taking into consideration your locale for displaying what is being interpreted as seconds since January 1 1970, Midnight UTC.

You likely want to use the eval with the tostring function as mentioned on the answer of your linked question. Compare the differences below:

| noop | stats count | fields 
| eval seconds=split("1;10;102;11107;999999",";") | mvexpand seconds
| eval durations=tostring(seconds,"duration")
| convert timeformat="%H:%M:%S" ctime(seconds) as converts

[Splunkster45]

Of course! Thanks

[acharlieh]

In your search your number of seconds value is in a field called duration, mine was in a field called seconds. So you want |eval newfieldname = tostring(duration,"duration") The first argument being the field to convert to a duration string, the second argument is the type of string conversion being performed. Check out info on the tostring function: http://docs.splunk.com/Documentation/Splunk/6.1.4/SearchReference/CommonEvalFunctions

Also be careful not to throw out the field when you want it, you may consider making the conversion later in your search (the stats command as currently written would throw away your durations field)

[Splunkster45]

Thanks for that piece of code. The durations column is exactly what I'm looking for! However, in trying to apply this to my code, I ran into some issues. The full code that I'm using is

"Session" | rex field=_raw "Session (?<number>DOUBLEBACKSLASHw+) (DOUBLEBACKSLASH((?<username>DOUBLEBACKSLASHw+)@|)" | transaction number startswith "started" endswith "ended" | where duration > 0   | eval durations = tostring(seconds,"duration") | stats count by username, number, duration |  fields - count |  sort dy durations desc

I have never been able to get the tostring function to work. For one, I don't have a defined 'seconds' field. I only have the field duration created by the transaction argument. In your example, both 'seconds' and 'duration' were inputs to tostring, however I only have the durations field. Neither tostring(duration) or tostring("duration") work. Any Ideas?