topic Re: Sum values fields in Splunk Search

Sum values fields

Miguel3393 — Fri, 06 Dec 2024 18:30:18 GMT

How can I get the total sum of the Duration fields?

Regards.

Re: Sum values fields

PickleRick — Fri, 06 Dec 2024 19:06:35 GMT

1. Please don't post screenshots. Copy-paste your search and results you're getting so far as text (preferably using preformatted paragraph style or a code block). It's much easier for everyone involved

2. You can't sum string values so you have to calculate sum before converting your duration field to string (which you're doing... strangely; you should rather use convert() function, not strftime). And even better - don't use eval to make the duration field a string, use fieldformat.

Re: Sum values fields

Miguel3393 — Fri, 06 Dec 2024 19:32:07 GMT

Thanks. I paste the script and result

index="cdr" | search "Call.TermParty.TrunkGroup.TrunkGroupId"="2811" OR
"Call.TermParty.TrunkGroup.TrunkGroupId"="2810"
"Call.ConnectTime"=*
"Call.DisconnectTime"=*
|lookup Pais Call.RoutingInfo.DestAddr OUTPUT Countrie
| eval Disctime=strftime('Call.DisconnectTime'/1000,"%m/%d/%Y %H:%M:%S %Q")
| eval Conntime=strftime('Call.ConnectTime'/1000, "%m/%d/%Y %H:%M:%S%Q")
| eval diffTime=('Call.DisconnectTime'-'Call.ConnectTime')
| eval Duracion=strftime(diffTime/1000, "%M:%S") | table Countrie, Duracion

Countrie Duracion
Chile 01:17
Hong Kong 00:02
Denmark 02:01
Denmark 00:51
Denmark 00:51
Denmark 06:30
China 02:59
Uruguay 00:18

Re: Sum values fields

Miguel3393 — Fri, 06 Dec 2024 19:37:50 GMT

Thanks. I paste the script and result

Countrie Duracion
Chile 01:17
Hong Kong 00:02
Denmark 02:01
Denmark 00:51
Denmark 00:51
Denmark 06:30
China 02:59
Uruguay 00:18

Re: Sum values fields

ITWhisperer — Fri, 06 Dec 2024 19:57:52 GMT

Use stats to sum your diffTime values, then use fieldformat with tostring and the "duration" argument to display the value as a string

Re: Sum values fields

richgalloway — Fri, 06 Dec 2024 20:48:49 GMT

Building on previous answers, use fieldformat to display Duracion in the desired way while still keeping it as a number. Then sum their values using addcoltotals.

index="cdr" | search "Call.TermParty.TrunkGroup.TrunkGroupId"="2811" OR "Call.TermParty.TrunkGroup.TrunkGroupId"="2810" "Call.ConnectTime"=* "Call.DisconnectTime"=* |lookup Pais Call.RoutingInfo.DestAddr OUTPUT Countrie | eval Disctime=strftime('Call.DisconnectTime'/1000,"%m/%d/%Y %H:%M:%S %Q") | eval Conntime=strftime('Call.ConnectTime'/1000, "%m/%d/%Y %H:%M:%S%Q") | eval diffTime=('Call.DisconnectTime'-'Call.ConnectTime') | fieldformat Duracion=strftime(diffTime/1000, "%M:%S") | table Countrie, Duracion | addcoltotals label="Total" labelfield=Countrie Duracion

Re: Sum values fields

Miguel3393 — Mon, 16 Dec 2024 23:42:50 GMT

Sorry for the delay, thanks for the response.
Does not show duration information.

Countrie Duracion

Uruguay
Uruguay
Uruguay
Uruguay
Denmark
China
Chile
Spain
Uruguay
Spain
Spain
Spain
Uruguay
Spain
Spain
Uruguay
Spain

Re: Sum values fields

bowesmana — Tue, 17 Dec 2024 00:25:27 GMT

@PickleRick wrote:
2. ... you should rather use convert() function, not strftime). ...

Out of interest - why? I much prefer strftime - it can be used with eval/fieldformat. convert cannot be used with fieldformat either.

Re: Sum values fields

PickleRick — Tue, 17 Dec 2024 06:30:02 GMT

For duration?

I'm all for strftime for formatting points in time. But for longer durations you'll get strange results (duration of year 1971?). Also timezone settings can wreak havoc with accuracy of the results.