Sum values fields

[Miguel3393]

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

Regards.

[ITWhisperer]

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

[Miguel3393]

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

[PickleRick]

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.

[bowesmana]

---

@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.

[PickleRick]

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.

[Miguel3393]

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

[richgalloway]

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

[Miguel3393]

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