Subsearch to sum two fields by a common field

[rsokolova]

Thanks in advance,

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:
PO_Ready Count
006341023564 9
011561023548 9
011971023544 9
011971023552 9

[elliotproebstel · Accepted solution]

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

[elliotproebstel]

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.

[rsokolova]

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.