topic Re: How to sum the count of two fields when they have the same value? in Splunk Search

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

lyndac — Thu, 14 Apr 2016 16:01:03 GMT

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

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

sideview — Thu, 14 Apr 2016 16:06:24 GMT

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.

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

vasanthmss — Thu, 14 Apr 2016 16:14:36 GMT

Try this,

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

Thanks,
V

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

sideview — Thu, 14 Apr 2016 16:56:43 GMT

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.

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

lyndac — Thu, 14 Apr 2016 16:59:27 GMT

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

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

vasanthmss — Thu, 14 Apr 2016 17:52:29 GMT

Perfect.. 🙂