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.

Take the 2021 Splunk Career Survey

### Help us learn about how Splunk has impacted your career by taking the 2021 Splunk Career Survey.Earn \$50 in Amazon cash! Full Details! >

Get Updates on the Splunk Community!