Splunk Search

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

lyndac
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
1 Solution

sideview
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

vasanthmss
Motivator

Try this,

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

Thanks,
V

V
0 Karma

sideview
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

lyndac
Contributor

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

0 Karma

vasanthmss
Motivator

Perfect.. 🙂

V
0 Karma

sideview
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.

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...