topic Re: Time conversion from milliseconds to break down to days hours minutes seconds in Splunk Search

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

reneedeleon — Wed, 09 Oct 2019 17:14:41 GMT

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

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

skoelpin — Wed, 09 Oct 2019 17:55:21 GMT

So what's your question?

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

gcusello — Thu, 10 Oct 2019 07:23:27 GMT

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

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

reneedeleon — Thu, 10 Oct 2019 12:22:27 GMT

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

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

gcusello — Thu, 10 Oct 2019 12:27:47 GMT

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

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

reneedeleon — Thu, 10 Oct 2019 12:48:35 GMT

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.

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

gcusello — Thu, 10 Oct 2019 14:07:08 GMT

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

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

reneedeleon — Thu, 10 Oct 2019 16:37:45 GMT

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.

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

jacobpevans — Thu, 10 Oct 2019 20:04:14 GMT

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

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

gcusello — Fri, 11 Oct 2019 07:08:19 GMT

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

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

reneedeleon — Fri, 11 Oct 2019 11:41:57 GMT

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.

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

reneedeleon — Fri, 11 Oct 2019 11:44:41 GMT

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.

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

woodcock — Sun, 13 Oct 2019 08:54:58 GMT

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")

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

reneedeleon — Tue, 15 Oct 2019 13:10:51 GMT

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

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

woodcock — Tue, 15 Oct 2019 15:47:36 GMT

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

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

reneedeleon — Wed, 16 Oct 2019 16:02:09 GMT

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

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

gcusello — Wed, 16 Oct 2019 16:14:03 GMT

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

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

woodcock — Wed, 16 Oct 2019 20:11:37 GMT

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

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

reneedeleon — Mon, 21 Oct 2019 12:47:43 GMT

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.

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

reneedeleon — Mon, 21 Oct 2019 12:48:34 GMT

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