Time conversion from milliseconds to break down to days hours minutes seconds

[reneedeleon]

I have been working on a search that gives a duration breakdown.

I am trying to achieve:

thehost  theip          c_time                     clean_date            tap                    whocares                     diff
vex          127.0.0.1  10/9/2019 10:02    7/2/2018 4:59    1.0.0.127   recluse                          2s  
hive             127.0.0.2  10/10/2019 10:02       7/3/2018 4:59    1.0.0.128   lunas howl                   13s  
fallen   127.0.0.3  10/11/2019 10:02       7/4/2018 4:59    1.0.0.129   not forgotten            2m 24s  
cabal    127.0.0.4  10/12/2019 10:02       7/5/2018 4:59    1.0.0.130   wendigo                          28m 48s  
scorn    127.0.0.5  10/13/2019 10:02       7/6/2018 4:59    1.0.0.131   redricks broadsword  5h 45m 36s  
taken    127.0.0.6  10/14/2019 10:02       7/7/2018 4:59    1.0.0.132   randys throwing knife    2d 21h 7m 12s  
guardian 127.0.0.7  10/15/2019 10:02       7/8/2018 4:59    1.0.0.133   thousand voices          34d 13h 26m 24s  

What I am getting:

thehost theip            c_time                   clean_date             tap                    whocares                     diff
vex          127.0.0.1  10/9/2019 10:02  7/2/2018 4:59  1.0.0.127   recluse                          11140.66139
hive             127.0.0.2  10/10/2019 10:02     7/3/2018 4:59  1.0.0.128   lunas howl                   9926.056944
fallen   127.0.0.3  10/11/2019 10:02     7/4/2018 4:59  1.0.0.129   not forgotten            101.1680556
cabal    127.0.0.4  10/12/2019 10:02     7/5/2018 4:59  1.0.0.130   wendigo                          3995.172778
scorn    127.0.0.5  10/13/2019 10:02     7/6/2018 4:59  1.0.0.131   redricks broadsword  767.7697222
taken    127.0.0.6  10/14/2019 10:02     7/7/2018 4:59  1.0.0.132   randys throwing knife    2113.435278
guardian 127.0.0.7  10/15/2019 10:02     7/8/2018 4:59  1.0.0.133   thousand voices          4890.476667

I am getting a final tally of total hour down. Below is the search I am using. Any help would be appreciated.

1. index=net*  Inactive NIC
2. | rex field=_raw "^[^ \n]* (?P[^ ]+)"
3. | rex field=_raw "^(?:[^ \n]* ){2}(?P[^ ]+)"
4. | rex field=_raw "(?\s)"
5. | rex field=_raw "[#]\d{3}(?\d\s+\w{8}\s\d+\S\d+\S\d+\s\d+\S\d+\S\d+(\S\d)?\s+(\d+\S\d+\S\d+\S\d+)?)" max_match=0
6. | rex field=_raw "[#]\d{3}(?\w+\S\w{13}\S\w{3}\s+\w{8}\s\d+\S\d+\S\d+\s\d+\S\d+\S\d+(\S\d)?\s+(\d+\S\d+\S\d+\S\d+)?)" max_match=0
7. | rex field=_raw "[#]\d{3}(?\d+\S\d+\S\d+\S\d+\S*\w*\s+\w*\s*\w{8}\s\d+\S\d+\S\d+\s\d+\S\d+\S\d+(\S\d)?\s+(\d+\S\d+\S\d+\S\d+)?)" max_match=0
8. | mvexpand Alert
9. | rex field=Alert "(?P\d)\s+(?\w{8}\s(?\d+)\S(?\d+)\S(?\d+)\s(?\d+)\S(?\d+)\S(?\d+)(\S\d)?)\s+(?\d+\S\d+\S\d+\S\d+)?"
10. | rex field=Alert "(?P\w+\S\w{13}\S\w{3})\s+(?\w{8}\s(?\d+)\S(?\d+)\S(?\d+)\s(?\d+)\S(?\d+)\S(?\d+)(\S\d)?)\s+(?\d+\S\d+\S\d+\S\d+)?"
11. | rex field=Alert "(?P\d+\S\d+\S\d+\S\d+)\S*\w*\s+\w*\s*(?\w{8}\s(?\d+)\S(?\d+)\S(?\d+)\s(?\d+)\S(?\d+)\S(?\d+)(\S\d)?)\s+(?\d+\S\d+\S\d+\S\d+)?"
12. | lookup dnslookup clientip as theip OUTPUT clienthost as whocares
13. | eval whocares=case(isnull(whocares),"UNKNOWN",1=1,whocares)
14. | eval clean_date=trim(date_down,"Inactive ")
15. | convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS c_time
16. | eval event=strptime(c_time, "%Y-%m-%d %H:%M:%S")
17. | eval tap = strptime(clean_date, "%Y-%m-%d %H:%M:%S")
18. | eval diff=(event - tap), diff=(diff / 60 / 60)
19. | convert rmunit(diff) as numSecs
20. | eval stringSecs=tostring(numSecs, "duration")
21. | eval stringSecs=replace(stringSecs, "(?:(\d+)\+)?0?(\d+):0?(\d+):0?(\d+)","\1d \2h \3m \4s")
22. | eval stringSecs=replace(diff, "^d (0h (0m )?)?","") 
23. | eval duration=strftime(diff,"%Y-%m-%d %H:%M:%S")
24. | table thehost, theip, c_time, clean_date, stap, whocares, diff

