tostring not totaling durations in columns that have 0 for totals.

[ericdelacruz]

For example, I have 2 columns that I am totaling their seconds into a 3rd. However, if one of the columns has 0 as the duration, the 3rd column with totals does not show any output at all. In my search I have the following where I am totaling the 2 columns (inbound_duration and outbound_duration). When both my columns have data it totals correctly. Any ideas what I could be missing here?

| eval total_duration = tostring(inbound_duration + outbound_duration,"duration")

[martin_mueller · Accepted solution]

You could either use the fillnull command prior to the eval to put zeroes in place of nulls, or use coalesce(field, 0) in the calculation to use a zero if the field value is null.

[martin_mueller]

Leave off the value="NULL", else it'll write "NULL" into your fields. Zero is the default.

[ericdelacruz]

That worked thanks!

[ericdelacruz]

I tried doing that, but still not working. I am a newbie so I think I am doing this completely wrong. The lines in question start 5th from the bottom. Below is the whole search query.

cdr_events NOT callingPartyGroup=Sales NOT finalCalledPartyGroup=Sales
| eval name=coalesce(callingPartyName, finalCalledPartyName)
| eval group=coalesce(callingPartyGroup,finalCalledPartyGroup)
| eval subgroup=coalesce(callingPartySubgroup, finalCalledPartySubgroup)
| get_call_id
| fillnull group subgroup value="NULL"
| eval nameGroupSubgroup=name + "---" + group + "---" + subgroup
| chart sum(duration) as seconds dc(callId) as calls over nameGroupSubgroup by type
| eval nameGroupSubgroup=split(nameGroupSubgroup,"---")
| eval name=mvindex(nameGroupSubgroup,0)
| eval group=mvindex(nameGroupSubgroup,1)
| eval subgroup=mvindex(nameGroupSubgroup,2)
| rename "seconds: incoming" as inbound_duration "seconds: outgoing" as outbound_duration "calls: incoming" as "inbound_calls" "calls: outgoing" as "outbound_calls"
| fields name group subgroup inbound_calls inbound_duration outbound_calls outbound_duration
| fillnull inbound_duration outbound_duration value="NULL"
| eval total_duration = tostring(inbound_duration + outbound_duration,"duration")
| eval total_calls = inbound_calls + outbound_calls
| eval inbound_duration= tostring(inbound_duration, "duration")
| eval outbound_duration= tostring(outbound_duration, "duration")