Splunk Search

Calculate Seconds that are over 60 minutes, to Days, Hours, Minutes, Seconds

Xe03kfp
Path Finder

I have an issue with calculating seconds that go over 60 minutes that sums to be a few days.

One of my eval calculations sums to be 496089.166322 seconds and if I use
|fieldformat "Total Time"=strftime('Total Time', "%M:%S") I get 48:09 as the sum but should calculate to 5 days, 17 hours, 48 minutes and 9 seconds

I am not sure if I have to use a macro to do the job? LINK

Or missing something obvious?

I have searched through every variation of this and have tried all the common date and time format variables with strftime( converts epoch time to format Y )

Here is my current search string where I have to break down the Days Hours Minutes and Seconds along with a ScreenCapture

Search String:

index="snort"
( 2222222 dest_port="") OR (1111111 src_port="") OR ( 1111111 src_ip="") OR (2222222 dest_ip="")
| eval disconnect_time=if(match(_raw,"2222222"),_time,null())
| eval connect_time=if(match(_raw,"1111111"),_time,null())
| eval Ephemeral=if(isnotnull(disconnect_time),dest_port,Ephemeral)
| eval Ephemeral=if(isnotnull(connect_time),src_port,Ephemeral)
| stats min(connect_time) as Connect max(disconnect_time) as Disconnect min(src_ip) as "Source IP" by Ephemeral
| eval Seconds=Disconnect-Connect
| fieldformat "Seconds"=strftime('Seconds', "%s")
| eval Minutes=Seconds/60 | eval Hours=Minutes/60
| eval Days=Hours/24
| convert timeformat="%a %b-%d %Y "at" %H:%M:%S" ctime(Connect) ctime(Disconnect)
| search Connect=* Disconnect=*
| rename Ephemeral as "Connection Port", Total_time as "lala"

Tags (3)
0 Karma
1 Solution

jaraneda
Engager

Hello,
I think that you have to use "tostring" on the eval command

| eval "Total Time"=tostring(Seconds,"duration")

The result of that command is 5+17:48:09.166322
where "5+" is the number of days.

I hope this help you 🙂

View solution in original post

jaraneda
Engager

Hello,
I think that you have to use "tostring" on the eval command

| eval "Total Time"=tostring(Seconds,"duration")

The result of that command is 5+17:48:09.166322
where "5+" is the number of days.

I hope this help you 🙂

Oisin77
Explorer

Can you get the amount of days on its own?

0 Karma

Xe03kfp
Path Finder

I found addcoltotals gives me a total in seconds for the field I specify, then I will have to convert the seconds.

0 Karma

Xe03kfp
Path Finder

Yes that worked! To make it pretty..is there a way to take away the miliseconds? Also, how would I sum the "Total Seonds" as a "Total Time" like: | transpose | "Total Time" string --so the total time shows left justified?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...