[woodcock]

Like this:

| makeresults 
| eval YourFieldNameHere = "9999.999"
| fieldformat YourFieldNameHere = replace(replace(replace(replace(tostring(1000 * YourFieldNameHere, "duration"), "\+", " days, "), "(\d+):(\d+:\d+)$", "\1 hours, \2"), "(\d+):(\d+)$", "\1 minutes, \2"), "$", " seconds")

[reneedeleon]

That gave the following:
187+03:05:08.611111
how do I translate that to English?

[woodcock]

That is 187 days, 3 hours, 5 minutes, 8 seconds, etc. It is a standard duration format.

[reneedeleon]

Would you happen to know the regex that spells it all out like you did?

[woodcock]

See updated answer. Don't forget to UpVote and click Accept to close the question.

[reneedeleon]

Thank you Sir it worked. Now the problem is the math or I did not place the string in the correct order. My results are all coming up as the same value.

[woodcock]

The solution is to take this line and add it to your existing search:

| fieldformat YourFieldNameHere = replace(replace(replace(replace(tostring(1000 * YourFieldNameHere, "duration"), "\+", " days, "), "(\d+):(\d+:\d+)$", "\1 hours, \2"), "(\d+):(\d+)$", "\1 minutes, \2"), "$", " seconds")

HOWEVER, you have to replace YourFieldNameHere with the actual name of your field. That is all there is to it. It works. Be sure to come back and click Accept to close the question.

[jacobpevans]

Greetings @reneedeleon,

Check out the built-in dmc_convert_runtime(1) macro. It's in the Monitoring Console app. This is its definition:

case( 
round($runtime$ / (3600*24) - 0.5) > 0,
round($runtime$ / (3600*24) - 0.5)."d ".round(($runtime$ % (3600*24)) / 3600 - 0.5)."h ".round(($runtime$ % 3600) / 60 - 0.5)."min ".round($runtime$ % 60, 2)."s", 
round(($runtime$ % (3600*24)) / 3600 - 0.5) > 0, round(($runtime$ % (3600*24)) / 3600 - 0.5)."h ".round(($runtime$ % 3600) / 60 - 0.5)."min ".round($runtime$ % 60, 2)."s", 
round(($runtime$ % 3600) / 60 - 0.5) > 0, round(($runtime$ % 3600) / 60 - 0.5)."min ".round($runtime$ % 60, 2)."s", 
1 = 1, round($runtime$ % 60, 2)."s" 
)

For an example of it in use, check out the first panel here:
https://192.168.0.1:8000/en-US/app/splunk_monitoring_console/search_usage_statistics_instance

Cheers,
Jacob

[reneedeleon]

Thank you Jacob, but I don't have access to that part of the app. I only get to play with the search and reporting.

[gcusello]

