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
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
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
Many thanks for the swift reply - for me your second option is working well