Splunk Search

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

reneedeleon
Engager

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
0 Karma

skoelpin
SplunkTrust
SplunkTrust

So what's your question?

0 Karma

reneedeleon
Engager

how do I get this query to show what I have tabled and a breakdown of the total time down broken down by days, hours, minutes, seconds.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...