Add count of subsearch results

[LittleColin]

I need to return all rows from my top search but add a count of rows from a map or subquery/subsearch.

In my system I have a number of batches which may have a number of errors that exist in a different index and I want to display a count of those errors (even if zero) alongside the batch.

Something like this pseudo query:

search index=A | fields batch_id, batch_name | count = COUNT("search index=B batch_id=$batch_id$ level=error")

This is to display on a dashboard in a stats table:

 Id     Name     Errors
 1234 | BatchA | 0
 4567 | BatchB | 6

[gcusello · Accepted solution]

Hi LittleColin,
if you have batch_name in both the indexes, you can try something like this:

index=B [ search index=A | dedup batch_id| fields batch_id ]
| stats values(batch_name) AS batch_name count AS Errors by batch_id

if instead you have batch_name only in index=A, you can try something like this:

index=B
| stats count AS Errors by batch_id
| join type=left batch_id [ search index=B | fields batch_id batch_name]
| table batch_id batch_name Errors

The second one is less performant than the first.

Bye.
Giuseppe

[LittleColin]

Many thanks for the swift reply - for me your second option is working well