Splunk Search

## Subsearch to sum two fields by a common field

Path Finder

Having a hard time trying to put 3 searches together to sum both search counts by PO. Please see below.

First/Second searches, will provide a PO column and Count.

Third search will also provide a PO column and Count.

The output expected would be:
006341023564 9
011561023548 9
011971023544 9
011971023552 9

Tags (4)
1 Solution
Champion

Here is one way to do it:

Edit the first search to change `stats count(JOB_ID) AS Count` to become `stats count(JOB_ID) AS Job_Count` and likewise change the second from `stats count(REQUEST) AS Count` to become `stats count(REQUEST) AS Request_Count`.

Then, join the two searches together by wrapping the second search in square brackets and appending to the end of the first search: `| join type=left PO_Ready`. And finally sum up the Count values by adding this to the end: `| eval Count=Job_Count + Request_Count | fields PO_Ready Count`

So it all winds up looking like `modified first search | join type=left PO_Ready [ modified second search ] | eval Count=Job_Count + Request_Count | fields PO_Ready Count`

Champion

Here is one way to do it:

Edit the first search to change `stats count(JOB_ID) AS Count` to become `stats count(JOB_ID) AS Job_Count` and likewise change the second from `stats count(REQUEST) AS Count` to become `stats count(REQUEST) AS Request_Count`.

Then, join the two searches together by wrapping the second search in square brackets and appending to the end of the first search: `| join type=left PO_Ready`. And finally sum up the Count values by adding this to the end: `| eval Count=Job_Count + Request_Count | fields PO_Ready Count`

So it all winds up looking like `modified first search | join type=left PO_Ready [ modified second search ] | eval Count=Job_Count + Request_Count | fields PO_Ready Count`

Champion

It's also quite possible that you could achieve this same goal faster by combining the two searches into a single base search, but they are sufficiently complex that I'm not confident I could merge them together without actually testing on your data.

Path Finder

Thank you so much. I was able to get the count together that way. Now its pulling that data faster with the inner left. Also thanks for the advice, will try to create just one search instead of 3 to see if runs faster.

Get Updates on the Splunk Community!

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...