Splunk Search

How do I get the sum of timestamps in a column?

Explorer

Hi,

I am unable to add two timestamps in a column using | addcoltotals or | stats.
Can you please help me with this? I want the sum of this column as a separate field.

elapsed_time
01:14:50.2257196
17:26:56.5245445

When I try the | addcoltotals it works for other columns with integer values, but not for this column.

addcoltotals labelfield=Elapsed_Time label="elap_time"

stats sum(elapsed_time) as elapsed_time
0 Karma
1 Solution

SplunkTrust
SplunkTrust

That is because arithmetic function such as sum will not work on string and your elapsed_time is string. What you need to do is to convert this into no of seconds (integer) and do your sum. You can convert it back to string after the sum if you'd like. Try something like this (run anywhere example, initial command before addcoltotals are to generate data)

| gentimes start=-1 | eval elapsed_time="01:14:50.2257196 17:26:56.5245445" | table elapsed_time | makemv elapsed_time | mvexpand elapsed_time | eval elapsed_time=strptime(elapsed_time,"%H:%M:%S.%N")-relative_time(now(),"@d") | addcoltotals |  eval elapsed_time1=strftime(elapsed_time,"%H:%M:%S.%N") | eval elapsed_time2=tostring(elapsed_time,"duration")

View solution in original post

SplunkTrust
SplunkTrust

That is because arithmetic function such as sum will not work on string and your elapsed_time is string. What you need to do is to convert this into no of seconds (integer) and do your sum. You can convert it back to string after the sum if you'd like. Try something like this (run anywhere example, initial command before addcoltotals are to generate data)

| gentimes start=-1 | eval elapsed_time="01:14:50.2257196 17:26:56.5245445" | table elapsed_time | makemv elapsed_time | mvexpand elapsed_time | eval elapsed_time=strptime(elapsed_time,"%H:%M:%S.%N")-relative_time(now(),"@d") | addcoltotals |  eval elapsed_time1=strftime(elapsed_time,"%H:%M:%S.%N") | eval elapsed_time2=tostring(elapsed_time,"duration")

View solution in original post

Explorer

Thanks for the reply.
However the elapsed time is not static one and it was generated as another search result.
when I run the above before index it says eval is malfunctioned.
Any ideas?

0 Karma

Explorer

It worked now without the gentimes now. Thanks much for help

0 Karma

SplunkTrust
SplunkTrust

The above search works fine for me. You need to replace this portion of above search with your search which give field elapsed_time

 | gentimes start=-1 | eval elapsed_time="01:14:50.2257196 17:26:56.5245445" | table elapsed_time | makemv elapsed_time | mvexpand elapsed_time
0 Karma