How to sum the count of two fields when they have the same value?

[lyndac]

I have data where each event has two fields to show the source and destination city of a package.I can get the count of each city separately, but am struggling to show a combined count.

index=foo | stats count by destCity -- returns a count of events for each destination city.

destCity             count
Baltimore            5
Philli               3
Toronto             20

index=foo | stats count by sourceCity -- returns the count of events for each source city.

sourceCity       count
Atlanta                7
Baltimore            5
Toronto            15

I need to be able to calculate:

country         count
Atlanta              7
Baltimore       10
Philli                  3
Toronto         35

[vasanthmss]

Try this,

index=foo | eval country=coalesce(destCity, sourceCity) | stats count by country

Thanks,
V

[sideview]

Note this will not work properly whenever an event has both destCity and sourceCity fields - in such cases it will disregard that event's sourceCity field.

[lyndac]

I found that when I tried it. Most of my events have both fields. The mvappend worked beautifully!

[vasanthmss]

Perfect.. 🙂

[sideview · Accepted solution]

I think this is as simple as

| eval city=mvappend(sourceCity,destCity) | stats count by city

Whether a given event has both fields, or has just one or the other, it will still work.