topic Re: Need a little help converting seconds to days, hours, minutes in Splunk Search

Need a little help converting seconds to days, hours, minutes

gabarrygowin — Mon, 09 Oct 2017 20:25:34 GMT

Hi all,

Windows reports everything in really long seconds uptime fields. I want to convert that to days, hours, minutes. Trying to get syntax provided in another post to work (and think I'm close) but now receiving "eval" command: Regex unmatched closing parens message that I can't seem to find.

Here's my search:

index=windows sourcetype=WinEventLog* host!="*.xx.com" EventCode=6013 | rex "(?\d+)\ seconds.$" | convert rmunit(secs) as numSecs | eval stringSec=tostring(numSecs,"duration") | eval stringSecs=replace(stringSecs,"(\d+):(\d+)(\d+)","\1h \2min \3s") | stats avg(duration) AS "Windows AVG Uptime"

Here's a sample of data:

10/9/17
12:01:44.000 PM
10/09/2017 12:01:44 PM
LogName=System
SourceName=EventLog
EventCode=6013
EventType=4
Type=Information
ComputerName=TORBSIVWD01.xx.com
TaskCategory=The operation completed successfully.
OpCode=None
RecordNumber=885007
Keywords=Classic
Message=The system uptime is 2132870 seconds.

All help MUCH appreciated!

Barry

Re: Need a little help converting seconds to days, hours, minutes

sbbadri — Mon, 09 Oct 2017 20:46:48 GMT

@gabarrygowin

try this,

index=windows sourcetype=WinEventLog* host!="*.xx.com" EventCode=6013 
| rex field=Message "The system uptime is\s+(?P<secs>\d+)\s+seconds" 
| convert rmunit(secs) as numSecs  
| eval stringSecs=tostring(numSecs,"duration")
| eval stringSecs = replace(stringSecs,"(\d+)\:(\d+)\:(\d+)","\1h \2min \3s") 
| stats avg(duration) AS "Windows AVG Uptime"

Re: Need a little help converting seconds to days, hours, minutes

gabarrygowin — Mon, 09 Oct 2017 20:52:51 GMT

Thanks much for the replay. I like your rex much more. The search completes now, but with now data going to Statistics.

Re: Need a little help converting seconds to days, hours, minutes

gabarrygowin — Mon, 09 Oct 2017 21:15:48 GMT

To clarify, the search is executing now, but "No results found". I know there are events there that match what we're trying to pull.

Re: Need a little help converting seconds to days, hours, minutes

sbbadri — Tue, 10 Oct 2017 13:36:35 GMT

try this
index=wineventlog host!=xxx.com EventCode=6013 
| rex field=Message "The system uptime is\s+(?P<secs>\d+)\s+seconds" 
| stats avg(secs) AS secs by host 
| convert rmunit(secs) as numSecs 
| eval stringSecs=tostring(numSecs,"duration") 
| eval stringSecs = replace(stringSecs,"(\d+)\:(\d+)\:(\d+)","\1h \2min \3s") 
| fields - secs numSecs 
| rename stringSecs as "Windows AVG Uptime"

Re: Need a little help converting seconds to days, hours, minutes

gabarrygowin — Tue, 10 Oct 2017 16:54:40 GMT

I greatly appreciate the effort you're putting in and apologize for the difficulties.

That recent code got me:
Windows AVG Uptime distinct_count(host)
11+18h 13min 13s.500000 1
6+04h 03min 59s.000000 1
6+06h 45min 55s.000000

I'm looking more for a simple counter that display the average uptime in days,hour,min for all our Windows hosts. May be I didn't paint the requirement correctly?

Re: Need a little help converting seconds to days, hours, minutes

gabarrygowin — Tue, 10 Oct 2017 18:08:50 GMT

Hmm, with your help, I think I'm really close now.

Search:
index=windows host!=*.xx.com EventCode=6013 | rex field=Message "The system uptime is\s+(?P\d+)\s+seconds" | eval secs=secs/86400 | stats avg(secs) as "Windows AVG Uptime"

Results;
Windows AVG Uptime
23.887137

Conclusion: "IF" the functions are working correctly, our average time up is 23.8 days.

Re: Need a little help converting seconds to days, hours, minutes

niketn — Wed, 11 Oct 2017 10:42:34 GMT

[UPDATED]
Following handles both scenarios where HH:MM:SS is within a Day or more than a day

| makeresults
| eval secs=678393
| eval strSecs=tostring(secs,"duration")
| eval strFormatSecs=if(match(strSecs,"\+"),replace(strSecs,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 Day \2 Hr \3 Min \4 Secs"),replace(strSecs,"(\d+)\:(\d+)\:(\d+)","\1 Hr \2 Min \3 Secs"))
| table secs strSecs strFormatSecs

Try with differen values of secs like 3600, 54000, 86400 etc.

Following is a run any where search based on answer by @lguinn [Splunk] : https://answers.splunk.com/answers/61652/convert-seconds-into-hours-minutes-and-seconds.html#answer-338759

Only difference is that you have days in your time as well which is represented as day+hour:minute:second format on converting tostring() as duration. So you would need to replace day+ as well:

| makeresults
| eval secs=678393
| eval strSecs=tostring(secs,"duration")
| eval strFormatSecs=replace(strSecs,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 days \2 Hr \3 Min \4 Secs")

strSecs in second eval will result in 7+20:26:33 and strFormatSec will evaluate after replace as 7 days 20 Hr 26 Min 33 Sec
Needless to say this can also be done via sed regular expression along rex to give more control compared to final eval with replace() command. @cpetterborg over to you 🙂

Re: Need a little help converting seconds to days, hours, minutes

cpetterborg — Wed, 11 Oct 2017 17:53:11 GMT

[UPDATED to allow for shorter time periods]

This should be faster:

| makeresults
 | eval secs=678393
 | eval strSecs=tostring(secs,"duration")
 | rex field=strSecs mode=sed "s/((((\d*)\+)?(\d*):)?(\d*):)?(\d*)/\4 days \5 hrs \6 min \7 sec/"

The original would only work well if there were all 4 values. I updated the rex to do shorter time periods (even down to less than 1 minute). It isn't a complete result with a result having 0 days, only days, so it isn't perfect, but it is better than the original. With an additional rex, it can fix that:

| makeresults
| eval secs=67839
| eval strSecs=tostring(secs,"duration")
| rex field=strSecs mode=sed "s/((((\d*)\+)?(\d*):)?(\d*):)?(\d*)/\4 days \5 hrs \6 min \7 sec/"
| rex field=strSecs mode=sed "s/^ days/0 days/"

Re: Need a little help converting seconds to days, hours, minutes

gabarrygowin — Wed, 11 Oct 2017 18:00:00 GMT

Hmm, fun. I'm lost on where to insert this into my existing search as makeresults seems to want to be first in line before I even tell it what index to look at.

Re: Need a little help converting seconds to days, hours, minutes

cpetterborg — Wed, 11 Oct 2017 19:15:53 GMT

This is somewhat incomplete, but you would put this in your search like this:

index=windows sourcetype=WinEventLog* host!="*.xx.com" EventCode=6013 
| rex field=Message "The system uptime is\s+(?P<secs>\d+)\s+seconds" 
| eval strSecs=tostring(secs,"duration")
| rex field=strSecs mode=sed "s/(\d*)\+(\d*):(\d*):(\d*)/\1 day \2 hrs \3 min \4 sec/"

Re: Need a little help converting seconds to days, hours, minutes

niketn — Thu, 12 Oct 2017 16:14:30 GMT

@gabarrygowin, community members use makeresults to mimic dummy data. You need to plug in your actual query. It should look like the following.

index=windows host!=*.xx.com EventCode=6013 
| rex field=Message "The system uptime is\s+(?P<secs>\d+)\s+seconds"
| eval secs=tostring(secs,"duration")
| eval secs=if(match(secs,"\+"),replace(secs,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 Day \2 Hr \3 Min \4 Secs"),replace(secs,"(\d+)\:(\d+)\:(\d+)","\1 Hr \2 Min \3 Secs"))
| table secs

Re: Need a little help converting seconds to days, hours, minutes

gabarrygowin — Thu, 12 Oct 2017 21:30:16 GMT

Error in 'eval' command: The expression is malformed. Expected ).
The search job has failed due to an error. You may be able view the job in the Job Inspector.

Re: Need a little help converting seconds to days, hours, minutes

cpetterborg — Thu, 12 Oct 2017 23:59:46 GMT

looks like it just needs one more parenthesis. I can't try this out completely because I don't have your data so the base search doesn't work, but it should fix the eval problem:

 index=windows host!=*.xx.com EventCode=6013 
 | rex field=Message "The system uptime is\s+(?P<secs>\d+)\s+seconds"
 | eval secs=tostring(secs,"duration")
 | eval secs=if(match(secs,"\+"),replace(secs,"(\d+)\+(\d+)\:(\d+)\:(\d+)","\1 Day \2 Hr \3 Min \4 Secs"),replace(secs,"(\d+)\:(\d+)\:(\d+)","\1 Hr \2 Min \3 Secs"))
 | table secs

Re: Need a little help converting seconds to days, hours, minutes

niketn — Sun, 15 Oct 2017 04:03:43 GMT

@gabarrygowin, sorry I had missed out closing parenthesis in both Run Anywhere example and the updated query above. I have updated the same please try again.

@cpetterborg, thanks again for helping out 🙂