Splunk Search
Highlighted

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

Contributor

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
Highlighted

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

SplunkTrust
SplunkTrust

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.

View solution in original post

Highlighted

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

Motivator

Try this,

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

Thanks,
V

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

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

Contributor

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

0 Karma
Highlighted

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

Motivator

Perfect.. 🙂

0 Karma