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
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.
index=foo | eval country=coalesce(destCity, sourceCity) | stats count by country
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.
I found that when I tried it. Most of my events have both fields. The mvappend worked beautifully!