Hi reneedeleon,
let me understand: do you want a sum of all diffs?
if this is your need you can use the addcoltotals command (see at https://docs.splunk.com/Documentation/Splunk/7.3.2/SearchReference/Addcoltotals ) :

...
| addcoltotals labelfield=diff label="Total diffs"

Bye.
Giuseppe

[reneedeleon]

Yes Sir, I am trying to get a total breakdown in the diff field: 34d 13h 26m 24s

[gcusello]

Hi reneedeleon,
the hint to use addcoltotals is sufficient for you or you need also an help for conversion?
Bye.
Giuseppe

[reneedeleon]

I need help with the conversion since it's a line by line total. Believe it or not most to the query was built utilizing your and dal's responses to many questions.

[gcusello]

Hi
before the table command put these lines

| rex field=diff "(?<hour>\d+)h"
| rex field=diff "(?<min>\d+)m"
| rex field=diff "(?<sec>\d+)s"
| eval seconds=if(isnull(hour),0,hour)*3600+if(isnull(min),0,min)*60+if(isnull(sec),0,sec) 

so you have the seconds to sum
then after addcoltotals, you have remove fields that you don't need using

| addcoltotals label=seconds labelfield=Total
| eval diff=if(Total="seconds",seconds,ppp)
| fields - hour - min - sec - Total

in othe words

index=net*  Inactive NIC
| rex field=_raw "^[^ \n]* (?P[^ ]+)"
| rex field=_raw "^(?:[^ \n]* ){2}(?P[^ ]+)"
| rex field=_raw "(?\s)"
| rex field=_raw "[#]\d{3}(?\d\s+\w{8}\s\d+\S\d+\S\d+\s\d+\S\d+\S\d+(\S\d)?\s+(\d+\S\d+\S\d+\S\d+)?)" max_match=0
| rex field=_raw "[#]\d{3}(?\w+\S\w{13}\S\w{3}\s+\w{8}\s\d+\S\d+\S\d+\s\d+\S\d+\S\d+(\S\d)?\s+(\d+\S\d+\S\d+\S\d+)?)" max_match=0
| rex field=_raw "[#]\d{3}(?\d+\S\d+\S\d+\S\d+\S*\w*\s+\w*\s*\w{8}\s\d+\S\d+\S\d+\s\d+\S\d+\S\d+(\S\d)?\s+(\d+\S\d+\S\d+\S\d+)?)" max_match=0
| mvexpand Alert
| rex field=Alert "(?P\d)\s+(?\w{8}\s(?\d+)\S(?\d+)\S(?\d+)\s(?\d+)\S(?\d+)\S(?\d+)(\S\d)?)\s+(?\d+\S\d+\S\d+\S\d+)?"
| rex field=Alert "(?P\w+\S\w{13}\S\w{3})\s+(?\w{8}\s(?\d+)\S(?\d+)\S(?\d+)\s(?\d+)\S(?\d+)\S(?\d+)(\S\d)?)\s+(?\d+\S\d+\S\d+\S\d+)?"
| rex field=Alert "(?P\d+\S\d+\S\d+\S\d+)\S*\w*\s+\w*\s*(?\w{8}\s(?\d+)\S(?\d+)\S(?\d+)\s(?\d+)\S(?\d+)\S(?\d+)(\S\d)?)\s+(?\d+\S\d+\S\d+\S\d+)?"
| lookup dnslookup clientip as theip OUTPUT clienthost as whocares
| eval whocares=case(isnull(whocares),"UNKNOWN",1=1,whocares)
| eval clean_date=trim(date_down,"Inactive ")
| convert timeformat="%Y-%m-%d %H:%M:%S" ctime(_time) AS c_time
| eval event=strptime(c_time, "%Y-%m-%d %H:%M:%S")
| eval tap = strptime(clean_date, "%Y-%m-%d %H:%M:%S")
| eval diff=(event - tap), diff=(diff / 60 / 60)
| convert rmunit(diff) as numSecs
| eval stringSecs=tostring(numSecs, "duration")
| eval stringSecs=replace(stringSecs, "(?:(\d+)\+)?0?(\d+):0?(\d+):0?(\d+)","\1d \2h \3m \4s")
| eval stringSecs=replace(diff, "^d (0h (0m )?)?","") 
| eval duration=strftime(diff,"%Y-%m-%d %H:%M:%S")
| rex field=diff "(?<hour>\d+)h"
| rex field=diff "(?<min>\d+)m"
| rex field=diff "(?<sec>\d+)s"
| eval seconds=if(isnull(hour),0,hour)*3600+if(isnull(min),0,min)*60+if(isnull(sec),0,sec) 
| table thehost, theip, c_time, clean_date, stap, whocares, diff
| addcoltotals label=seconds labelfield=Total
| eval diff=if(Total="seconds",seconds,ppp)
| fields - hour - min - sec - Total

Bye.
Giuseppe

[reneedeleon]

I tried it and it gave total at the bottom of the stats table under the total field. I am trying to get a line by line breakdown by days, hours, minutes, seconds. I will play with the search you gave me and try to get it there. Thank you.

[gcusello]

If you want, you can convert again the result in hours, minutes and seconds replacing the last eval with :

| eval diff=if(Total="seconds",hour."h ".min."m ".sec."s",ppp)

Bye.
Giuseppe

[reneedeleon]

Thank you Giuseppe. I tried the new iteration but I am still getting a blank in the field column. Also now I am being asked to generate the diff field with days instead of hours.

[gcusello]

Sorry!
there was my error in copying, where you see "ppp", you have to put "diff".

| eval diff=if(Total="seconds",seconds,diff)
| eval diff=if(Total="seconds",hour."h ".min."m ".sec."s",diff)

Ciao.
Giuseppe

[reneedeleon]

Thank you, I will give this a try as well.

[gcusello]

You're welcome!
if this answer solced your problem or helped you, please accept and/or upvote it.
Ciao and see next time.

Giuseppe